SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count Query breaking down by month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim_jim
Constraint Violating Yak Guru

USA
298 Posts

Posted - 12/06/2013 :  10:32:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4364 Posts

Posted - 12/06/2013 :  10:56:21  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 12/07/2013 :  04:26:59  Show Profile  Reply with Quote
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

Edited by - visakh16 on 12/07/2013 04:27:32
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000