question

SankaranRameshbabuRagavi-1606 avatar image
0 Votes"
SankaranRameshbabuRagavi-1606 asked AndreiFomitchev answered

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

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).

sql-server-generalsql-server-transact-sqlazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 commented

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

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

But how do I get the list of resources who submitted last week and not this week (status = saved/rejected) ?

0 Votes 0 ·
GuoxiongYuan-7218 avatar image GuoxiongYuan-7218 SankaranRameshbabuRagavi-1606 ·

Since I do not have your table structure, I guess it (suppose ResourceID is the key of the table):

 ;WITH CTE_Submit_Last_Week AS (
     SELECT *
     FROM YourTable
     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))
 ),
 CTE_Submit_This_Week AS (
     SELECT *
     FROM YourTable
     WHERE submitted >= DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())), 0)) AND submitted < DATEADD(dd,  7, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, GETDATE())), 0))
 )
    
 SELECT *
 FROM CTE_Submit_Last_Week AS lw
 LEFT JOIN CTE_Submit_This_Week AS tw ON tw.ResourceID = lw.ResourceID
 WHERE lw.[status] = 'saved' AND tw.[status] = 'rejected';
0 Votes 0 ·

Thank you so much, that worked perfectly fine.

One other question, is it possible to group by the startdate? Because I want it to be automatic to create reports in powerbi. For example, if I have to compare last week with the week prior to that, then I should be able to set the startdate (04/05/2021) and look for people who have not submitted time for the week of 04/05, but have submitted time for the week previous to that 03/29.

0 Votes 0 ·
Show more comments

My week starts on Monday and ends on Sunday

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered SankaranRameshbabuRagavi-1606 edited

Hi @SankaranRameshbabuRagavi-1606,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey Melissa,

I'm so sorry, but thank you for that information. Please find the below details,

CREATE TABLE TimeTracker(
[EmployeeID] [nchar](10) NOT NULL,
[EmpName] [nchar](10) NULL,
[Status] [varchar](50) NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL)
GO

insert into TimeTracker values
(1,'Sam','submitted','2021-3-29','2021-4-4'),
(2,'Jack','saved','2021-3-29','2021-4-4'),
(1,'Sam','saved','2021-4-5','2021-4-11'),
(2,'Jack','approved','2021-4-5','2021-4-11'),
(3,'John','rejected','2021-4-5','2021-4-11')


When I group by StartDate, I need those who have status (saved, rejected) in current week but status (submitted, approved) in previous week. This should be automatic, I should be able to look for any week.

Please let me know if that makes sense.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered SankaranRameshbabuRagavi-1606 commented

Hi @SankaranRameshbabuRagavi-1606,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The output is correct, but how about cases where,

(1,'Sam','submitted','2021-3-29','2021-4-4'),
(2,'Jack','saved','2021-3-29','2021-4-4'),
(1,'Sam','saved','2021-4-5','2021-4-11'),
(2,'Jack','approved','2021-4-5','2021-4-11'),
(1,'Sam','submitted','2021-4-5','2021-4-11')

So I should not get any output in this case because Sam has one saved and submitted status for the same week.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered SankaranRameshbabuRagavi-1606 commented

Hi @SankaranRameshbabuRagavi-1606,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SankaranRameshbabuRagavi-1606,

Could you please provide any update? Thanks.

If above is still not working, please provide more sample data and expected output.

Best regards
Melissa

0 Votes 0 ·

Hey Melissa, I'm so sorry I was away from work for the past one week. I will check and get back to you on the solution!

0 Votes 0 ·
AndreiFomitchev avatar image
0 Votes"
AndreiFomitchev answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.