Select Totals - 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

Select Totals

Author

Topic

galbrecht
Starting Member

17 Posts

Posted - 2009-12-07 : 16:34:15

Hi

I have a table that holds all our service desk calls. I would like to be able to extract the total number of calls logged pre year and month as well as total calls closed per year and month. and example of the table is as folows.

requestid, Requestdate, requestclosedate
12, 01/01/2009, 10/01/2009
13, 02/01/2009
14, 01/01/2009,
15, 01/01/2009, 20/01/2009
16, 03/01/2009, 21/01/2009
17, 04/01/2009, 21/01/2009

Could somebody please help me out
Thanks

Greg

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 17:19:41

Maybe this?

Count yearly -

select datepart(year,Requestdate),count (requestid) 
from <table>
group by datepart(year,Requestdate)

Count monthly -

select datename(month,Requestdate),count (requestid) 
from <table>
group by 
datename(month,Requestdate)

galbrecht
Starting Member

17 Posts

Posted - 2009-12-07 : 17:31:28

Thanks for the reply,
My appologies - I never said what I wanted correctly. I would like to have one query that gives me the result as follows: Moth, total by requestdate and total by requestclosedate.

Month, Requestdate, Requestclosedate
January, 10, 9
February, 5, 7
March, 20, 7

Thanks

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-12-07 : 18:18:21

Hello,

I dont have sql server installed to work with what I wrote in here but please try this & let me know.

regards,
Anil.

Select b.ClosedMonth, b.MonthlyTotal, sum(a.MonthlyTotal) AS CumulativeTotalsThruYear
from
(select sum(requestid) as MonthlyTotal, datepart(month, requestclosedate) as ClosedMonth
from table
group by datepart(month, requestclosedate))b
cross join
(select sum(requestid) as MonthlyTotal, datepart(month, requestclosedate) as ClosedMonth
from table
group by datepart(month, requestclosedate))a
where a.MonthlyTotal < b.MonthlyTotal
group by b.ClosedMonth, b.MonthlyTotal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources