CODEWITHSUNDEEP
sqlsql-serversql-server-2014
David
David

Reputation: 137

Pulling last week M-F range from any current week day

SELECT d.i_EmpID as 'EmpID',m.c_EmployeeFirstName as 'First Name', 
m.c_employeeLastName as 'Last Name', d.c_date as 'Date', d.c_A as 'A', d.c_M 
as 'M', d.c_P as 'P', d.c_lp as 'Lp'
,'School' as Company
FROM [table1].[dbo].[tbl_daily] d
inner join [table2].[dbo].[tbl_emp] m
on d.[i_EmpID] = m.[i_EmpID]
where left(c_date, 2)=datepart(month, getdate()-1) and 
right(c_date,4)=datepart(year,getdate()-1) and substring(c_date, 
4,2)=datepart(day,getdate()-3)

Hello,

I am trying to pull last week date range past off of current date. Right now I have 5 separate queries unioned for the end of the code (day,getdate()-3) to be the other 5 days in the week (I am looking for Monday to Friday). This is essentially my query without the other 4 unions to be able to pull Monday to Friday and I already this is not the best way at all to do it and in fact it doesn't even work today.

I am able to get this done in MySQL because it has a built in week function and I can just pull last week, however I can't figure out how to pull last week in SQL because the language is different. My issue is that my code worked yesterday, however due to the fact it is subtracting the days, on Tuesday to Friday, it won't work/it doesn't work. I am by no means an expert with using dates in SQL and I am at a loss.

I know there has to be a better way, I've spent about 4 hours reading various guides and examples but I cannot seem to find an example of specifically what I am looking for.

Long and short of it is that I am trying pull last week (example: 9/24 to 9/28) any day of the current week that I run it. So even if I run my report on Friday(10/5), I am able to see 9/24 to 9/28 and the similarly next week to be able to pull 10/1 to 10/5 of this week. Those are the tables and columns I am pulling. Also, if it matters, this is not a school project but a work related project I was tasked with.

Any guidance would be greatly appreciated. Thank you in advanced.

edit: Sample Data:

EID     First   Last    Date          A  M  P   LP        Company
5303    Kent    Grit    09/25/2018    9  44 54   3        Company 1
6890    Lola    Grif    09/28/2018    5  4  3             Company 1
6272    Paul    Hammer  09/26/2018    1  2  4    4        Company 1
6273    Joe     Hammer  09/24/2018    11 8  7             Company 1

Upvotes: 0

Views: 110

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

Is this what you're looking for? 

...
WHERE
  CAST(c_date AS DATE) >= DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE())-1, 0)
  AND
  CAST(c_date AS DATE) < DATEADD(DAY, +5, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE())-1, 0))

It will pull last Monday at midnight and last Saturday at midnight (so you get the last second of Friday in your result set) from Sunday through Saturday.

Edit: Added the CAST per comments.

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be accomplished with dateadd and datepart functions with some arithmetic.

with dates(dt) as (select '2018-10-02' union all
                   select '2018-10-01' union all
                   select '2018-10-03' union all
                   select '2018-10-04' union all
                   select '2018-10-05' union all
                   select '2018-10-06' union all
                   select '2018-10-11' 
                  ) 
--Actual query to be executed by replacing the table name
select dt,
dateadd(day,-datepart(dw,cast(dt as date))-5,cast(dt as date)) as strt,
dateadd(day,-datepart(dw,cast(dt as date))-1,cast(dt as date)) as ed
from dates --replace this with the table being used

Upvotes: 1

Related Questions