troubleshooting Question

Microsoft SQL Server Query - SQL query row count

Avatar of Thean S
Thean S asked on
DatabasesMicrosoft SQL ServerSQL
6 Comments1 Solution86 ViewsLast Modified:
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]
Book1.xlsx
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros