How do I return multiple columns and rows in a SQL Count Query?

Question

I am trying to put together the query for a multiple row report. I have this working. Each type is coming from a different database, so I am using subqueries to pull it together and the results are accurate, but all on one row. I would like to put incidents on their own row, service requests on their own row, and there are four other tables I want to pull the same aged data from. How do I get each set of aged days onto their own row for each type of ticket?

--SELECT COUNT (*) 
Select ai15 as [Incidents Aged 15 Days], as15 as [Service Requests Aged 15 Days], 
       ai30 as [Incidents Aged 30 Days], as30 as [Service Requests Aged 30 Days], 
       ai60 as [Incidents Aged 60 Days], as60 as [Service Requests Aged 60 Days], 
       ai90 as [Incidents Aged 90 Days], as90 as [Service Requests Aged 90 Days], 
       ai120 as [Incidents Aged 120 Days], as120  as [Service Requests Aged 120 Days]
from 
    (select count(*) as ai15
          from Incidents
          where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 15
     ) gt15i,
     (select count(*) as ai30
          from Incidents
          where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 30
     ) gt30i,
     (select count(*) as ai60
           from IncidentDimVw
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 60
     ) gt60i,
     (select count(*) as ai90
           from Incidents
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 90
     ) gt90i,
     (select count(*) as ai120
           from Incidents
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 120
     ) gt120i,
     (select count(*) as as15
           from SRs
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 15
     ) gt15s,
     (select count(*) as as30
           from SRs
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 30
     ) gt30s,
     (select count(*) as as60
           from ServiceRequestDimVw
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 60
     ) gt60s,
     (select count(*) as as90
           from SRs
           where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 90
     ) gt90s,
     (select count(*) as as120
          from SRs
          where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 120
     ) gt120s

Change the query to a union, split between incidents and services with a marker as to which one is which? — Bernd Linde
– Bernd Linde, Commented Jan 9, 2015 at 16:46

Hogan · Accepted Answer · 2015-01-09 16:49:20Z

Not 100% clear without better specifications, but try the following, I'm guessing it will get you on the right track:

select count(*) as count, 'ai15' as subType, 'Incidents' as mainType
from Incidents
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 15

union all

select count(*) as count, 'ai30' as subType, 'Incidents' as mainType
from Incidents
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 30

union all

select count(*) as count,  'ai60' as subType, 'IncidentDimVw' as mainType
from IncidentDimVw
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 60

union all

select count(*) as count, 'ai90' as subType, 'Incidents' as mainType
from Incidents
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 90

union all

select count(*) as count, 'ai120' as subType, 'Incidents' as mainType
from Incidents
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 120

union all

select count(*) as count, 'as15' as subType, 'SRs' as mainType
from SRs
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 15

union all

select count(*) as count, 'as30' as subType, 'SRs' as mainType
from SRs
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 30

union all

select count(*) as count, 'as60' as subType, 'ServiceRequestDimVw' as mainType
from ServiceRequestDimVw
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 60

union all

select count(*) as count, 'as90' as subType, 'SRs' as mainType
from SRs
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 90

union all

select count(*) as count, 'as120' as subType, 'SRs' as mainType
from SRs
where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 120

This was not exactly what I was looking for as the columns and rows are reversed, but I may wind up using this for a different report. Thank you!

Mysti · Accepted Answer · 2015-01-09 18:20:21Z

This is the final code I wound up using based on Bernd Linde's suggestion:

Select gttype as [Ticket Type],
       ai15 as [Incidents Aged 15 Days],
       ai30 as [Incidents Aged 30 Days],
       ai60 as [Incidents Aged 60 Days],
       ai90 as [Incidents Aged 90 Days],
       ai120 as [Incidents Aged 120 Days]
from 
    (select 'Incident' as gttype
    ) gttyper,
    (select count(*) as ai15
    from Incidents
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 15
    ) gt15i,
    (select count(*) as ai30
    from Incidents
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 30
    ) gt30i,
    (select count(*) as ai60
    from Incidents
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 60
    ) gt60i,
    (select count(*) as ai90
    from Incidents
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 90
    ) gt90i,
    (select count(*) as ai120
    from Incidents
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 120
    ) gt120i
    UNION
Select gttype as [Ticket Type],
       as15 as [Service Requests Aged 15 Days], 
       as30 as [Service Requests Aged 30 Days], 
       as60 as [Service Requests Aged 60 Days], 
       as90 as [Service Requests Aged 90 Days], 
       as120  as [Service Requests Aged 120 Days]
from 
    (select 'Service Request' as gttype
    ) gttyper,
    (select count(*) as as15
    from SRs
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 15
    ) gt15s,
    (select count(*) as as30
    from SRs
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 30
    ) gt30s,
    (select count(*) as as60
    from SRs
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 60
    ) gt60s,
    (select count(*) as as90
    from SRs
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 90
    ) gt90s,
    (select count(*) as as120
    from SRs
    where status like '%Active%' and ABS(DATEDIFF(day, CreatedDate, GETDATE())) > 120
    ) gt120s

Collectives™ on Stack Overflow

How do I return multiple columns and rows in a SQL Count Query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related