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.
Author |
Topic |
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2013-12-06 : 10:32:28
|
Hi AllNeed 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 2013QuerySELECT 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 AverageTriageTimeFrom REPORT_DESCRIPTION RD LEFT OUTER JOIN SELECTED_REPORT SR onRD.rptdesc = SR.rptdescwhere 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.RPTDESCorder 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 AverageTriageTime20 5 0 60 60 12 0 25 25 5 8 0 140 140 17 0 39 39 421 1 0 10 10 10 0 5 5 5 1 0 35 35 35 0 5 5 526 2 0 25 25 12 0 10 10 5 9 0 135 135 15 0 44 44 427 11 0 120 120 10 0 55 55 5 1 0 10 10 10 0 5 5 530 1 0 10 10 10 0 5 5 5 13 0 130 130 10 0 63 63 432 12 0 120 120 10 0 60 60 5 1 0 15 15 15 0 10 10 1062 4 9 45 585 551 0 40 40 10 4 0 54 54 13 0 20 20 599 5 0 60 60 12 0 25 25 5 40 0 222 222 5 3 181 361 184118 37 0 205 205 5 0 185 185 5 1 0 30 30 30 0 5 5 5120 1 0 10 10 10 0 5 5 5 125 0 705 705 5 5 603 903 304155 101 0 617 617 6 0 505 505 5 35 0 627 627 17 1 210 270 66263 17 0 385 385 22 0 85 85 5 47 0 470 470 10 3 216 396 184276 35 0 350 350 10 0 175 175 5 4 0 20 20 5 0 18 18 4306 4 0 20 20 5 0 20 20 5 0 0 0 0 0 0 0 0Also 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 feildsThanks |
|
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.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-07 : 04:26:59
|
Something like thisSELECT 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 AverageTriageTimeFROM(SELECT m.RPTDESC,n.[Date]FROM (SELECT DISTINCT RPTDESC FROM REPORT_DESCRIPTION )mCROSS JOIN dbo.CalendarTable('20130101','20131231',0,1)n)tLEFT 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 AverageTriageTimeFrom REPORT_DESCRIPTION RD INNER JOIN SELECTED_REPORT SR onRD.rptdesc = SR.rptdescwhere 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))uON u.RPTDESC = t.RPTDESCAND u.MnthDate = t.[Date]order by t.rptdesc,t.[Date] see calndartable function herehttp://visakhm.blogspot.in/2010/02/generating-calendar-table.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|