We help IT Professionals succeed at work.

Microsoft SQL Server Query - SQL query row count

80 Views
Last Modified: 2019-02-11
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

ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

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
CERTIFIED EXPERT

Commented:
ISNULL() or COALESCE()..


Itzik Ben-Gan: T-SQL Fundamentals .

Author

Commented:
helpful solution.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.