CODEWITHSUNDEEP
sqlsql-server
Nancy Ghazal
Nancy Ghazal

Reputation: 5

select data between 2 dates in SQL

I have a problem with the following code:

    SELECT PM.PM_Eng_Name, ISNULL(SUM(PMOutput.Quantity),0) AS TotalOut
FROM PM LEFT OUTER JOIN
PMOutput ON PM.PM_code = PMOutput.PM_code
WHERE (PMOutput.Output_Date BETWEEN ‘2013-01-01’ AND ‘2013-08-25’)
GROUP BY PM.PM_Eng_Name

When I run this query I got the total output only for the materials that have output transactions during the selected date rang, while I need to generate the total output for all the PM_Eng_Names I have, with the value 0 for the materials that have no output transaction in the selected date range Note: I got the perfect report when I remove the WHERE clause, but the date is important for my project Anyone can help me please?

Upvotes: 0

Views: 2624

Answers (3)

whytheq
whytheq

Reputation: 35605

FROM is one of the initial logic query phases and is completed before WHERE is applied. When WHERE is applied in your query it is filtering out lots of rows as they have Output_Date that is null.

So initially your LEFT JOIN does this:

CREATE TABLE #j 
    (
    Name char(8)
    );
GO
INSERT INTO #j 
    values 
    ('jim'),
    ('michael'),
    ('raj'),
    ('jason'),
    ('tim');
GO

CREATE TABLE #q 
    (
    name char(8), 
    dateBirth datetime
    );
GO
INSERT INTO #q
    values 
    ('jim', '01 jan 2010'),
    ('michael', '01 sep 2010');
GO

SELECT * FROM #j LEFT OUTER JOIN #q ON #j.Name = #q.Name;

This returns the following:

enter image description here

SQL-server treats NULL differently depending which phase of the query it finds the nulls and which operator is applied to the nulls. In the WHERE cluse we can see that it discards the rows with null DateBirth if we specify a filter on this attribute:

SELECT  *
FROM    #j
        LEFT OUTER JOIN #q ON
        #j.Name = #q.Name
WHERE   dateBirth BETWEEN '2000-01-01' AND '2020-01-01';

Results in:

enter image description here

("Logical Query Phases" would be a good Google)

Upvotes: 0

roman
roman

Reputation: 117606

To get correct results, add dates condition into join instead of where and put isnull inside sum:

select
    PM.PM_Eng_Name,
    sum(isnull(PMOutput.Quantity, 0)) as TotalOut
from PM
    left outer join PMOutput on PM.PM_code = PMOutput.PM_code and PMOutput.Output_Date BETWEEN ‘2013-01-01’ AND ‘2013-08-25’
group by PM.PM_Eng_Name

Upvotes: 4

Philippe Banwarth
Philippe Banwarth

Reputation: 17755

I think that is because the 'outer joined' columns from PMOutput contain only null, so they are filtered by the where clause

what happens with :

WHERE PMOutput.Output_Date is null or (PMOutput.Output_Date BETWEEN ‘2013-01-01’ AND ‘2013-08-25’)

Upvotes: 1

Related Questions