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
 Count Query breaking down by month

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2013-12-06 : 10:32:28
Hi All
Need help in a query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013

Query
SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime,
(sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins,
(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime

From REPORT_DESCRIPTION RD LEFT OUTER JOIN SELECTED_REPORT SR on
RD.rptdesc = SR.rptdesc
where SR.deliverydate != '1/1/1900'
and SR.deliverydate between '1/1/2013' and '1/31/2013'
and RD.rptdesc in (20,21,26,27,30,32,62,99,118,120,155,263,276,306)

group by RD.RPTDESC
order by rptdesc


Results

Jan-13 Feb-13
RPTDESC ReportCount ProdHours ProdMins TotalProdTime AverageProdTime TriageHours TriageMins TotalTriageTime AverageTriageTime ReportCount ProdHours ProdMins TotalProdTime AverageProdTime TriageHours TriageMins TotalTriageTime AverageTriageTime
20 5 0 60 60 12 0 25 25 5 8 0 140 140 17 0 39 39 4
21 1 0 10 10 10 0 5 5 5 1 0 35 35 35 0 5 5 5
26 2 0 25 25 12 0 10 10 5 9 0 135 135 15 0 44 44 4
27 11 0 120 120 10 0 55 55 5 1 0 10 10 10 0 5 5 5
30 1 0 10 10 10 0 5 5 5 13 0 130 130 10 0 63 63 4
32 12 0 120 120 10 0 60 60 5 1 0 15 15 15 0 10 10 10
62 4 9 45 585 551 0 40 40 10 4 0 54 54 13 0 20 20 5
99 5 0 60 60 12 0 25 25 5 40 0 222 222 5 3 181 361 184
118 37 0 205 205 5 0 185 185 5 1 0 30 30 30 0 5 5 5
120 1 0 10 10 10 0 5 5 5 125 0 705 705 5 5 603 903 304
155 101 0 617 617 6 0 505 505 5 35 0 627 627 17 1 210 270 66
263 17 0 385 385 22 0 85 85 5 47 0 470 470 10 3 216 396 184
276 35 0 350 350 10 0 175 175 5 4 0 20 20 5 0 18 18 4
306 4 0 20 20 5 0 20 20 5 0 0 0 0 0 0 0 0



Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in febrauary and how to get decimal values in average feilds


Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-06 : 10:56:21
A couple of things:
1. In your current query, because of your predicate, you have turned the LEFT JOIN to an INNER JOIN.
2. In order to break the results out by month, you need to GROUP BY month.
3. In order to get rows that don't have any values, you need to outer join to a data set that has a list of months. You can do this by joining to an existing Date table, create a CTE that calculates the months or a function that returns a table with those values, etc..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-07 : 04:26:59
Something like this


SELECT t.*,
COALESCE(ReportCount,0) AS ReportCount,
COALESCE(ProdHours,0) AS ProdHours,
COALESCE(ProdMins,0) AS ProdMins,
COALESCE(TotalProdTime,0) AS TotalProdTime,
COALESCE(AverageProdTime,0) AS AverageProdTime,
COALESCE(TriageHours,0) AS TriageHours,
COALESCE(TriageMins,0) AS TriageMins,
COALESCE(TotalTriageTime,0) AS TotalTriageTime,
COALESCE(AverageTriageTime,0) AS AverageTriageTime
FROM
(
SELECT m.RPTDESC,n.[Date]
FROM (SELECT DISTINCT RPTDESC FROM REPORT_DESCRIPTION )m
CROSS JOIN dbo.CalendarTable('20130101','20131231',0,1)n
)t
LEFT JOIN
(
SELECT RD.RPTDESC,DATEADD(mm,DATEDIFF(mm,0,SR.deliverydate),0) AS MnthDate,
Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime,
((sum(SR.Hrs)*60.0+ Sum(SR.Mins))*1.0/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins,
(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,((sum(SR.TriageHrs)*60+ Sum(SR.TriageMins))*1.0/Count(SR.RPTDESC)) as AverageTriageTime

From REPORT_DESCRIPTION RD INNER JOIN SELECTED_REPORT SR on
RD.rptdesc = SR.rptdesc
where SR.deliverydate >= '20130101' and SR.deliverydate < '20140101'
and RD.rptdesc in (20,21,26,27,30,32,62,99,118,120,155,263,276,306)
group by RD.RPTDESC,DATEADD(mm,DATEDIFF(mm,0,SR.deliverydate),0)
)u
ON u.RPTDESC = t.RPTDESC
AND u.MnthDate = t.[Date]
order by t.rptdesc,t.[Date]

see calndartable function here

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -