Avatar of Thean S
Thean S
 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
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Thean S

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Untitled.png
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ste5an

ISNULL() or COALESCE()..


Itzik Ben-Gan: T-SQL Fundamentals .
Thean S

ASKER
helpful solution.