SQL 0 sum for no match

natgillie
natgillie used Ask the Experts™
on
Hello,  I have a table that lists all the reasons for a line delay.  I have a table that may or may not have a line delay for today.  I need a query that shows all  possible reasons, sum the total line delay even if there were no line delays today.  I have everything I need untill I try to put the requirement of today.  Then it bombs.  Here is the code.

SELECT   sum(isnull(ld.LineDelayTimeLost,0)) as SumLD, rc.reasoncodedescription
FROM      dbo.tblReasonCode AS RC left outer join
                dbo.tblLineDelay AS LD on RC.ReasonCodeID = LD.ReasonCodeID left outer join      
                dbo.tblCPSEOS AS CPS ON LD.CPSEOSID = CPS.CPSEOSID  
               
group by  rc.ReasonCodeDescription

This gives me the following: all the reason codes and there total line delay for all time.  If there is no line delay it place a 0 in the correct colum,

when I add  Where cps.CPSEOSDate =convert(varchar(10), getdate(),121) I get nothing if nothing has been entered for today.  How do I make all the reason codes show up with a 0 in the correct colum?
Thank you in advance!

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
instead of  

sum(isnull(ld.LineDelayTimeLost,0))

try

isnull(sum(ld.LineDelayTimeLost),0)

Author

Commented:
No the issue is still with the where clause.  When I try to narrow it to just todays data.
where  
cps.CPSEOSDate =convert(varchar(10), getdate(),121)  

when this is added the query returns nothing.  I only want todays line delays.  If there are no line delays and there is nothing in the line delay table I want a 0 to show up in the SumLD column.  Thanks for trying to help.  
How are you datetime values stored on cps.CPSEOSDate? My first stab would be to say that the dates stored on CPSEOSDate are either datetime or smalldatetime, including hh:mm
And your WHERE clause strips off time value from GETDATE().
What happens if you change your query to this.

SELECT   
	sum(isnull(ld.LineDelayTimeLost,0)) as SumLD,	
	rc.reasoncodedescription
FROM      
	dbo.tblReasonCode AS RC 
	left outer join dbo.tblLineDelay AS LD 
			on RC.ReasonCodeID = LD.ReasonCodeID 
	left outer join dbo.tblCPSEOS AS CPS 
			ON LD.CPSEOSID = CPS.CPSEOSID  
Where 
	cps.CPSEOSDate BETWEEN convert(varchar(10), getdate(),121) AND DATEADD(day,1,convert(varchar(10), getdate(),121))
group by  
	rc.ReasonCodeDescription

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
It is still the same issue.  Nothing shows up if nothing has been entered for today.  The date stamp is as follows.  5/4/2009 12:00:00 AM.  If there was a reason for the line day today that reason and only that reason will show up.  I need regretfully all the reasons to show up and a 0 in the sum column.  Thank you so much for the effort.
Most Valuable Expert 2011
Top Expert 2012

Commented:
SELECT    
       isnull(sum(ld.LineDelayTimeLost),0) as SumLD,    
        rc.reasoncodedescription
FROM      
        dbo.tblReasonCode AS RC  
        left outer join dbo.tblLineDelay AS LD  
                        on RC.ReasonCodeID = LD.ReasonCodeID  
        left outer join dbo.tblCPSEOS AS CPS  
                        ON LD.CPSEOSID = CPS.CPSEOSID  
                     and   cps.CPSEOSDate BETWEEN convert(varchar(10), getdate(),121) AND DATEADD(day,1,convert(varchar(10), getdate(),121))
group by  
        rc.ReasonCodeDescription
G GodwinDatabase Administrator

Commented:
Try:
"Where cps.CPSEOSDate =convert(varchar(10), getdate(),121) or cps.CPSEOSDate IS NULL"
Best Regards
-G

Author

Commented:
I am getting todays data but only pratial.  Here is what I am getting so far.  What I am missing is the other reasons with a 0 in the SumLD
ReasonCode.jpg
Most Valuable Expert 2011
Top Expert 2012

Commented:
there are multiple suggestions above
what code generated that data?  
You could try like this as well

SELECT  sum(isnull(ld.LineDelayTimeLost,0)) as SumLD, 
	rc.reasoncodedescription
FROM dbo.tblReasonCode AS RC 
left outer join dbo.tblLineDelay AS LD on RC.ReasonCodeID = LD.ReasonCodeID 
left outer join (select * from dbo.tblCPSEOS where CPSEOSDate =convert(varchar(10), getdate(),121)) CPS ON LD.CPSEOSID = CPS.CPSEOSID  
group by  rc.ReasonCodeDescription

Open in new window

Author

Commented:
My code is generating the data shown above.  The suggestions thus far are not solving the issue of today's data.   The suggestion by sdstuber and ralmada do the same thing.  It show all the data for all of time.  Not narrowed to just today.   I am so thankfull for all the suggestions.  
Most Valuable Expert 2011
Top Expert 2012
Commented:
ah, I finally understand ( I think)  sorry it took so long...

try this...
SELECT isnull(
           (SELECT SUM(ld.linedelaytimelost)
            FROM dbo.tbllinedelay as ld, dbo.tblcpseos as cps
            WHERE rc.reasoncodeid = ld.reasoncodeid AND ld.cpseosid = cps.cpseosid
            AND   cps.cpseosdate BETWEEN CONVERT(varchar(10), getdate(), 121)
                                     AND dateadd(day, 1, CONVERT(varchar(10), getdate(), 121))),
           0
       )
           AS sumld,
       rc.reasoncodedescription
FROM dbo.tblreasoncode as rc

Open in new window

G GodwinDatabase Administrator

Commented:
The root problem is that the where clause you are adding is on a field in the right most table of your left outer joins (i.e. CPS).  To do this, the CPS record must exist, and for the CPS to exist, the LD must exist,and for the LD to exist, the RC must exist.  It may as will be inner joins.  
Try adding the "or cps.CPSEOSDate IS NULL" to your where clause as you had it. See what you get back.
-G

Author

Commented:
GDG_DBA: thank you for the suggestion.  It is not getting all of the reason codes.  Only the ones that have been used today.  

Author

Commented:
GDG_DBA: Can I add the where clause information in another location?  I know that sounds silly but what about in the select area something like cps.CPSEOSDate =convert(varchar(10), getdate()-1,121. While I have tried that and it did fail with a Incorrect syntax near '=' I wonder if I did the correct syntax would that help?  
What about like this?
select 	rc.reasoncodedescription,
	sum(isnull(b.LineDelayTimeLost,0)) as SumLD
from dbo.tblReasonCode AS RC,
left join (
		select * from dbo.tblLineDelay as LD 
		inner join dbo.tblCPSEOS CPS on LD.CPSEOSID = CPS.CPSEOSID 
		where CPSEOSDate =convert(varchar(10), getdate(),121)
	) b on RC.ReasonCodeID = b.ReasonCodeID
group by rc.ReasonCodeDescription

Open in new window

Author

Commented:
sdstuber:  I think your onto somthing.  I am checking to see if the results are accurate.  All reason codes are currently showing!!!!!!  I am now checking the sums.  Thanks I will get back with you shortly.

Author

Commented:
sdstuber:  Excellent.  The results are accurate and all reason codes are showing.  Thank you to all involved.  Hope you have a great day!!!.

Author

Commented:
Thanks so very much.
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial