Not giving result but NUll

[Cpreston]

Hi

I have taken up quite a complex query for my standards. It is retuning NULL in the filed WorkingDaysL1.

On the results it is also showing
Warning: Null value is eliminated by an aggregate or other SET operation.
(121 rows affected)

which I don't understand either.

I have enclosed the SQL which I know may not mean much without knowing the data sets it all comes from. Any ideas where the code may be wrong though and where and how to change it.

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) +  - 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()) - 0, 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)

 

[Andrzejek]

I would investigate this SELECT:

... END - 
([blue]SELECT COUNT (*)  AS Expr1
FROM dbo.[148-Holiday] AS hol
WHERE hol.HolidayDate >= bd.start_previous_month 
AND hol.HolidayDate < lid.InvoiceDate[/blue]) [red]AS WorkingDaysL1) AS WorkingDaysL1, [/red]
pa.BranchID ...

Copy it out of of the big Select, substitute bd.start_previous_month and lid.InvoiceDate with some valid values and see what happens.

BTW - there is a mismatch number of '(' and ')' in this Select, or am I missing something....


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Yes I think it the issue is within the 2 select Statements from Select Datediff down to pa.branchID.

I have tried to add in isnull(count but that did not work. I will try and add in some valid values as you suggest and see if it comes up with anything.

Not sure what you mean about the ' and ' mismatch

 

[Andrzejek]

When I copy this part of your Select:
[tt]
[highlight #FCE94F]([/highlight]SELECT COUNT (*) AS Expr1
FROM dbo.[148-Holiday] AS hol
WHERE hol.HolidayDate >= bd.start_previous_month
AND hol.HolidayDate < lid.InvoiceDate[highlight #FCE94F])[/highlight] AS WorkingDaysL1[highlight #8AE234])[/highlight] AS WorkingDaysL1,
[/tt]
The [highlight #FCE94F]Yellow[/highlight] parenthesis are matching, but where does the [highlight #8AE234]Green[/highlight] one come from...
Must be from somewhere, otherwise you would error.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

) AS WorkingDaysL1) AS WorkingDaysL1, is this not repeating the AS statement.

I have tried the below which is what I think you meant, and it brings back a count of 15 days which is the right holiday days in that table. I changed the dates and it brings in the right day count each time.
If not could you explain a little more

SELECT COUNT(*) AS Expr1
FROM dbo.[148-Holiday] AS hol
WHERE hol.HolidayDate >= '2020-01-01 00:00:00' AND hol.HolidayDate < '2021-01-01 00:00:00'


I don't understand where the query is getting the bd from either

 

[Andrzejek]

I see you have substituted [tt]bd.start_previous_month[/tt] and [tt]lid.InvoiceDate[/tt] fields with some valid values, but now the question is: can these two fields be NULL?
If so, your calculations in your Select may/will return NULLs


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

I have some how managed to get it working, I think by pure luck than knowledge.

Thanks for the reply's it certainly helped me break things down and understand where I was looking, muchly appreciated

 

[Andrzejek]

Whatever it takes


---- Andy

There is a great need for a sarcasm font.