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).
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).
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
But how do I get the list of resources who submitted last week and not this week (status = saved/rejected) ?
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';
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.
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.
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.
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.
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.
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.
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
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!
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
15 people are following this question.