asked on

Microsoft SQL Server Query - SQL query row count

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

ste5an

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?

Thean S

ASKER

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

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thean S

ASKER

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.

ste5an

ISNULL() or COALESCE()..

Itzik Ben-Gan: T-SQL Fundamentals .

Thean S

ASKER

helpful solution.