Metric Report Query - 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

Metric Report Query

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-10-16 : 11:45:18

Hi Everyone
Need help with a query to pull some metrics from couple of tables

Table 1: REPORT_INFO
Tabel 1 Feilds:rptid,rptname
Table 2:COMPLETED_REPORT
Table 2 Feilds:rptid,triagemins,prodmins,nbrrpts,deliverydate,requestid

I'm trying to pull metrics such that for every rptid what was the number of reports(nbrrpts),what was the triage time(triagemins),What was the production time (prodmins) broken down by month for the reporting period 10/1/2013 - 09/30/2014

Problem with my query
I'm able to get the report for the entire time frame but when i do monthly there are few reports where there is no data and the records that are generated is inconsistent

SELECT RI.RPTNAME as [Report Name],sum(CR.nbrrpts),sum(CR.Triagemins) as triageminutes,sum(CR.Mins)as ProductionMinutes
from Report_INFO RI inner join COMPLETED_REPORT CR
ON RI.rptid = CI.rptid
where CR.requestid in (select requestid from completed_report where deliverydate between '10/1/2013' and '10/31/2014')
group by RI.rptname

Woukld like to see the data in the below format

October 2013
Nbr rpts triagemins productionmins
REport Name

How do i get the data such that it returns zero for all the count feilds if there is no data for a report in a month . I can run it 12 times to populate the data for the next 12 months

Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-16 : 12:17:22

Do you mean the sum columns (I don't see any counts). Try ISNULL(SUM([your column]), 0)

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-10-16 : 12:28:36

There are 125 report names and there are cases where a particular report has not been completed in a given month so when i use a inner join the report name does not come as result .

Example
Query for Oct 2013 - 107 reports along with counts
Query for Nov 2013 - 97 Reports along with counts
Query for 10/1/2013- 9/30/2014 - 125 reports along with counts

so i want to be able to populate data for each month for all 125 reports . If there is no data returned for a report i want it to be all zeroes for that month

quote:
Originally posted by gbritton

Do you mean the sum columns (I don't see any counts). Try ISNULL(SUM([your column]), 0)

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-16 : 12:32:28

Try this:

select ri.rptname as [Report Name]
      ,sum(isnull(cr.nbrrpts,0))
      ,sum(isnull(cr.triagemins,0)) as triageminutes
      ,sum(isnull(cr.mins,0)) as ProductionMinutes
  from report_info as ri
       left outer join completed_report as cr
                    on cr.rptid=ri.rptid
                   and cr.deliverydate>='20131001'
                   and cr.deliverydate<'20131101'
 group by ri.rptname

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2014-10-16 : 13:03:51

Thanks so much . worked

quote:

Originally posted by bitsmed

Try this:

select ri.rptname as [Report Name]
      ,sum(isnull(cr.nbrrpts,0))
      ,sum(isnull(cr.triagemins,0)) as triageminutes
      ,sum(isnull(cr.mins,0)) as ProductionMinutes
  from report_info as ri
       left outer join completed_report as cr
                    on cr.rptid=ri.rptid
                   and cr.deliverydate>='20131001'
                   and cr.deliverydate<'20131101'
 group by ri.rptname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources