GROUPING BY A MONTH_ new - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GROUPING BY A MONTH_ new

Author

Topic

lwarunek
Starting Member

22 Posts

Posted - 2008-04-07 : 11:25:54

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:

SELECT
SUM(
CASE WHEN Datepart(month,P.DateClosed) = M.month_value THEN
P.ActualValue/3
ELSE
0
END
)as rev,
M.month_value,
--M.month_name
Datepart(year,P.DateClosed)

FROM
Months M
LEFT OUTER JOIN Projects P ON
M.month_value = Datepart(month,P.DateClosed)
LEFT OUTER JOIN ClosureProbability CP ON
CP.ClosureProbID = P.ClosureProbabilityID

WHERE
P.EndDate>getDate() and P.Dropped<>1 and CP.Closure_Probability_Percent =100

GROUP BY
M.month_value,
--M.month_name,
Datepart(year,P.DateClosed)

ORDER BY
M.month_value,
Datepart(year,P.DateClosed)desc

The results looks like:

£ M Year
------------
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

Imukai
Starting Member

29 Posts

Posted - 2008-04-07 : 11:42:13

You're adding WHERE clauses to your two LEFT JOIN'ed tables, which in effect (I believe) makes them into INNER JOINs at that point - thus you will only get results containing records.

You might try a UNION statement, or, what I've done in the past, I've added conditions to the LEFT JOIN itself and that seems to have worked.

Like in your case...

LEFT JOIN Projects P ON M.month_value = Datepart(month,P.DateClosed) AND P.EndDate > getDate() AND P.Dropped <> 1
LEFT JOIN ClosureProbability CP ON CP.ClosureProbID = P.ClosureProbabilityID AND CP.Closure_Probability_Percent=100

Really not sure what that does on the backend (I'm sure one of the guru's will ream me if necessary) but as I said, that's worked for me before when I've wanted a LEFT join with criteria attached to it.

lwarunek
Starting Member

22 Posts

Posted - 2008-04-07 : 11:45:59

Cheers!

lwarunek
Starting Member

22 Posts

Posted - 2008-04-07 : 11:46:51

Forgot to add,
it worked!

Thank you.

lwarunek
Starting Member

22 Posts

Posted - 2008-04-07 : 11:49:28

I've got another thing that needs to be done.

How would you achieve displaying the months in order starting from month 3 and ending in month 2 for instance?
Fiscal year from March till Feb, e.g.

Cheers.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-08 : 04:56:08

Something like this should do it...

order by (M.month_value + 9) % 12

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources