SQLTeam.com | Weblogs | Forums

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

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.

hi

i tried to do this

hope this helps
:slight_smile: :slight_smile:

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

image
image

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

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

Thank you so much.

I will test and let you the outcome.

Cheers

Thanks. It works. I appreciate.