Need help in this 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

Need help in this query

Author

Topic

Phany
Starting Member

8 Posts

Posted - 2014-02-09 : 03:33:49

Need help in this query
WITH a AS (
SELECT
clientid,
DATEPART(year, row_date) AS 'Year',
DATEPART(month, row_date) AS 'Month',
value,
CASE
WHEN metricid in (56) THEN 'numerator' ELSE 'denominator' END AS metric

FROM ****[Values] AS V
WHERE metricid IN (56, 10,11,16)
--WHERE metricid IN (11,16)
AND row_date BETWEEN '2013-10-01' AND '2014-02-01'
AND value IS NOT NULL)
, b AS (
SELECT clientid ,
Year ,
Month ,
value AS 'numerator',
metric

FROM a
WHERE metric = 'numerator')
, c AS (
SELECT clientid ,
Year ,
Month ,
value AS 'denominator',
metric

FROM a
WHERE metric = 'denominator')
, d AS (
SELECT b.YEAR, b.MONTH, c.denominator, b.numerator
FROM b INNER JOIN c
ON c.clientid = b.clientid
AND c.[YEAR] = b.[year]
AND c.[month] = b.[month]
WHERE c.denominator <> 0 AND b.numerator <> 0
)
, e AS (
SELECT [Year], [Month], SUM(numerator) AS numerator, SUM(denominator) AS denominator
FROM d
GROUP BY [Year], [Month]
)

I need to look for 2 metrics 56 and 16 but this query can help me only with one metric 56..help pls

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-09 : 07:34:32

Maybe problem is the temporary table d, where you inner join on clientid, year and month. I would probably write the query like this:

select [Year]
      ,[Month]
      ,case when metric='denominator' then [value] else 0 end as denominator
      ,case when metric='numerator' then [value] else 0 end as numerator
  from a

Phany
Starting Member

8 Posts

Posted - 2014-02-09 : 08:48:24

Thank you bitsmed..

Im in a new trouble.. Im trying to combine 3 queries...

************ 1st Query ************
WITH a AS (
SELECT
clientid,
DATEPART(year, row_date) AS 'Year',
DATEPART(month, row_date) AS 'Month',
value ,
CASE metricid WHEN 16 THEN 'FCR' ELSE 'Cases' END AS metric
FROM XXXXXX AS V
WHERE metricid IN (16, 11)
AND row_date BETWEEN '2012-01-01' AND '2014-10-01'
AND value IS NOT NULL)
, b AS (
SELECT clientid ,
Year ,
Month ,
value AS 'Cases',
metric
FROM a
WHERE metric = 'cases')
, c AS (
SELECT clientid ,
Year ,
Month ,
value AS 'FCR',
metric
FROM a
WHERE metric = 'FCR')
, d AS (
SELECT b.YEAR, b.MONTH, c.FCR, b.Cases
FROM b INNER JOIN c
ON c.clientid = b.clientid
AND c.[YEAR] = b.[year]
AND c.[month] = b.[month]
WHERE c.fcr <> 0 AND b.cases <> 0
)
,E AS (
SELECT [Year], [Month], SUM(FCR) AS FCR, SUM(Cases) AS Cases
FROM d
GROUP BY [Year], [Month])

select YEAR, MONTH,
CASE MONTH
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END AS MonthName
,e.FCR AS FCRCases
,e.Cases AS TotalCases
,CASE
WHEN [month] IN (11, 12, 1) THEN 1
WHEN [month] IN (2, 3, 4) THEN 2
WHEN [month] IN (5, 6, 7) THEN 3
WHEN [month] IN (8, 9, 10) THEN 4
END AS 'Quarter'
--,CONVERT(DECIMAL(18, 2),(e.FCR/e.Cases)*100) AS FCRRaw
from e
order by YEAR, MONTH

**************2nd query*************

WITH a AS (
SELECT
clientid,
DATEPART(year, row_date) AS 'Year',
DATEPART(month, row_date) AS 'Month',
value,
CASE metricid WHEN 56 THEN 'numerator' ELSE 'denominator' END AS metric

FROM XXXXXXX.[Values] AS V
WHERE metricid IN (56, 10)
--WHERE metricid IN (11,16)
AND row_date BETWEEN '2013-10-01' AND '2014-02-01'
AND value IS NOT NULL)
, b AS (
SELECT clientid ,
Year ,
Month ,
value AS 'numerator',
metric

FROM a
WHERE metric = 'numerator')
, c AS (
SELECT clientid ,
Year ,
Month ,
value AS 'denominator',
metric

FROM a
WHERE metric = 'denominator')
, d AS (
SELECT b.YEAR, b.MONTH, c.denominator, b.numerator
FROM b INNER JOIN c
ON c.clientid = b.clientid
AND c.[YEAR] = b.[year]
AND c.[month] = b.[month]
WHERE c.denominator <> 0 AND b.numerator <> 0
)
, e AS (
SELECT [Year], [Month], SUM(numerator) AS numerator, SUM(denominator) AS denominator
FROM d
GROUP BY [Year], [Month]
)

SELECT *,
CASE
WHEN [month] IN (11, 12, 1) THEN 1
WHEN [month] IN (2, 3, 4) THEN 2
WHEN [month] IN (5, 6, 7) THEN 3
WHEN [month] IN (8, 9, 10) THEN 4
END AS 'Quarter'
FROM e
ORDER BY 1,2

******************3rd query**************

WITH a AS (
SELECT --L.[LocationGroupId],
-- T.locationid,
-- T.AccountId,
TR.datestamp,
/*Convert(NVARCHAR, DatePArt(year, TR.datestamp)) + '-' + Convert(NVARCHAR, DatePArt(month, TR.datestamp)) + '-01'*/
TR.Period AS ValueDate,
CASE WHEN TR.TargetResultState = 0 THEN 0 WHEN TR.TargetResultState = 1 THEN 1 WHEN TR.TargetResultState = 2 THEN 1 ELSE 0 END AS Met,
CASE WHEN CONVERT(DATE, Convert(NVARCHAR, DatePArt(year, TR.datestamp)) + '-' + Convert(NVARCHAR, DatePArt(month, TR.datestamp)) + '-01') > T.Startdate AND CONVERT(DATE, Convert(NVARCHAR, DatePArt(year, TR.datestamp)) + '-' + Convert(NVARCHAR, DatePArt(month, TR.datestamp)) + '-01') < T.Enddate THEN 1 ELSE 0 END AS ActiveTarget
FROM XXXXXX AS TR
INNER JOIN dbo.Target T ON TR.TargetID = T.ID
--INNER JOIN dbo.Location L ON T.Locationid = L.Id
WHERE --locationid <> - 1 AND
TR.Period IN ('201306', '201307', '201308', '201309', '201310', '201311', '201312', '201401'))

select ValueDate, SUM(Met) AS Met, Count(ActiveTarget) AS ActiveTargets,
right(ValueDate,2) as Month
,left(ValueDate,4) as Year
,CASE
WHEN right(ValueDate,2) IN (11, 12, 1) THEN 1
WHEN right(ValueDate,2) IN (2, 3, 4) THEN 2
WHEN right(ValueDate,2) IN (5, 6, 7) THEN 3
WHEN right(ValueDate,2) IN (8, 9, 10) THEN 4
END AS 'Quarter'
from a
group by ValueDate
order by ValueDate

Can you help me? im not sure if union all or which method would work

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-09 : 10:06:00

If it were up to me, I'd rewrite the queries, as there's alot of unnecessary code.
If you explain the section in query 3, where you calculate "ActiveTarget", I might be able to come up with a suggestion.
Seems like ActiveTarget is always 0, and as you do a count, you'd always get a count of all the records within the period and that links to table Target. I'm guessing you'd want to do a sum instead?

Edit: Spelling error corrected.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-09 : 10:25:16

Sorry, I was a bit hasty in the last comment about the "ActiveTarget".
Try this:

select [Year]
      ,[Month]
      ,case
          when [Month] in (11,12,1) then 1
          when [Month] in (2,3,4) then 2
          when [Month] in (5,6,7) then 3
          when [Month] in (8,9,10) then 4
       end as [quarter]
      ,sum(fcr) as fcr
      ,sum(fcr) as cases
      ,sum(mumerator) as numerator
      ,sum(denominator) as denominator
      ,sum(met) as met
      ,sum(activetarget) as activetarget
  from (/* QUERY 1 */
        select datepart(year,row_date) as [Year]
              ,datepart(month,row_date) as [Month]
              ,sum(case when metricid=16 then [value] else 0 end) as fcr
              ,sum(case when metricid=11 then [value] else 0 end) as cases
              ,0 as numerator
              ,0 as denominator
              ,0 as met
              ,0 as activetarget
          from XXXXXX
         where metricid in (11,16)
           and row_date>='2012-01-01'
           and row_date<='2014-10-01'
           and [value] is not null
         group by datepart(year,row_date)
                 ,datepart(month,row_date)
        union all
        /* QUERY 2 */
        select datepart(year,row_date) as [Year]
              ,datepart(month,row_date) as [Month]
              ,0 as fcr
              ,0 as cases
              ,sum(case when metricid=56 then [value] else 0 end) as numerator
              ,sum(case when metricid=10 then [value] else 0 end) as denominator
              ,0 as met
              ,0 as activetarget
          from XXXXXXX.[Values]
         where metricid in (10,56)
           and row_date>='2013-10-01'
           and row_date<='2014-02-01'
           and [value] is not null
        union all
        /* QUERY 3 */
        select datepart(year,tr.datestamp) as [Year]
              ,datepart(month,tr.datestamp) as [Month]
              ,0 as fcr
              ,0 as cases
              ,0 as numerator
              ,0 as denominator
              ,sum(case tr.TargetResultState
                      when 1 then 1
                      when 2 then 1
                      else 0
                   end
                  ) as met
              ,sum(case dateadd(dd,day(tr.datestamp)*-1+1,cast(tr.datestamp as date))<t.Startdate
                    and dateadd(dd,day(tr.datestamp)*-1+1,cast(tr.datestamp as date))>t.Enddate
                      then 1
                      else 0
                   end
                  ) as activetarget
          from XXXXXX as tr
               inner join dbo.Target as t
                       on t.ID=tr.TARGETID
         where tr.Period>='201306'
           and tr.Period<='201401'
       ) as a
 group by [Year]
         ,[Month]
         ,case
             when [Month] in (11,12,1) then 1
             when [Month] in (2,3,4) then 2
             when [Month] in (5,6,7) then 3
             when [Month] in (8,9,10) then 4
          end

Phany
Starting Member

8 Posts

Posted - 2014-02-09 : 11:40:47

Thanks a ton bitsmed

Trying now

Phany
Starting Member

8 Posts

Posted - 2014-02-09 : 12:27:55

values were not matching accurately.. I will try to sort it out.. Thanks a lot again bitsmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources