Will Will - 1 year ago 49
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download