CODEWITHSUNDEEP
sqlsql-server
Tairoc
Tairoc

Reputation: 661

How to pull previous day's records for weekdays only?

I have this simple query that returns previous day's data. 

SELECT * FROM mytable 
WHERE DATEDIFF(day,CONVERT(datetime,mytable.mydate, 121),GETDATE()) = 1

Using this query becomes an issue on Mondays since the previous day is Sunday but there is no data on Sunday.

So I would like the query to retrieve Friday's data (last activity) on Monday

Any ideas how to do this?

Just a little note about date data type. The date field is of VARCHAR data type.

I inherited the database and has been in use now for over 10 years.

Thanks in advance for your assistance.

Upvotes: 1

Views: 1325

Answers (3)

steamrolla
steamrolla

Reputation: 2491

Declare a datetime variable, and, with DATEPART(), determine which day of the week GETDATE() is (which day the query is being run). Then, on Mondays, change our date by negative two days.

declare @queryDate datetime;
set @queryDate = case when DATEPART(dw, GETDATE()) = 1 then DATEADD(d, -2, GETDATE()) else GETDATE() end;
select * from mytable 
where DATEDIFF(day,CONVERT(datetime,mytable.mydate, 121),@queryDate) = 1

Upvotes: 1

edosoft
edosoft

Reputation: 17281

You could try something like this. It asumes sunday at the first day of the week. 

SELECT * FROM mytable 
WHERE DATEDIFF(day,CONVERT(datetime,mytable.mydate, 121),GETDATE()) = case datepart(dw, GETDATE())
when 2 then 3 --read 3 days back on a monday
when 1 then 2 --read 2 days back on a sunday
else 1 end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

How about this?

SELECT TOP 1 WITH TIES t.*
FROM mytable t
WHERE mydate < CAST(getdate()  as DATE) -- assume mydate is a date
ORDER BY CAST(mydate as DATE) DESC;

This will get the most recent day's data in the table, for any day where there is data.

EDIT:

Apparently, mydate is stored in a varchar. This is a bad idea, but if you are going to do it, then format 121 is a very good choice. I would modify the above as:

SELECT TOP 1 WITH TIES t.*
FROM mytable t
WHERE mydate < CONVERT(VARCHAR(10), getdate(), 121)
ORDER BY LEFT(mydate, 10) DESC;

Note: this does the comparison of the dates as strings. But that is okay, because the dates are in the format YYYY-MM-DD, which is comparable. If performance is an issue, I would advise a computed column on mydate (to convert to a datetime or date) with an index on the column.

Upvotes: 3

Related Questions