Query summary the result - 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 2005 Forums

Transact-SQL (2005)

Query summary the result

Author

Topic

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-23 : 12:11:13

I added some fields in the GROUP BY
I have a query to calculate the total tasks in one year. But I don't know how to get the summary table

My query is :

SELECT
c.id, c.SDID, b.Team1group, b.Team2group, b.Team3Group, c.DateTime,
WO_Created_hour = datepart(hh,c.DateTime),
Count_SSID = count(c.SDID),
WO_Before7 = case when datepart(hh,c.DateTime) < '7' then count(datepart(hh,c.DateTime)) else 0 end,
WO_7 = case when datepart(hh,c.DateTime) = '7' then count(datepart(hh,c.DateTime)) else 0 end,
WO_8 = case when datepart(hh,c.DateTime) = '8' then count(datepart(hh,c.DateTime)) else 0 end,
WO_9 = case when datepart(hh,c.DateTime) = '9' then count(datepart(hh,c.DateTime)) else 0 end,
WO_10 = case when datepart(hh,c.DateTime) = '10' then count(datepart(hh,c.DateTime)) else 0 end,
WO_11 = case when datepart(hh,c.DateTime) = '11' then count(datepart(hh,c.DateTime)) else 0 end,
WO_12 = case when datepart(hh,c.DateTime) = '12' then count(datepart(hh,c.DateTime)) else 0 end,
WO_13 = case when datepart(hh,c.DateTime) = '13' then count(datepart(hh,c.DateTime)) else 0 end,
WO_14 = case when datepart(hh,c.DateTime) = '14' then count(datepart(hh,c.DateTime)) else 0 end,
WO_15 = case when datepart(hh,c.DateTime) = '15' then count(datepart(hh,c.DateTime)) else 0 end,
WO_16 = case when datepart(hh,c.DateTime) = '16' then count(datepart(hh,c.DateTime)) else 0 end,
WO_After17 = case when datepart(hh,c.DateTime) > '17' then count(datepart(hh,c.DateTime)) else 0 end

FROM timerecord b, task a, SeDesk c

WHERE b.Id = a.ID
and c.DateTime between '2011-01-01 00:00:00.000' and '2011-12-31 00:00:00.000'

GROUP BY b.Team1group, b.Team2group, b.Team3group,
c.DateTime,datepart(hh,c.DateTime)

================
My expect result is: assuming it is month 1, 2 and with Task at 7:00, 8:00, 9:00
it summaries the total task in each month
Team___________Month__WOBefore7__WO7__WO8__WO9
Team1
Application______1_______4_________7____8____2
Parking__________2_______7_________4____3____2
Team 2
Application______1_______4_________4____6____2
Parking__________2_______5_________2____1____2

=====================
My result is it doesn't summary the total task in January,

Team___________Month__WOBefore7__WO7__WO8__WO9
Team1
Application_______1_______4_________7____8____2
Parking1__________2_______1_________2____1____1
Parking2__________2_______4_________1____1____0
Parking3__________2_______2_________1____1____1

Team 2
Application______1_______4_________3____4____2
Application______1_______4_________1____1____2
Application______1_______4_________0____1____2
Parking__________2_______5_________2____1____2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 13:09:20

i'm sure posted code is not working code as its not even syntactically correct

for example i can see fields in select which are not included in group by and not aggregated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 14:13:01

[code]SELECT
c.id, c.SDID, b.Team1group, b.Team2group, b.Team3Group, CONVERT(date,c.DateTime),
WO_Created_hour = datepart(hh,c.DateTime),
Count_SSID = count(c.SDID),
WO_Before7 = case when datepart(hh,c.DateTime) < '7' then count(datepart(hh,c.DateTime)) else 0 end,
WO_7 = case when datepart(hh,c.DateTime) = '7' then count(datepart(hh,c.DateTime)) else 0 end,
WO_8 = case when datepart(hh,c.DateTime) = '8' then count(datepart(hh,c.DateTime)) else 0 end,
WO_9 = case when datepart(hh,c.DateTime) = '9' then count(datepart(hh,c.DateTime)) else 0 end,
WO_10 = case when datepart(hh,c.DateTime) = '10' then count(datepart(hh,c.DateTime)) else 0 end,
WO_11 = case when datepart(hh,c.DateTime) = '11' then count(datepart(hh,c.DateTime)) else 0 end,
WO_12 = case when datepart(hh,c.DateTime) = '12' then count(datepart(hh,c.DateTime)) else 0 end,
WO_13 = case when datepart(hh,c.DateTime) = '13' then count(datepart(hh,c.DateTime)) else 0 end,
WO_14 = case when datepart(hh,c.DateTime) = '14' then count(datepart(hh,c.DateTime)) else 0 end,
WO_15 = case when datepart(hh,c.DateTime) = '15' then count(datepart(hh,c.DateTime)) else 0 end,
WO_16 = case when datepart(hh,c.DateTime) = '16' then count(datepart(hh,c.DateTime)) else 0 end,
WO_After17 = case when datepart(hh,c.DateTime) > '17' then count(datepart(hh,c.DateTime)) else 0 end

FROM timerecord b, task a, SeDesk c

WHERE b.Id = a.ID
and c.DateTime between '2011-01-01 00:00:00.000' and '2011-12-31 00:00:00.000'

GROUP BY b.Team1group, b.Team2group, b.Team3group,
CONVERT(date,c.DateTime),datepart(hh,c.DateTime)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-23 : 15:22:24

Thank you very much, I got it.
the the field CONVERT(date,c.DateTime), didn't work, I just leaves it as a field c.DateTime, and it works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 17:51:07

quote:
Originally posted by JadeV

Thank you very much, I got it.
the the field CONVERT(date,c.DateTime), didn't work, I just leaves it as a field c.DateTime, and it works.

why? are you on sql 2005? date is available from sql 2008 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources