Working Days giving NULL values

[Cpreston]

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

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)

 

[SkipVought]

Hi,

Your SQL has 4 SELECT statements but only 2 FROM statements.

Each SELECT needs a FROM from which to select.

That's something that must be resolved before any other analysis.

Tip: when I coded my SQL or needed to debug some other SQL, I performed this "anal" formatting:
1. Show each SELECT, FROM, WHERE, HAVING, GROUP BY entity on a separate line
2. Show each SELECT within a SELECT at a regular indent.
...and any other visual devise I could devise to help me discern all the pieces within the whole so that any anomaly might be easier to identify and fix.

Like...
[pre]
SELECT
pr.ProductCode
, pr.Description
, pso.StockLevelMin
...
[/pre]

This is the price of maintainability.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

In general, I agree with you, Skip, but...
There are some SELECTs without FROM in his SQL, like;[tt]
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS start_previous_month[/tt]
which pretty much says: give me some current system data.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

I cut my teeth and spent most of my career in Oracle SQL where every SELECT had a FROM. So be it.

But I would still urge the OP to edit his SQL by arranging it in a manner that makes examination and debugging easier to see.

Where there are SELECTs within SELECTs, do those inner SELECTs give desired results. That might mean constructing special test data to prove certain parts of your code.

Had this code been working as expected before today? If so what has changed?

You have a long row to hoe.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Cpreston]

Hi

Thanks for the replies. This is not code and I only got involved as it is not working now. Nothing changed as far as I am aware which led me to believe the Holiday table was not populated, which it was not so I populated that up to New Year 2024. For some reason and slightly after I updated the holiday table we did get some results in the workingdaysL1 column but this morning it was back to null. This made me think the dates it was Using like like;
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS start_previous_month
which pretty much says: give me some current system data. Were the reason.

I am thinking of rebuilding it all step by step and of course use the From statement for the selects.

Thanks anyway,

 

[Andrzejek]

Your:[tt]
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS [red]start_previous_month[/red][/tt]
I would assume you want to get:[tt]
2023/[red]08[/red]/01[/tt] as a [red]start_previous_month[/red] (since now we have [red]Sept[/red]. 6, 2023, or 2023/[red]09[/red]/06)

But your SELECT gives:[tt]
2023/[highlight #FCE94F]07[/highlight]/01[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

While Andrzejek spots a logical error in the determination of the start_previous_month, this is not mainly related to getting NULL in the column WorkingDaysL1.

Also fixing data in dbo.[148-Holiday] will just fix what is subtracted from weekdays by the query part

SELECT COUNT(*) AS Expr1 FROM dbo.[148-Holiday] AS hol WHERE hol.HolidayDate >= bd.start_previous_month AND hol.HolidayDate < lid.InvoiceDate

That would become 0 with no data in dbo.[148-Holiday], but not null. The worst ase is an empty resultset which leads to Count(*)=0 not null. There might be a corner case when the WHERE condition rsults in NULL.

The point is, without getting into the details, you only get a NULL result if the initial number from which holidays are subtracted is null.

The starting point for the days calculation is DATEDIFF(dd, bd.start_previous_month, lid.InvoiceDate) and DATEDIFF(wk, bd.start_previous_month, lid.InvoiceDate) and there an error in start_previous_month might cause a NULL, indeed. but also a missing InvoiceDate. Ensure that these expressions can't be NULL, you might need to look into IvoiceDate of the table dbo.LastInvoiceDate for missing records there or missing InvoiceDate values aka NULLs there. Becaue NULL as input of most any function leads its result to be NULL and not error, that's how NULLs can propagate into an end result.

So the most general advice when you encounter a NULL in a result you don't expect to be NULL is looking for NULLs as input of any partial term along the way of determining the result column.

Chriss

 

[fredericofonseca]

code formatted to be easier to read - and some comments added to it.

as for why the nulls, the comments above already highlight what you need to look at.

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
     /* sql below should really be replaced with a select from a calendar table (and the holiday table as well if those not defined on the calendar table)
       if properly built the sql would become
       (select count(*)
        from calendartable hol
        where hol.IsWorkingDay = 1 
        -- e.g. exclude any weekend day and any holiday if those are flagged as well. 
        -- if not left outer join to the holiday table excluding those found would work as well
        and hol.HolidayDate >= bd.start_previous_month
        and hol.HolidayDate < lid.InvoiceDate
       )

     */
     , (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
/*
above where can be rewriten as 
where pa.CalendarYear * 100 + pa.CalendarMonth between convert(int, convert(char(6), dateadd(month, -2, getdate()), 112))
                                                   and convert(int, convert(char(6), getdate(), 112))
*/

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
-- as the following are not aggregation results they should be part of the joins or the where clause - "having should only be used for things like "having count(*) > 0 or having sum(amt) > 200" type of filters
having pa.BranchID in (1, 9)
    and pg.Level1ID = 893
    and pr.ProductCode like 'am%'
    and (pso.stocklevelmin > 0)
    and (pso.StockLevelMax > 0)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi All

Big thanks for all your replies. We cam in this morning and it is working again, so I think the NULL situation must be in the areas highlighted in your comments and causing the workingdaysL1 to not calculate.
I will work on your comments and see if I can find the cause the next time we get NULL again in the results. Again thanks to all of you to reply.