Grouping Results by Month - 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

General SQL Server Forums

New to SQL Server Programming

Grouping Results by Month

Author

Topic

mikecro
Starting Member

21 Posts

Posted - 2009-10-20 : 10:54:42

Hi, I have a query that puts out a list of available electives by month, however I'm getting a row for every instance.

Select e.elective,
case when (month(b.start_dt) = 1) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS JAN,
case when (month(b.start_dt) = 2) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS FEB,
case when (month(b.start_dt) = 3) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS MAR,
case when (month(b.start_dt) = 4) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS APR,
case when (month(b.start_dt) = 5) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS MAY,
case when (month(b.start_dt) = 6) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS JUN,
case when (month(b.start_dt) = 7) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS JUL,
case when (month(b.start_dt) = 8) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS AUG,
case when (month(b.start_dt) = 9) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS SEP,
case when (month(b.start_dt) = 10) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS OCT,
case when (month(b.start_dt) = 11) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS NOV,
case when (month(b.start_dt) = 12) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS DEC
from
(select elective_id,
elective_name as elective
from electives where active=1 and type <> 'sub') as e
inner join
(select elective_id,
block_id,
start_dt,
num_slots
from block_schedule where year(start_dt)=@year) as b
on e.elective_id=b.elective_id
left join
(select block_id,
status
from assignments where status in ('pending','approved')) as a
on b.block_id=a.block_id
group by e.elective, e.elective_id, b.block_id,b.start_dt,b.num_slots
order by e.elective,b.start_dt

The output is:

Anesthesiology 52 22_0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
Anesthesiology 52 NULL 789_1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
Anesthesiology 52 NULL NULL 23_0 NULL NULL NULL NULL NULL NULL NULL NULL NULL
Anesthesiology 52 NULL NULL NULL 24_0 NULL NULL NULL NULL NULL NULL NULL NULL
Anesthesiology 52 NULL NULL NULL NULL 25_1 NULL NULL NULL NULL NULL NULL NULL
Anesthesiology 52 NULL NULL NULL NULL NULL 26_0 NULL NULL NULL NULL NULL NULL
Anesthesiology 52 NULL NULL NULL NULL NULL NULL NULL NULL 27_1 NULL NULL NULL
Anesthesiology 52 NULL NULL NULL NULL NULL NULL NULL NULL NULL 28_1 NULL NULL
Anesthesiology 52 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 29_1 NULL
Anesthesiology 52 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 30_1

I'd like to be able to combine the resulting rows so that there is only one row per elective_id.

Any help would be greatly appreciated!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 12:23:48

group by first two fields and apply max or min over others


Select e.elective,
max(case when (month(b.start_dt) = 1) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JAN,
max(case when (month(b.start_dt) = 2) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS FEB,
max(case when (month(b.start_dt) = 3) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS MAR,
max(case when (month(b.start_dt) = 4) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS APR,
max(case when (month(b.start_dt) = 5) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS MAY,
max(case when (month(b.start_dt) = 6) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JUN,
max(case when (month(b.start_dt) = 7) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS JUL,
max(case when (month(b.start_dt) = 8) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS AUG,
max(case when (month(b.start_dt) = 9) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS SEP,
max(case when (month(b.start_dt) = 10) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS OCT,
max(case when (month(b.start_dt) = 11) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS NOV,
max(case when (month(b.start_dt) = 12) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS DEC
from
(select elective_id,
elective_name as elective
from electives where active=1 and type <> 'sub') as e
inner join
(select elective_id,
block_id,
start_dt,
num_slots
from block_schedule where year(start_dt)=@year) as b
on e.elective_id=b.elective_id
left join
(select block_id,
status
from assignments where status in ('pending','approved')) as a
on b.block_id=a.block_id
group by e.elective
order by e.elective,b.start_dt

mikecro
Starting Member

21 Posts

Posted - 2009-10-20 : 13:46:35

Hi Visak,

Thanks for your response, but the following errors occur which are not exactly obvious:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'as'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 13:49:55

missed some braces


Select e.elective,
max(case when (month(b.start_dt) = 1) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JAN,
max(case when (month(b.start_dt) = 2) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS FEB,
max(case when (month(b.start_dt) = 3) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS MAR,
max(case when (month(b.start_dt) = 4) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS APR,
max(case when (month(b.start_dt) = 5) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS MAY,
max(case when (month(b.start_dt) = 6) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JUN,
max(case when (month(b.start_dt) = 7) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JUL,
max(case when (month(b.start_dt) = 8) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS AUG,
max(case when (month(b.start_dt) = 9) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS SEP,
max(case when (month(b.start_dt) = 10) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS OCT,
max(case when (month(b.start_dt) = 11) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS NOV,
max(case when (month(b.start_dt) = 12) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS DEC
from
(select elective_id,
elective_name as elective
from electives where active=1 and type <> 'sub') as e
inner join
(select elective_id,
block_id,
start_dt,
num_slots
from block_schedule where year(start_dt)=@year) as b
on e.elective_id=b.elective_id
left join
(select block_id,
status
from assignments where status in ('pending','approved')) as a
on b.block_id=a.block_id
group by e.elective
order by e.elective,b.start_dt

mikecro
Starting Member

21 Posts

Posted - 2009-10-20 : 15:01:15

Now I'm getting the dreaded:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 15:14:07

wat about :-


select elective,
MAX(JAN),
MAX(FEB),...,MAX(DEC)
from
(
Select e.elective,
max(case when (month(b.start_dt) = 1) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JAN,
max(case when (month(b.start_dt) = 2) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS FEB,
max(case when (month(b.start_dt) = 3) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS MAR,
max(case when (month(b.start_dt) = 4) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS APR,
max(case when (month(b.start_dt) = 5) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS MAY,
max(case when (month(b.start_dt) = 6) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end) AS JUN,
max(case when (month(b.start_dt) = 7) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS JUL,
max(case when (month(b.start_dt) = 8) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS AUG,
max(case when (month(b.start_dt) = 9) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS SEP,
max(case when (month(b.start_dt) = 10) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS OCT,
max(case when (month(b.start_dt) = 11) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS NOV,
max(case when (month(b.start_dt) = 12) then (cast(b.block_id as varchar) + '_' + cast(b.num_slots - count(a.block_id)as varchar)) end AS DEC
from
(select elective_id,
elective_name as elective
from electives where active=1 and type <> 'sub') as e
inner join
(select elective_id,
block_id,
start_dt,
num_slots
from block_schedule where year(start_dt)=@year) as b
on e.elective_id=b.elective_id
left join
(select block_id,
status
from assignments where status in ('pending','approved')) as a
on b.block_id=a.block_id
group by e.elective
)t
group by elective

mikecro
Starting Member

21 Posts

Posted - 2009-10-20 : 16:03:29

Same result and I'm trying it on some other queries, too.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-20 : 16:29:00

k

Want an answer fast

Post the DDL of your table(s)

Post Sample Data in the form of DML

(INSERT INTO t (Collist) SELECT 'x','y','z' UNION ALL SELECT 'x','y','z'...ect

The post what the expect results should look like

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-20 : 18:10:02

[code]SELECT e.Elective,
e.Elective_ID,
MAX(CASE WHEN b.theMonth = 1 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Jan,
MAX(CASE WHEN b.theMonth = 2 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Feb,
MAX(CASE WHEN b.theMonth = 3 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Mar,
MAX(CASE WHEN b.theMonth = 4 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Apr,
MAX(CASE WHEN b.theMonth = 5 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS May,
MAX(CASE WHEN b.theMonth = 6 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Jun,
MAX(CASE WHEN b.theMonth = 7 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Jul,
MAX(CASE WHEN b.theMonth = 8 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Aug,
MAX(CASE WHEN b.theMonth = 9 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Sep,
MAX(CASE WHEN b.theMonth = 10 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Oct,
MAX(CASE WHEN b.theMonth = 11 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS Nov,
MAX(CASE WHEN b.theMonth = 12 THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - a.Items AS VARCHAR(12)) END) AS [Dec]
FROM (
SELECT Elective_ID,
Elective_Name AS Elective
FROM dbo.Electives
WHERE Active = 1
AND Type <> 'sub'
) AS e
INNER JOIN (
SELECT Elective_ID,
Block_ID,
DATEPART(MONTH, Start_DT) AS theMonth,
COUNT(*) AS NumSlots
FROM dbo.Block_Schedule
WHERE Start_DT >= DATEADD(YEAR, @Year - 1900, 0)
AND Start_DT < DATEADD(YEAR, @Year - 1899, 0)
GROUP BY Elective_ID,
Block_ID,
DATEPART(MONTH, Start_DT)
) AS b ON b.Elective_ID = e.Elective_ID
LEFT JOIN (
SELECT Block_ID,
COUNT(*) AS Items
FROM Assignments
WHERE Status IN ('Pending', 'Approved')
GROUP BY Block_ID
) AS a ON a.Block_ID = b.Block_ID
GROUP BY e.Elective,
e.Elective_ID
ORDER BY e.Elective,
b.Start_DT[/code]

N 56°04'39.26"
E 12°55'05.63"

mikecro
Starting Member

21 Posts

Posted - 2009-10-21 : 11:07:39

Thanks for your help everyone!! It would have taken me a month to figure that one out.

I made some minor adjustments to your suggested solutions and here is the working version:

SELECT e.Elective,
e.Elective_ID,
MAX(CASE WHEN (month(b.start_dt) = 1) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Jan,
MAX(CASE WHEN (month(b.start_dt) = 2) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Feb,
MAX(CASE WHEN (month(b.start_dt) = 3) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Mar,
MAX(CASE WHEN (month(b.start_dt) = 4) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Apr,
MAX(CASE WHEN (month(b.start_dt) = 5) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS May,
MAX(CASE WHEN (month(b.start_dt) = 6) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Jun,
MAX(CASE WHEN (month(b.start_dt) = 7) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Jul,
MAX(CASE WHEN (month(b.start_dt) = 8) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Aug,
MAX(CASE WHEN (month(b.start_dt) = 9) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Sep,
MAX(CASE WHEN (month(b.start_dt) = 10) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Oct,
MAX(CASE WHEN (month(b.start_dt) = 11) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Nov,
MAX(CASE WHEN (month(b.start_dt) = 12) THEN CAST(b.Block_ID AS VARCHAR(12)) + '_' + CAST(b.NumSlots - isnull(a.Items,0) AS VARCHAR(12)) END) AS Dec
FROM
(SELECT Elective_ID,
Elective_Name AS Elective
FROM dbo.Electives
WHERE Active = 1 AND Type <> 'sub') AS e
INNER JOIN
(SELECT Elective_ID,
Block_ID,
start_dt,
Num_Slots as NumSlots
FROM dbo.Block_Schedule
WHERE year(start_dt)=2010 and active=1
GROUP BY elective_ID, Block_ID, start_dt, num_slots) AS b
ON b.Elective_ID = e.Elective_ID
LEFT JOIN
(SELECT Block_ID,
COUNT(*) AS Items
FROM Assignments
WHERE Status IN ('Pending', 'Approved')
GROUP BY Block_ID) AS a
ON a.Block_ID = b.Block_ID
GROUP BY e.Elective, e.Elective_ID
Order by e.elective

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources