Reducing an extensive Query

Question

I am compiling historical data on work tickets from our db. I am using the last day of the month for the past 12 months. On that date I want to see how many tickets were open (datecreated before or equal to last day of month and dateclose after last day or null). How many of those tickets had been open for more than 30 days as of the last day of the month, and how many tickets were closed during that month.

My query returns 4 values for each of the previous 12 months. I developed a query where I gathered the data for one month at a time and then used Union to get the next month and so forth.

I am now attempting to develop a query which does not need a Union for each month of data collected. Here is a sample month.

Select Convert(Varchar,
               DATEADD(day,
                       -1,
                       DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 12, 0)),
               101) as [ DateRun ],
       t1. [ data1 ],
       sum(t2. [ data2 ] + t3. [ data3 ]) as [ Total Open ],
       (t1. [ data1 ] + 0.0) /
       sum((t2. [ data2 ] + 0.0) + (t3. [ data3 ] + 0.0)) as [ Percent Aged Over 30 Days ]
  From (Select count(*) as [ data1 ]
          From (Select s.ticketnumber, s.datecreated, s.dateclosed
                  from mydb2 i
                 inner join mydb1 s
                    on i.ticketNumber = s.ticketNumber
                 Where datediff(dd,
                                s.dateCreated,
                                DATEADD(day,
                                        -1,
                                        DATEADD(mm,
                                                DATEDIFF(m, 0, GETDATE()) - 12,
                                                0))) > '30'
                   AND (s.dateclosed is null OR
                        s.dateclosed >=
                        DATEADD(day,
                                -1,
                                DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 12, 0)))
                 Group by s.ticketNumber, s.datecreated, s.dateclosed) as tb1) as t1,
       (Select count(*) as [ data2 ]
          From (Select s.ticketnumber, s.datecreated, s.dateclosed
                  from mydb2 i
                 inner join mydb1 s
                    on i.ticketNumber = s.ticketNumber
                 Where s.datecreated <
                       DATEADD(day,
                               -1,
                               DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 12, 0))
                   AND (s.dateclosed is null OR
                       s.dateclosed >=
                       DATEADD(day,
                                -1,
                                DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 12, 0)))
                 Group by s.ticketNumber, s.datecreated, s.dateclosed) as tb2) as t2,
       (Select count(*) as [ data3 ]
          From (Select s.ticketnumber, s.datecreated, s.dateclosed
                  from mydb2 i
                 inner join mydb1 s
                    on i.ticketNumber = s.ticketNumber
                 Where s.dateclosed >
                       DATEADD(day,
                               -1,
                               DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 13, 1))
                   AND s.dateclosed <
                       DATEADD(day,
                               -1,
                               DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 12, 0))
                 Group by s.ticketNumber, s.datecreated, s.dateclosed) as tb3) as t3
 Group by t1. [ data1 ]

Through research I was able to get the past 12 months last day with the following code, but I have not been able to update the code above so that I don't need 12 Unions.

Select dateadd(month, 1 + datediff(month, 0, s.datecreated), -1) as [ Date Run ]
  from mydb1 s
 Where s.datecreated >
       DATEADD(day, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 12, 0))
   and s.datecreated <
       DATEADD(day, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
 Group by dateadd(month, 1 + datediff(month, 0, s.datecreated), -1)
 Order by 1

Answer 1

You might try generating the months using a CTE, use that as the basis for your query, then run the aggregates as subqueries. In other words, something like this:

with Months( ClosingDate, n ) as
(
    select DATEADD(dd, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) as ClosingDate, 1 as n
    union all
    select DATEADD(dd, -1, DATEADD(mm, -1, DATEADD(dd, 1, ClosingDate))), n + 1
    from Months
    where n < 12
) 
select DATENAME(mm, m.ClosingDate) MonthLabel, DATEADD(dd, 1 - DATEPART(dd, m.ClosingDate), m.ClosingDate) OpeningDate, m.ClosingDate,
ISNULL((select COUNT(*)
    from mydb1 s
    where s.dateCreated < DATEADD(dd, -29, m.ClosingDate)
      and (s.dateClosed is null or s.dateClosed >= DATEADD(dd, 1, m.ClosingDate))
), 0) [Total Open 30+ Days]
from Months m

For clarity I included only one of the aggregates and omitted your joins and grouping.