Help needed on MS SQL query

Thean S
Thean S used Ask the Experts™
on
Hello All:

The below query returns row count of cases that are created for every 30 min. The below query displays slots only where row count exist (query not displays slots where row count is 0).

I need to display all slots with row count - even if row count is 0 (from 00:00:10.000 to 23:59:10.000). Can you please suggest on which line and what logic i need to add in below query

WITH Data
AS ( SELECT T.* ,
            DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(T.CreatedOn AS DATE), T.CreatedOn) / 30 * 30, CAST(CAST(T.CreatedOn AS DATE) AS DATETIME)) AS BucketStart,
            DATEADD(MINUTE, 30 + DATEDIFF(MINUTE, CAST(T.CreatedOn AS DATE), T.CreatedOn) / 30 * 30, CAST(CAST(T.CreatedOn AS DATE) AS DATETIME)) AS BucketEnd
     FROM   IB T )

SELECT   CONVERT(VARCHAR(20), D.BucketStart, 120) + ' - ' + CONVERT(VARCHAR(10), BucketEnd, 108) AS [DateTime],
         COUNT(*) As [Total Case Count]
FROM     Data D      
WHERE owneridname = 'MBO' 
AND CreatedOn between '2019-02-04 00:00:10.000' and '2019-02-04 23:59:10.000'
GROUP BY D.BucketStart, BucketEnd
ORDER BY D.BucketStart;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
So are these the slots you are looking to show?

And will there just be one day of data reported?

00:00:10 - 00:30:10
00:30:10 - 01:00:10
01:00:10 - 01:30:10
01:30:10 - 02:00:10
02:00:10 - 02:30:10
02:30:10 - 03:00:10
03:00:10 - 03:30:10
03:30:10 - 04:00:10
04:00:10 - 04:30:10
04:30:10 - 05:00:10
05:00:10 - 05:30:10
05:30:10 - 06:00:10
06:00:10 - 06:30:10
06:30:10 - 07:00:10
07:00:10 - 07:30:10
07:30:10 - 08:00:10
08:00:10 - 08:30:10
08:30:10 - 09:00:10
09:00:10 - 09:30:10
09:30:10 - 10:00:10
10:00:10 - 10:30:10
10:30:10 - 11:00:10
11:00:10 - 11:30:10
11:30:10 - 12:00:10
12:00:10 - 12:30:10
12:30:10 - 13:00:10
13:00:10 - 13:30:10
13:30:10 - 14:00:10
14:00:10 - 14:30:10
14:30:10 - 15:00:10
15:00:10 - 15:30:10
15:30:10 - 16:00:10
16:00:10 - 16:30:10
16:30:10 - 17:00:10
17:00:10 - 17:30:10
17:30:10 - 18:00:10
18:00:10 - 18:30:10
18:30:10 - 19:00:10
19:00:10 - 19:30:10
19:30:10 - 20:00:10
20:00:10 - 20:30:10
20:30:10 - 21:00:10
21:00:10 - 21:30:10
21:30:10 - 22:00:10
22:00:10 - 22:30:10
22:30:10 - 23:00:10
23:00:10 - 23:30:10

Open in new window


»bp

Author

Commented:
Yes, you're correct. I am looking just one day data of reported.

Author

Commented:
can someone please advise how to modify above query to achieve above results
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
You need to form a list of all 48 possible "slots", then left join your current query to that list, only then you will see all slots and the counts where there is a match.

Here is an example set of source data:
CREATE TABLE mytable(
   CreatedOn VARCHAR(19) NOT NULL PRIMARY KEY
);
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 08:13:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 08:23:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 08:33:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 08:43:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 08:53:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 10:03:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 10:13:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 10:23:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 10:33:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 10:43:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 10:53:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 11:03:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 11:13:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 11:23:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 11:33:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 11:43:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 11:53:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 12:03:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 12:13:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 12:23:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 12:33:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 12:43:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 14:47:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 14:57:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 15:07:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 15:17:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 15:27:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 15:37:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 15:47:56');
INSERT INTO mytable(CreatedOn) VALUES ('2019-02-11 15:57:56');

Open in new window

Now we generate the hours (x) * halfhour (y) * date (z)  number of "slots" using 3 derived tables cross joined together
with data as (
        select
              cast(CreatedOn as date) dt
            , datepart(hour,CreatedOn)  h
            , round(datepart(minute,CreatedOn) / 30,0) * 30 m
            , count(*) cnt
        from mytable
        group by
              cast(CreatedOn as date)
            , datepart(hour,CreatedOn)
            , round(datepart(minute,CreatedOn) / 30,0) * 30
    )
select
        CONVERT(VARCHAR(20), dateadd(minute,y.m,dateadd(hour,x.h,z.dt)), 120)  
          + ' - ' 
          + CONVERT(VARCHAR(10), dateadd(minute,y.m+30,dateadd(hour,x.h,z.dt)), 108) AS [DateTime]
      , coalesce(data.cnt,0) cnt
from ( 
    values (8),(9),(10),(11),(12),(13),(14),(15),(16),(17)
   ) x (h)
cross join (
    select * 
    from ( 
        values (0),(30)
       )  y  (m)
    ) y
cross join (select distinct cast(dt as datetime) dt from data) z
left join data on x.h = data.h and y.m = data.m and z.dt = data.dt
order by
    z.dt, x.h, y.m
;

Open in new window

The result is:
+----+--------------------------------+-----+
|    |            DateTime            | cnt |
+----+--------------------------------+-----+
|  1 | 2019-02-11 08:00:00 - 08:30:00 |   2 |
|  2 | 2019-02-11 08:30:00 - 09:00:00 |   3 |
|  3 | 2019-02-11 09:00:00 - 09:30:00 |   0 |
|  4 | 2019-02-11 09:30:00 - 10:00:00 |   0 |
|  5 | 2019-02-11 10:00:00 - 10:30:00 |   3 |
|  6 | 2019-02-11 10:30:00 - 11:00:00 |   3 |
|  7 | 2019-02-11 11:00:00 - 11:30:00 |   3 |
|  8 | 2019-02-11 11:30:00 - 12:00:00 |   3 |
|  9 | 2019-02-11 12:00:00 - 12:30:00 |   3 |
| 10 | 2019-02-11 12:30:00 - 13:00:00 |   2 |
| 11 | 2019-02-11 13:00:00 - 13:30:00 |   0 |
| 12 | 2019-02-11 13:30:00 - 14:00:00 |   0 |
| 13 | 2019-02-11 14:00:00 - 14:30:00 |   0 |
| 14 | 2019-02-11 14:30:00 - 15:00:00 |   2 |
| 15 | 2019-02-11 15:00:00 - 15:30:00 |   3 |
| 16 | 2019-02-11 15:30:00 - 16:00:00 |   3 |
| 17 | 2019-02-11 16:00:00 - 16:30:00 |   0 |
| 18 | 2019-02-11 16:30:00 - 17:00:00 |   0 |
| 19 | 2019-02-11 17:00:00 - 17:30:00 |   0 |
| 20 | 2019-02-11 17:30:00 - 18:00:00 |   0 |
+----+--------------------------------+-----+

Open in new window


Please note that for brevity I just used hours 8 to 17, you should expand that to 0 to 23

Author

Commented:
Hi PortletPaul,

Is it possible this without using any temp table (mytable)? Just i want to pass date time in query like CreatedOn between '2019-02-04 00:00:10.000' and '2019-02-04 23:59:10.000'..... or can you please help me to make it dynamic for date and time slots instead of hard coding date time slots...
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I was NOT trying to suggest you needed "mytable" or any temporary table, just place your required table and where clause inside the "data" cte

with data as (
        select
              cast(CreatedOn as date) dt
            , datepart(hour,CreatedOn)  h
            , round(datepart(minute,CreatedOn) / 30,0) * 30 m
            , count(*) cnt
        from IB                 --  use the real table here
        WHERE owneridname = 'MBO' 
        AND CreatedOn >= '20190204' and CreatedOn < '20190205' -- I do NOT recommend using between for date ranges
        group by
              cast(CreatedOn as date)
            , datepart(hour,CreatedOn)
            , round(datepart(minute,CreatedOn) / 30,0) * 30
    )
...

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
or,
declare @from as date = '20190204'
;with data as (
        select
              cast(CreatedOn as date) dt
            , datepart(hour,CreatedOn)  h
            , round(datepart(minute,CreatedOn) / 30,0) * 30 m
            , count(*) cnt
        from IB                 --  use the real table here
        WHERE owneridname = 'MBO' 
        AND CreatedOn >= @from and CreatedOn < dateadd(day,1,@from)
        group by
              cast(CreatedOn as date)
            , datepart(hour,CreatedOn)
            , round(datepart(minute,CreatedOn) / 30,0) * 30
    )
...

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Answered via private session. Duration 00:02:15

[edit]
... no it wasn't - there was an attempted session but I could not hear Thean S

Author

Commented:
Tried below query, there is syntax error in below query. Please help me to correct below error

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.
with data as (
        select
              cast(CreatedOn as date) dt
            , datepart(hour,CreatedOn)  h
            , round(datepart(minute,CreatedOn) / 30,0) * 30 m
            , count(*) cnt
        from IB                 --  use the real table here
        WHERE owneridname = 'MBO' 
        AND CreatedOn >= '20190204' and CreatedOn < '20190205' -- I do NOT recommend using between for date ranges
        group by
              cast(CreatedOn as date)
            , datepart(hour,CreatedOn)
            , round(datepart(minute,CreatedOn) / 30,0) * 30
    )
--...

Open in new window

Author

Commented:
Hi PortletPaul,

I have fixed syntax error. Please see below updated query and correct me if there is any logic error.

Btw,
1. The below query returns data (count) starting from 6:00 to 22:30, but i need data (count) starting from 00:00 to 23:59 OR from 00:00:10 to 23:59:10
2. In the attached output file (highlighted with Yellow), after 10:30 its showing 11:30. Here, the interval is showing 1 hr. I need interval of every 30 minutes. If there is no activity (no row count) from 10:30 to 11:00, the row count should be 0, i need to display row count of every 30 minutes of one day of data.

Please find the attached query output file and help me on this.
Thanks in advance.
with data as (
        select
              cast(CreatedOn as date) [Date]
            , datepart(hour,CreatedOn)  [Hour]
            , round(datepart(minute,CreatedOn) / 30,0) * 30 [Minute]
            , count(*) [Total Count]
        from IB                 
        WHERE owneridname = 'MBO' 
        AND CreatedOn >= '20190204' and CreatedOn < '20190205' 
        group by
              cast(CreatedOn as date)
            , datepart(hour,CreatedOn)
            , round(datepart(minute,CreatedOn) / 30,0) * 30
    )
select *
from data

Open in new window

Output.xlsx
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Give this a try, in testing here it seems to get pretty close to what you wanted I think...

;WITH 
    Series(TimeSlot) AS (
        SELECT 0 AS TimeSlot
        UNION ALL
        SELECT TimeSlot + 30
        FROM   Series
        WHERE  TimeSlot < 1410
    )
SELECT CONVERT(varchar(5),DATEADD(minute,s.TimeSlot,0),108) TimeSlot,
       d.Count Count
FROM Series s
LEFT OUTER JOIN (
    SELECT (DATEPART(hour, CreatedOn) * 60) + ROUND(DATEPART(minute, CreatedOn) / 30, 0) * 30 TimeSlot,
           COUNT(*) Count
    FROM IB
    WHERE OwnerIdName = 'MBO'
    AND CreatedOn >= '20190204' 
    AND CreatedOn < '20190205'
    GROUP BY (DATEPART(hour, CreatedOn) * 60) + ROUND(DATEPART(minute, CreatedOn) / 30,0) * 30) AS d
ON d.TimeSlot = s.TimeSlot
ORDER BY 1;

Open in new window


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial