Microsoft SQL Server Query - SQL query row count

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

Below query is returning 0 row count for all time slots, but this is not correct. There are some row count for some time slots. Can you please review below query and help me where i need to modify logic in below query.

I'm herewith attaching output of below query.

Thanks in advance.
DECLARE @BeginTime CHAR(19) = '2019-02-04 00:00:10.000'
DECLARE @EndTime CHAR(19) = '2019-02-04 23:59:10.000'

;with cte1 as (
 select [date1] + ' - ' + [date2]  as [DateTime] from (
select convert(varchar(13),dateadd(mi,sv.number*30,@BeginTime),108) as [date1] ,lead(convert(varchar(13),dateadd(mi,sv.number*30,@BeginTime),108) )over(order by sv.number) as [date2]
from master..spt_values AS sv 
 where   datediff(mi,dateadd(mi,sv.number*30,@BeginTime),@EndTime)>=0 
 and sv.number>=0 and sv.type='p')t where [date2] is not null  
)

 ,cte2 as (SELECT   CONVERT(VARCHAR(20), D.BucketStart, 120) + ' - ' + CONVERT(VARCHAR(10), BucketEnd, 108) AS [DateTime],
         COUNT(*) As [Total Case Count]
FROM ( 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 ) D      
WHERE name = 'MBO' 
AND CreatedOn between '2019-02-04 00:00:10.000' and '2019-02-04 23:59:10.000'
GROUP BY D.BucketStart, BucketEnd)

select a.[DateTime], case when b.[Total Case Count] is not null then b.[Total Case Count] else 0 end as  [Total Case Count]
 from cte1 a left join cte2 b on a.[DateTime]=b.[DateTime]

Open in new window

Book1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Start by using the proper data type: DATE. Your mixing strings with dates and date/times... this is the root of your problem. The inner sub-query of your first CTE already returns no rows:

DECLARE @BeginTime CHAR(19) = '2019-02-04 00:00:10.000';
DECLARE @EndTime CHAR(19) = '2019-02-04 23:59:10.000';

SELECT CONVERT(VARCHAR(13), DATEADD(mi, sv.number * 30, @BeginTime), 108) AS [date1] ,
       LEAD(CONVERT(VARCHAR(13), DATEADD(mi, sv.number * 30, @BeginTime), 108)) OVER ( ORDER BY sv.number ) AS [date2]
FROM   master..spt_values sv
WHERE  DATEDIFF(mi, DATEADD(mi, sv.number * 30, @BeginTime), @EndTime) >= 0
       AND sv.number >= 0
       AND sv.type = 'p';

Open in new window


What are you trying to do here? Half-hour time buckets?

Author

Commented:
Hi Ste5an,

Yes, its Half-hour time buckets :)

In below query we have showed row count only where activity is exist. But now I want to show all time slot activities of day from 00:00:10 to 23:59:10. If there is no activity in half hour bucket, i want to show as 0.

Btw, are you suggesting to change  CHAR(19) to DATETIME ?

In below query we have showed row count only where activity is exist.
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

Senior Developer
Commented:
Your bucket tally does not return anything.. e.g. this works:

DECLARE @DayFilter DATE = '20190109';

WITH MockIB
AS ( SELECT 'MBO' AS owneridname ,
            O.create_date AS CreatedOn
     FROM   sys.objects O ) ,
     Filtered
AS ( SELECT *
     FROM   MockIB MIB
     WHERE  MIB.owneridname = 'MBO'
            AND MIB.CreatedOn
            BETWEEN @DayFilter AND DATEADD(DAY, 1, @DayFilter)) ,
     AvailableBuckets
AS ( SELECT  DISTINCT DATEADD(MINUTE, SV.number * 30, CAST(@DayFilter AS DATETIME)) AS BucketDateTime ,
                      SV.number AS BucketNumber
     FROM    master.dbo.spt_values SV
     WHERE   SV.number
     BETWEEN 0 AND 47 ) ,
     DataBuckets
AS ( SELECT F.* ,
            DATEDIFF(MINUTE, CAST(F.CreatedOn AS DATE), F.CreatedOn) / 30 AS BucketNumber
     FROM   Filtered F ) ,
     AggDataBuckets
AS ( SELECT   D.BucketNumber ,
              COUNT(*) AS Cnt
     FROM     DataBuckets D
     GROUP BY D.BucketNumber )
SELECT   A.BucketDateTime ,
         A.BucketNumber ,
         D.Cnt
FROM     AvailableBuckets A
         LEFT JOIN AggDataBuckets D ON A.BucketNumber = D.BucketNumber
ORDER BY A.BucketNumber;

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Ste5an,

1.
SELECT *
     FROM   MockIB MIB

I have replaced MockIB with my correct table name (code line number: 9). Please correct me if I am wrong.

2. After replace, the output looks like below. Can you please suggest, i want to display 0 instead of NULL (where there is no activity in bucket). on which line of code we have to change this.
Untitled.png
ste5anSenior Developer

Commented:
ISNULL() or COALESCE()..


Itzik Ben-Gan: T-SQL Fundamentals .

Author

Commented:
helpful solution.

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