Words: 891
Time to read: ~ 5 minutes
Pro-Cras-Tin-Ation!
Like most things in life, this piece of work came about while attempting to complete something else. It’s not a bad thing, I expect it at this stage.
Easy Like Sunday Morning
I find it easy to get the total of a row in SQL. Hell, when it is not particularly important, I’ll even go the easy route and use a calculated column in the table.
CREATE TABLE dbo.PushupsOctober
(
pushup_date date NOT NULL
CONSTRAINT PK_PushupsOctober PRIMARY KEY CLUSTERED,
attempt_01 tinyint NULL,
attempt_02 tinyint NULL,
attempt_03 tinyint NULL,
attempt_04 tinyint NULL,
attempt_05 tinyint NULL,
attempt_06 tinyint NULL,
attempt_07 tinyint NULL,
attempt_08 tinyint NULL,
total_pushups_per_day AS (ISNULL(attempt_01, 0) + ISNULL(attempt_02, 0) + ISNULL(attempt_03, 0) + ISNULL(attempt_04, 0) + ISNULL(attempt_05, 0) + ISNULL(attempt_06, 0) + ISNULL(attempt_07, 0) + ISNULL(attempt_08, 0))
);
GO
Then, all I have to do is insert the data and SQL will automatically take care of calculating the total per row for me.
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/07/2020 00:00:00', 20, 20, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/08/2020 00:00:00', 20, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/09/2020 00:00:00', 20, 20, 25, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/10/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/11/2020 00:00:00', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/12/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/13/2020 00:00:00', 20, 15, 15, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/14/2020 00:00:00', 30, 30, 20, 20, 25, 20, 20, 20);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/15/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/16/2020 00:00:00', 25, 25, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/17/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
GO
SELECT *
FROM dbo.PushupsOctober;
GO
Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.
SELECT *,
SUM(p.total_pushups_per_day) OVER () AS total_so_far
FROM dbo.PushupsOctober AS p;
GO
Easy Like Monday Morning
PowerShell is a different beast. Please don’t get me wrong; I still love the language. I don’t find it easier to get a row total and then a grand total though.
It’s possible! I’m just hoping that there is a better way. Saying all that here is my attempt at a row total and grand total using PowerShell.
If you have a better way (you choose the conditions that satisfy “better”) please let me know.
Grabbing the Data
First, let’s grab the data from the table in our database.
$data_2 = Invoke-DbaQuery -SqlInstance localhost -Database LocalTesting -Query @'
SELECT * FROM dbo.PushupsOctober;
'@
Removing Unwanted Properties
Here’s where I remembered that I had a calculated column, realised that it would be cheating to use it and decided it needed to go. Thankfully, this also enabled me to get rid of those pesky columns that get returned from Invoke-DbaQuery when you forget the parameter -As PSObject!
$data_2 = $data_2 | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, total_pushups_per_day
Grabbing Property Names
There’s a couple of things that we need here. We need a way to add up all the “attempt” columns so we need a way to select them all.
$props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name
There, that should do nicely!
Grabbing the Values for those Properties
Now, we can iterate over them and get all the values.
foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }
Potential Problem
See all those empty lines? Yep, that’s a potential problem for Measure-Object.
$hasToBeAnEasierWay = foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }
$hasToBeAnEasierWay | Measure-Object -Sum
Removing NULL or WhiteSpace
Thankfully, there’s a way to get rid of those empty lines.
$hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum
Putting it ALL Together
Now that we have the skeleton of a script ready, let’s put it all together.
Row total
$data_2 | ForEach-Object -Begin {
$props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name
} -Process {
$total = $null
$hasToBeAnEasierWay = $null
$hasToBeAnEasierWay = foreach ($prop in $props) {
$_ | Select-Object -ExpandProperty $prop
}
$total = ($hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum).Sum
$_ | Select-Object -Property *, @{
Name = 'total_per_day'
Expression = { $total }
}, @{
Name = 'days_left'
Expression = { ((Get-Date -Date '2020-10-31') - (Get-Date -Date $_.pushup_date)).Days }
}
} -OutVariable data_3 | Format-Table -Autosize
We now have the row total in our total_per_day property. And, with our use of -outvariable data_3, we have the results saved into a variable called $data_3 .
Grand Total
Once we have a single column that we can sum up to give us our grand total, then PowerShell makes this operation trivial.
I do have to use Format-List here because Format-Table can’t fit all the properties in so our new property total_so_far won’t show up.
$data_3 | Select-Object -Property *, @{
Name = 'total_so_far'
Expression = { ($data_3 | Measure-Object -Property total_per_day -Sum).Sum }
} | Format-List
There We Go!
While, I’d argue that it’s not as easy as SQL, it’s completely possible to get row totals and grant totals in PowerShell.
Honestly though, I hope there’s an easier way. Otherwise, I’m going to do it in SQL and then grab it out into PowerShell afterwards.