How to get count of Current day total, Previous day total and their Variance

JimCollins · July 27, 2019, 6:23am

Hello Guys,

I would like to thank you for the wonderful job you are doing.

I need help my query. I run a report every day to count the total numbers of survey that come into the system. What I want to do is to get the count of today(current day) and the count of Yesterday total from my query and get the difference if any. I have included some same data, hopefully this should help. I am using SQL 2102

My desire output should be like:

Category Today_Reporting_Total Previous_Day_Reporting_Total Total_Difference
Survey1 10 12 2

DECLARE @Prev_Current TABLE
(
Category Varchar(255),
ContactID int,
EventDateTime Datetime
)

INSERT INTO @Prev_Current (Category, ContactID,EventDateTime)
VALUES
('Survey1', 245211, '2019-04-26 16:41:08.497'),
('Survey1', 245847, '2019-03-23 10:09:35.390'),
('Survey1', 245914, '2019-03-31 21:47:47.070'),
('Survey1', 246332, '2019-04-15 13:43:02.657'),
('Survey1', 246333, '2019-04-15 13:50:01.473'),

('Survey1', 245212, '2019-07-26 11:53:48.520'),
('Survey1', 245849, '2019-07-27 12:53:48.520'),
('Survey1', 245918, '2019-07-27 10:53:48.520'),
('Survey1', 246330, '2019-07-26 14:53:48.520'),
('Survey1', 246331, '2019-07-26 15:53:48.520');

Select c.Category
,Count(c.ContactID) AS Today_Reporting_Total
,Count(p.ContactID) AS Previous_Day_Reporting_Total
,Count(c.ContactID) - Count(p.ContactID) as Total_Difference
from @Prev_Current AS C
left outer join @Prev_Current AS P on c.ContactID = p.ContactID
--AND P.EventDateTime= DATEADD(d,-1,c.EventDateTime)

Group by c.Category

Thanking you in advance.

harishgg1 · July 27, 2019, 7:27am

hi

i tried to do this

hope this helps

i have used CTE's
cte's are temporary tables in T-SQL ( common table expressions )

drop create data ..

drop table tempdata 
go 

create table tempdata
(
Category Varchar(255),
ContactID int,
EventDateTime Datetime
)

INSERT INTO tempdata (Category, ContactID,EventDateTime)
VALUES
('Survey1', 245211, '2019-04-26 16:41:08.497'),
('Survey1', 245847, '2019-03-23 10:09:35.390'),
('Survey1', 245914, '2019-03-31 21:47:47.070'),
('Survey1', 246332, '2019-04-15 13:43:02.657'),
('Survey1', 246333, '2019-04-15 13:50:01.473'),

('Survey1', 245212, '2019-07-26 11:53:48.520'),
('Survey1', 245849, '2019-07-27 12:53:48.520'),
('Survey1', 245918, '2019-07-27 10:53:48.520'),
('Survey1', 246330, '2019-07-26 14:53:48.520'),
('Survey1', 246331, '2019-07-26 15:53:48.520');

select * from tempdata order by EventDateTime
go

SQL ....

DECLARE @today DATETIME = '2019-07-27 10:53:48.520'; 

WITH ctetoday 
     AS (SELECT Count(*) AS Today_Reporting_Total 
         FROM   tempdata 
         WHERE  Cast(eventdatetime AS DATE) = Cast(@today AS DATE)), 
     cteprevious 
     AS (SELECT Count(*) AS Previous_Day_Reporting_Total 
         FROM   tempdata 
         WHERE  Cast(eventdatetime AS DATE) < Cast(@today AS DATE)) 
SELECT today_reporting_total, 
       previous_day_reporting_total, 
       today_reporting_total - previous_day_reporting_total AS Total_Difference 
FROM   ctetoday, 
       cteprevious 

go

JimCollins · July 27, 2019, 3:48pm

Hi harishgg1

Thank you so much. Maybe, I did not explain myself very well. What I really want is something like cumulative. For example, if I run the query yesterday and the count is 2, therefore today count should be 2 plus the count from yesterday. e.g. Yesterday count is 10. Today should be 10 plus today's run and the difference.

Can I please add category to the output. The joins, do I need to Join cteprevious on ctetoday on Category or which key should I used in case I have multiple category.

Thank you for taking time out this weekend to put smile on someone face.

Cheers

harishgg1 · July 28, 2019, 7:03am

hi Jim

Could this be what you are looking for ?????

Only hard part is understanding what you want !!!

I have put Survey1 and Survey2 data

drop create data ....

drop table #tempdata 
go 

create table #tempdata
(
Category Varchar(255),
ContactID int,
EventDateTime Datetime
)

INSERT INTO #tempdata (Category, ContactID,EventDateTime)
VALUES
('Survey1', 245211, '2019-04-26 16:41:08.497'),
('Survey1', 245847, '2019-03-23 10:09:35.390'),
('Survey1', 245914, '2019-03-31 21:47:47.070'),
('Survey1', 246332, '2019-04-15 13:43:02.657'),
('Survey1', 246333, '2019-04-15 13:50:01.473'),

('Survey1', 245212, '2019-07-26 11:53:48.520'),
('Survey1', 245849, '2019-07-27 12:53:48.520'),
('Survey1', 245918, '2019-07-27 10:53:48.520'),
('Survey1', 246330, '2019-07-26 14:53:48.520'),
('Survey1', 246331, '2019-07-26 15:53:48.520'),

('Survey2', 245100, '2019-04-26 16:41:08.497'),
('Survey2', 245847, '2019-03-23 10:09:35.390'),
('Survey2', 235914, '2019-03-31 21:47:47.070'),
('Survey2', 246332, '2019-04-15 13:43:02.657'),
('Survey2', 246333, '2019-04-15 13:50:01.473'),

('Survey2', 265412, '2019-07-26 11:53:48.520'),
('Survey2', 245849, '2019-07-27 12:53:48.520'),
('Survey2', 267918, '2019-07-27 10:53:48.520'),
('Survey2', 246330, '2019-07-26 14:53:48.520'),
('Survey2', 246881, '2019-07-26 15:53:48.520')
go 

select * from #tempdata
go

SQL ..

; WITH cte_cumtotal 
     AS (SELECT *, 
                Count(category) 
                  OVER( 
                    partition BY category 
                    ORDER BY eventdatetime) AS cum_total 
         FROM   #tempdata), 
     cte_lag 
     AS (SELECT category, 
                eventdatetime, 
                cum_total, 
                Lag(cum_total) 
                  OVER ( 
                    partition BY category 
                    ORDER BY category, eventdatetime) AS lag_total 
         FROM   cte_cumtotal) 
SELECT category, 
       eventdatetime, 
       cum_total             AS Today_Reporting_Total, 
       lag_total             AS Previous_Reporting_Total, 
       cum_total - lag_total AS Total_Difference 
FROM   cte_lag

JimCollins · July 28, 2019, 8:37am

Thank you so much.

I will test and let you the outcome.

Cheers

JimCollins · July 30, 2019, 4:40pm

Thanks. It works. I appreciate.