CODEWITHSUNDEEP
sqlsql-serverdatetime
Meenu Shankar
Meenu Shankar

Reputation: 63

SQL fetch data for the present year until previous week friday

Am trying to fetch data in SQL until previous week Friday for the present year from the datetime field fetchDate

I tried something like this but it fetches until today 

(year(fetchDate) = year(GETDATE()) and month(fetchDate) <= month(GETDATE()) and day(fetchDate) <= day(GETDATE()))

whereas 

(year(fetchDate) = year(GETDATE()) and month(fetchDate) <= month(GETDATE()) and day(fetchDate) <= day(DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4)))

brings me data only for the present month of the year until last week.

Upvotes: 0

Views: 76

Answers (2)

Tarek Salha
Tarek Salha

Reputation: 384

I would rather go for the following solution as it is sargable. Inside the WHERE predicate we are not wrapping the fetchdate into a function (e.g. YEAR(Fetchdate)), the query optimizer could use existing indexes and does not have to scan the whole table. Especially for BI workloads where this query is common, it is extremely important to optimize for that a lot of records are queried. It comes at the cost of little less readability

declare @tab table
(
    fetchdate datetime
)
insert into @tab
values ('2019-01-01'),('2019-03-15'),('2018-12-31'),('2019-03-16')
SELECT 
*,
case when datepart(weekday, getdate()) >5 then
 DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) end as TestLastFriday,
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) as TestFirstDayOfYear
FROM @tab
where 
    fetchdate <= case when datepart(weekday, getdate()) >5 then DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
                 else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) end and 
    fetchdate >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

Upvotes: 1

Alex Harris
Alex Harris

Reputation: 6392

I believe what you need is the following:

WHERE YEAR(fetchDate) = YEAR(CURDATE()) 
AND fetchDate < DATE_SUB(NOW(), INTERVAL ((7 + WEEKDAY(DATE_SUB(NOW(), INTERVAL 1 WEEK)) - 4) % 7) DAY)

We get how many days ago last week's Friday was with:

((7 + WEEKDAY(DATE_SUB(NOW(), INTERVAL 1 WEEK)) - 4) % 7)

Upvotes: 1

Related Questions