Still getting NULL value for column in SQL pivot table

Question

Bit stumped on this one. In my below query it outputs fine except one month where for one of the item groups there was no invoices for that month. As such, its outputting a blank field which in turn is not giving me a grand total for that row.

SELECT
[%0] AS 'Year',
 P.[ItemGroup] as 'BusinessUnit'
, [1] as Jan
, [2] as Feb
, [3] as Mar
, [4] as Apr
, [5] as May
, [6] as Jun
, [7] as Jul
, [8] as Aug
, [9] as Sep
, [10] as Oct
, [11] as Nov
, [12] as Dec
, [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] AS 'Grand Total'
From
(select CASE WHEN (T4.[ItmsGrpNam] = 'ADC Parts') Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Bronze PM' Then 'PM'
WHEN T4.[ItmsGrpNam] = 'Competitor Parts' Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Comprehen CareFee' Then 'Comprehensive Care' 
WHEN T4.[ItmsGrpNam] = 'Contractor Ins' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Labour' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Contractor PM' Then 'PM' 
WHEN T4.[ItmsGrpNam] = 'Delivery & Installat' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'IPSO Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Items' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Reconditioned Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Service' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Other Travel' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Bags' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trol Delivery' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolley Parts' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'TrolleyRwM' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolleys' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] LIKE '%%Comm%%' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Vended Ancillary' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Merchant Fee and CC' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Laundry P&A' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Garment ID Parts' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Heat Sealer' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Labelling Service' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'PYO' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Thermal Printers' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Freight Income' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Postage &Handling' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] LIKE '%%Ind%%' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Ironers' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Second Hand Equip' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'EnviroSaver' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Parts' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Manufacturing' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] LIKE '%%Rent%%' Then 'Rent' 
ELSE T4.ItmsGrpNam END AS ItemGroup,sum(ISNULL(T0.[LineTotal],0)) as T, month(T1.[DocDate]) as Month from INV1 T0  
INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
where   year(T1.[DocDate]) = [%0]
Group by T1.[CardCode],T4.[ItmsGrpNam],T1.[DocDate]


UNION ALL

select CASE WHEN (T4.[ItmsGrpNam] = 'ADC Parts') Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Bronze PM' Then 'PM'
WHEN T4.[ItmsGrpNam] = 'Competitor Parts' Then 'Spare Parts'
WHEN T4.[ItmsGrpNam] = 'Comprehen CareFee' Then 'Comprehensive Care' 
WHEN T4.[ItmsGrpNam] = 'Contractor Ins' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Labour' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Contractor Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Contractor PM' Then 'PM' 
WHEN T4.[ItmsGrpNam] = 'Delivery & Installat' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'IPSO Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Items' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Reconditioned Parts' Then 'Spare Parts' 
WHEN T4.[ItmsGrpNam] = 'Service' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Other Travel' Then 'Service' 
WHEN T4.[ItmsGrpNam] = 'Bags' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trol Delivery' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolley Parts' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'TrolleyRwM' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] = 'Trolleys' Then 'Trolleys' 
WHEN T4.[ItmsGrpNam] LIKE '%%Comm%%' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Vended Ancillary' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Merchant Fee and CC' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Laundry P&A' Then 'Commercial' 
WHEN T4.[ItmsGrpNam] = 'Garment ID Parts' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Heat Sealer' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Labelling Service' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'PYO' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Thermal Printers' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Freight Income' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] = 'Postage &Handling' Then 'Labelling' 
WHEN T4.[ItmsGrpNam] LIKE '%%Ind%%' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Ironers' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'Second Hand Equip' Then 'Industrial' 
WHEN T4.[ItmsGrpNam] = 'EnviroSaver' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Parts' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] = 'Ozone Manufacturing' Then 'EnviroSaver' 
WHEN T4.[ItmsGrpNam] LIKE '%%Rent%%' Then 'Rent' 
ELSE T4.ItmsGrpNam END AS ItemGroup,sum(ISNULL(-T0.[LineTotal],0)) as T, month(T1.[DocDate]) as Month from RIN1 T0  
INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
where   year(T1.[DocDate]) = [%0]
Group by T1.[CardCode],T4.[ItmsGrpNam],T1.[DocDate]

)S

Pivot
(Sum(T) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P


ORDER BY [ItemGroup] ASC

If I change the values in my select statement to be ISNULL([9],0) then it outputs 0.00 for me for that month, but will still give a blank value in the grand total column for that row, even though every other month has values.

As soon as a single month doesnt have any invoices it refuses to sum the rest of the months up. Ideally, if there are no invoices for the month I would just get a 0 value but still have a grand total. Example would be for this year if one item group had 15000 for Jan and 4000 for Feb it would give me a grand total of 19000 since we havent reached any other month yet.

EDIT: Added the image to show current result, as you can see Sep for Industrial is blank and so is the grand total(GT) for that row. This should be a 0 value for that month and still have all the months summed up.

You could probably simplify the query and provide sample data and desired results to illustrate your issue. — Gordon Linoff, Feb 16 '21 at 01:15
You need `ISNULL` on each of those columns in the grand total. But if you switched to a `group by` pivot with conditional aggregation, you could then just use `rollup` to get the total. Not sure if that's easier or harder. Aside: I hope `%0` doesn't signify you are interpolating values direct into SQL, instead of using parameters. Also doing a `where` on `year()` function will be slow, better to use `date >= @yearstart and date < dateadd(year, 1, @yearstart)` — Charlieface, Feb 16 '21 at 01:28
Hi @GordonLinoff, I have added an image to illustrate the issue. — Nick Jones, Feb 16 '21 at 02:02
@Charlieface thanks for your response. Im still learning to understand pivot tables as they arent my strongest suite yet. This query in particular is being run through SQL report on B1 usability package for B1 — Nick Jones, Feb 16 '21 at 02:04

score 1 · Accepted Answer · answered Feb 16 '21 at 02:09

1

value + NULL will always return NULL. So you need ISNULL here:

ISNULL([1],0) + ISNULL[2],0) + ISNULL[3],0) + ISNULL[4],0) + ISNULL[5],0) + ISNULL[6],0) + ISNULL[7],0) + ISNULL[8],0) + ISNULL[9],0) + ISNULL[10],0) + ISNULL[11],0) + ISNULL[12] AS [Grand Total]

If you were using a custom pivot by conditionally aggregating, such as:

Jan = SUM(CASE WHEN Month = 1 THEN T END),
Feb = SUM(CASE WHEN Month = 2 THEN T END),

Then you could do it much simpler: SUM(T) AS [Grand Total]

answered Feb 16 '21 at 02:09

Charlieface

52,284
6
19
43

Marked as the correct answer as it solved my issue at hand. In the future I will try and rewrite this code to be better and simpler when i understand more about pivot tables. – Nick Jones Feb 16 '21 at 02:16

Still getting NULL value for column in SQL pivot table

1 Answers1