How to compare current week's data with previous week data

Question

How do I write a query compare who submitted the time last week and who did not submit it this week in SQL server?
I have a table with the resourcename, status (saved, rejected, submitted, approved), and startdate (1st day of every week).

Answer 1

It depends on which weekday is the first day of the week. Here are the scripts to get last week start and end dates and this week start and end dates:

-- Week starts on Sunday and ends on Saturday
SELECT DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)) AS Last_Week_Start_Date,
       DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)) AS Last_Week_End_Date,
    DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()), 0)) AS This_Week_Start_Date,
    DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()), 0)) AS This_Week_End_Date;

-- Week starts on Monday and ends on Sunday
SELECT DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())) - 1, 0)) AS Last_Week_Start_Date,
       DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())) - 1, 0)) AS Last_Week_End_Date,
    DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())), 0)) AS This_Week_Start_Date,
       DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())), 0)) AS This_Week_End_Date;

In your WHERE clause, when you use submitted < Last_Week_End_Date, you need to add one more day, i.e., if Sunday is the first day of the week:

WHERE submitted >= DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0)) AND submitted < DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - 1, 0))   -- 2021-04-11 00:00:00.000

if Monday is the first day of the week:

WHERE submitted >= DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())) - 1, 0)) AND submitted < DATEADD(dd,  7, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())) - 1, 0))   -- 2021-04-12 00:00:00.000

Answer 2

Hi @Sankaran Rameshbabu, Ragavi ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Please also refer below example and check whether it is helpful:

create table TableSA  
(ResourcenID int,  
resourcename varchar(20),  
status varchar(20),  
startdate date)  
  
insert into TableSA values  
(1,'A','saved','2021-3-29'),  
(2,'B','saved','2021-3-29'),  
(1,'A','rejected','2021-4-5'),  
(2,'B','approved','2021-4-5'),  
(3,'C','saved','2021-4-5'),  
(4,'D','saved','2021-4-5'),  
(3,'C','submitted','2021-4-12'),  
(4,'D','rejected','2021-4-12')  
  
declare @startdate date='2021-04-05'  --define your startdate here  
  
select a.*   
from   
(select * from TableSA where startdate=dateadd(DAY,-7,@startdate)) a  
left join   
(select * from TableSA where startdate=@startdate) b  
on a.ResourcenID=b.ResourcenID   
where a.status='saved' and b.status='rejected'  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Sankaran Rameshbabu, Ragavi ,

Please refer below:

select a.*   
from TimeTracker a   
left join TimeTracker b   
on a.StartDate=dateadd(day,7,b.StartDate) and a.EmployeeID=b.EmployeeID  
where a.Status in ('saved','rejected')   
and b.Status in ('submitted','approved')  

Output:

EmployeeID	EmpName	Status	StartDate	EndDate  
1         	Sam       	saved	2021-04-05	2021-04-11  

If above is not working, please provide the expected output or more sample data. Thanks.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Sankaran Rameshbabu, Ragavi ,

Please refer below updated query and check whether it is working.

;with cte as (  
select *, IIF(Status in ('saved','rejected'),1,0) Status1  
from TimeTracker)  
,cte1 as (  
select EmployeeID,EmpName,StartDate,status1  
,count(Status1) over (partition by EmployeeID,EmpName,StartDate) count  
from cte)  
select a.EmployeeID,a.EmpName,a.StartDate  
from cte1 a  
left join cte1 b   
on a.StartDate=dateadd(day,7,b.StartDate) and a.EmployeeID=b.EmployeeID  
where a.Status1=1 and b.Status1=0 and a.count=1  

If above is still not working, please provide more sample data, enough to illustrate all angles of the problem, and expected output. Thanks.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

DECLARE @status TABLE (status_id Int, name VarChar(50))
INSERT INTO @status VALUES (1, 'saved'),(2, 'rejected'),
(3,'submitted'),(4,'approved');
SELECT * FROM @status
DECLARE @res TABLE (res_id Int, status_id Int,
res_name VarChar(50), start_date Date);
INSERT INTO @res VALUES 
(1,3,'res01','2021-03-17'),
(2,1,'res02','2021-03-24'),
(1,4,'res01','2021-03-24')

SELECT r1.res_name, r1.start_date AS this_week,s1.name,
r2.start_date AS prev_week, s2.name
FROM @res r1 LEFT JOIN @res r2 
ON r2.res_id = r1.res_id AND
DatePart(ww,r2.start_date)=DatePart(ww,r1.start_date)-1
JOIN @status s1 ON s1.status_id = r1.status_id
JOIN @status s2 ON s2.status_id = r2.status_id
UNION ALL
SELECT r1.res_name, r1.start_date AS this_week,s1.name,
NULL AS prev_week, NULL
FROM @res r1 JOIN @status s1 ON s1.status_id = r1.status_id
WHERE r1.res_name NOT IN (
    SELECT r2.res_name FROM @res r2
    WHERE r2.res_id = r1.res_id AND
    DatePart(ww,r2.start_date)=DatePart(ww,r1.start_date)-1
)
ORDER BY r1.res_name, r1.start_date


res_name    this_week   name    prev_week   name
res01   2021-03-17  submitted   NULL    NULL
res01   2021-03-24  approved    2021-03-17  submitted
res02   2021-03-24  saved   NULL    NULL