Hi
I have a complex query which appears to populate the workingdaysL1 column sometime and other times I get a NULL. If it is run with NULL values showing I get this in the message
Warning: Null value is eliminated by an aggregate or other SET operation.
But it brings in results except the workingdaysL1 column is showing as NULL. Initially the [148-Holiday] did not have any dates in for 2023 so I populated this up to New Years day 2024. It did not populate
the workingdaysL1 column immediately but then it started populating. Today we are getting NULL in the column instead of any figures. The query was not written by myself so finding it hard to decipher. Anything obvious popping out to anyone at all. Thanks in advance
I have a complex query which appears to populate the workingdaysL1 column sometime and other times I get a NULL. If it is run with NULL values showing I get this in the message
Warning: Null value is eliminated by an aggregate or other SET operation.
But it brings in results except the workingdaysL1 column is showing as NULL. Initially the [148-Holiday] did not have any dates in for 2023 so I populated this up to New Years day 2024. It did not populate
the workingdaysL1 column immediately but then it started populating. Today we are getting NULL in the column instead of any figures. The query was not written by myself so finding it hard to decipher. Anything obvious popping out to anyone at all. Thanks in advance
SQL:
SELECT pr.ProductCode, pr.Description, pso.StockLevelMin, pso.StockLevelMax, st.stockavailable, SUM(PA.QuantityUsed) AS QTYUsed, pr.udfTopTier AS TOPTIER, ws.ScheduleNumber AS SchedNum,
m3wo.m3 AS M3_WO, pa.ProductID,
(SELECT DATEDIFF(dd, bd.start_previous_month, lid.InvoiceDate) + 1 - DATEDIFF(wk, bd.start_previous_month, lid.InvoiceDate) * 2 - CASE WHEN DATENAME(dw, bd.start_previous_month)
= 'Sunday' THEN 1 ELSE 0 END - CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END -
(SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday] AS hol
WHERE hol.HolidayDate >= bd.start_previous_month AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1) AS WorkingDaysL1, pa.BranchID
FROM dbo.ProductAnalysis AS pa INNER JOIN
dbo. Product AS pr ON pa.ProductID = pr.ProductID INNER JOIN
dbo.ProductGroup AS pg ON pr.ProductGroupID = pg.ProductGroupID INNER JOIN
dbo.[148-vwQuantityUsedCheck_Usage_MDF3] AS qucu ON pa.ProductID = qucu.ProductID INNER JOIN
dbo.productStockOption AS pso ON pso.productid = pr.productid INNER JOIN
dbo.Stock AS st ON st.ProductID = PSO.ProductID AND st.BranchID = pa.BranchID LEFT OUTER JOIN
[148-vwOnWOSchedule] AS ws ON ws.ProductID = pa.ProductID LEFT OUTER JOIN
[148-vwOnWorksOrder] AS m3wo ON m3wo.ProductID = pa.ProductID CROSS JOIN
dbo.LastInvoiceDate AS lid OUTER apply
(SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS start_previous_month) AS bd
WHERE pa.CalendarYear * 10000 + pa.CalendarMonth * 100 BETWEEN datepart(year, DATEADD(month, - 2, GETDATE())) * 10000 + datepart(MONTH, DATEADD(month, - 2, GETDATE()))
* 100 /* get previous month - result could be previous year*/ AND datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100
GROUP BY pa.BranchID, pa.ProductID, bd.start_previous_month, pg.Level1ID, lid.InvoiceDate, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END, pr.ProductCode, pr.Description, pso.StockLevelMin,
pso.StockLevelMax, st.stockavailable, pr.udfTopTier, m3wo.m3, ws.ScheduleNumber
HAVING pa.BranchID IN (1, 9) AND pg.Level1ID = 893 AND pr.ProductCode LIKE 'am%' AND (Pso.stocklevelmin > 0) AND (PSO.StockLevelMax > 0)