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
Thean SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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 SAuthor 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

ste5anSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Thean SAuthor 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 DeveloperCommented:
ISNULL() or COALESCE()..


Itzik Ben-Gan: T-SQL Fundamentals .
Thean SAuthor Commented:
helpful solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.