Will Will - 7 months ago 8
SQL Question

Doing Counts based on timestamps

I have a query that gives me the following results. This result set contains the times when jobs on a server started as well as when they finished.

JobName LatestStartTime LatestEndTime
Job1 2016-04-15 00:00:40.000 2016-04-15 00:07:40.000
Job2 2016-04-15 00:01:23.000 2016-04-15 00:17:37.000
Job3 2016-04-15 08:00:03.000 2016-04-15 08:18:05.000
Job4 2016-04-15 08:30:06.000 2016-04-15 08:57:21.000
Job5 2016-04-15 09:00:03.000 2016-04-15 09:07:49.000
Job6 2016-04-15 03:53:40.000 2016-04-15 03:53:41.000
Job7 2016-04-15 09:30:07.000 2016-04-15 11:36:35.000


On the other hand, I have a query that creates a temp table with 15 min intervals. As the following:

Increment
2016-04-15 00:00:00.000
2016-04-15 00:15:00.000
2016-04-15 00:30:00.000
2016-04-15 00:45:00.000
2016-04-15 01:00:00.000
2016-04-15 01:15:00.000
2016-04-15 01:30:00.000
2016-04-15 01:45:00.000
2016-04-15 02:00:00.000
2016-04-15 02:15:00.000
2016-04-15 02:30:00.000
2016-04-15 02:45:00.000


I want to know which jobs run at a determined increment. For example, my final result set should look something like:

Increment NumberOfJobs JobNames
2016-04-15 00:00:00.000 2 Job1, Job2
2016-04-15 00:15:00.000 1 Job2
2016-04-15 00:30:00.000 0 NULL


OR

Increment NumberOfJobs JobNames
2016-04-15 00:00:00.000 2 Job1
2016-04-15 00:00:00.000 2 Job2

2016-04-15 00:15:00.000 1 Job2
2016-04-15 00:30:00.000 0 NULL

Answer

The difficult part is getting the list of jobs on a single row. The counts are easy:

select t.increment, j.jobname,
       sum(count(*)) over (partition by t.increment) as countOfJobs
from times t left join
     jobs j
     on t.increment >= j.lasteststarttime and
        t.increment <= j.lastestendtime
group by t.increment, j.jobname
order by increment, jobname;

Getting the list requires a weird subquery in SQL Server:

select t.increment, count(*) as numJobs,
       stuff((select ', ' + j2.jobname
              from jobs j2
              where t.increment >= j2.lasteststarttime and
                    t.increment <= j2.lastestendtime
              for xml path ('')
             ), 1, 1, '') as jobs
from times t  left join
         jobs j
         on t.increment >= j.lasteststarttime and
            t.increment <= j.lastestendtime
group by t.increment
order by increment;

If the job names contain unusual characters (those that need escaping for XML), then the XML logic is slightly more complex. That seems unlikely for job names, though.