Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lwarunek
Starting Member
22 Posts |
 Posted - 2008-04-07 : 11:24:05
|
| Hi Everybody.I know there has been a lot stuff in the past about this subject but I think my case could still be a bit different.There is the SQL:SELECTSUM( CASE WHEN Datepart(month,P.DateClosed) = M.month_value THEN P.ActualValue/3 ELSE 0 END)as rev, M.month_value,--M.month_nameDatepart(year,P.DateClosed)FROMMonths M LEFT OUTER JOIN Projects P ONM.month_value = Datepart(month,P.DateClosed)LEFT OUTER JOIN ClosureProbability CP ONCP.ClosureProbID = P.ClosureProbabilityIDWHEREP.EndDate>getDate() and P.Dropped<>1 and CP.Closure_Probability_Percent =100GROUP BYM.month_value,--M.month_name,Datepart(year,P.DateClosed)ORDER BY M.month_value,Datepart(year,P.DateClosed)descThe results looks like: 133 1 2008 46 2 2008 333 3 2008 500 7 2007 125 11 2007 I want to see the months that don't have any values too.For some reason I can only see the ones that are in the records, even though I have joined them to my months table.Can u help me?Cheers |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-04-07 : 12:13:21
|
| Can enddate be null? If so, this part of your queryWHEREP.EndDate>getDate() will not be true when endDate is null. Same is true for DateClosed. Jim |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
GROUP BY MONTHS_new