[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

sql server - How do I select the date of the most recent weekend?

Hi Experts,

How do I select the date of the most recent weekend from a table, given the column name "valueDate"?
0
feesu
Asked:
feesu
  • 17
  • 16
  • 3
  • +3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Can you kindly post some sample records and the desired result sets to make it more clear..
0
 
LowfatspreadCommented:
select Valuedate,Datename(dw,Valuedate) from (
Select  
Max(case When datename(dw,ValueDate) in ('Saturday','Sunday')
         then valuedate else null end) as Valuedate
from yourtable
) as X

0
 
Rajkumar GsSoftware EngineerCommented:
-- QUERY TO SELECT RECENT WEEKDAY BY PASSING A DATE AS PARAMETER
-- SET YOUR INPUT DATE FIRST

-- SELECT FROM HERE TO EXECUTE
DECLARE @DATE      DATETIME
SET @DATE = '03/25/2010'  -- Saturday

SELECT      CONVERT(VARCHAR(11), Valuedate, 106) AS RecentWeekDay,
            Datename(dw,Valuedate) AS RecentWeekDayName,
            CONVERT(VARCHAR(11), @DATE, 106) AS ParameterDate,
            Datename(dw,@DATE) AS ParameterDayName
FROM #tblDays
WHERE valueDate =
            (SELECT MAX(valueDate) FROM #tblDays
                  WHERE      DATEPART (dw, Valuedate) IN (1, 7) AND
                              Valuedate < @DATE )

Change < symbol to > in the query, if you want next nearest weekday.

Raj
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rajkumar GsSoftware EngineerCommented:
To test, use this script




CREATE TABLE #tblDays
(
      valueDate      DATETIME
)


-- DELETE #tblDays
INSERT INTO #tblDays
SELECT '04/03/2010' UNION -- Saturday
SELECT '04/01/2010' UNION -- Thursday
SELECT '03/29/2010' UNION -- Monday
SELECT '03/27/2010' UNION -- Saturday
SELECT '03/25/2010' UNION -- Thursday
SELECT '03/22/2010' UNION -- Monday
SELECT '03/03/2010' UNION -- Wednesday
SELECT '02/28/2010'

-- PREVIEW DATES
SELECT CONVERT(VARCHAR(10), Valuedate, 102) AS Valuedate,
            Datename(dw,Valuedate) AS DayName
FROM #tblDays
ORDER BY Valuedate desc



-- QUERY TO SELECT RECENT WEEKDAY BY PASSING A DATE AS PARAMETER
-- SET YOUR INPUT DATE FIRST

-- SELECT FROM HERE TO EXECUTE
DECLARE @DATE      DATETIME
--SET @DATE = '04/04/2010' -- Sunday
--SET @DATE = '04/03/2010' -- Saturday
--SET @DATE = '03/27/2010'  -- Saturday
SET @DATE = '03/25/2010'  -- Saturday

SELECT      CONVERT(VARCHAR(11), Valuedate, 106) AS RecentWeekDay,
            Datename(dw,Valuedate) AS RecentWeekDayName,
            CONVERT(VARCHAR(11), @DATE, 106) AS ParameterDate,
            Datename(dw,@DATE) AS ParameterDayName
FROM #tblDays
WHERE valueDate =
            (SELECT MAX(valueDate) FROM #tblDays
                  WHERE      DATEPART (dw, Valuedate) IN (1, 7) AND
                              Valuedate < @DATE )
-- SELECT TILL HERE TO EXECUTE

DROP TABLE #tblDays
      

Raj
0
 
feesuAuthor Commented:
Thank you all for your replies.
My scenario is a table of stock prices, so some dates may not be available, some times last trading day is not a weekend, might be a public holiday where the last trading day is a day or two before the weekend. What I need to lookup is the last trading day before a week.
0
 
SharathData EngineerCommented:
Can you provide some sample set and expected result?
0
 
feesuAuthor Commented:
Below is a sample for the Sector "1". You will notice that Jan 8/9 are weekends, and therefore, considering the last day of trading in the previous week from lets say 11/01/2010 is going to be Jan 7, but if there was a public holiday or for whatever reason, there was no trading on Jan 7, then the query should return Jan 6, and if no trading on Jan 6 then the day before, and so on.
------------------------------------
valueDate      Sector      Amount
------------------------------------
03/01/2010      1      7005
04/01/2010      1      6891
05/01/2010      1      6970
06/01/2010      1      6969
07/01/2010      1      7012
10/01/2010      1      7059
11/01/2010      1      7048
12/01/2010      1      7025
13/01/2010      1      6983
14/01/2010      1      6986
17/01/2010      1      7014
18/01/2010      1      6997
19/01/2010      1      7012
20/01/2010      1      7062
21/01/2010      1      7062
24/01/2010      1      7025
25/01/2010      1      7084
26/01/2010      1      7069
27/01/2010      1      6975
28/01/2010      1      7035
31/01/2010      1      7025
01/02/2010      1      7015
02/02/2010      1      7016
0
 
Rajkumar GsSoftware EngineerCommented:
You need to keep a table containing the list of Holidays'

Check these scripts - which is commented whereever necessary.

Hope this helps

Raj
-- MAIN TABLE
CREATE TABLE #table
(
	valueDate	datetime,
	Sector		int,
	Amount		decimal(18,2)
)

-- HOLIDAY TABLE - Which contains the list of holidays
CREATE TABLE #Holidays
(
	HolidayDate	datetime,
	HolidayName	VARCHAR(50)
)

-- INSERT DATA INTO HOLIDAY TABLE
SET DATEFORMAT dmy  
INSERT INTO #Holidays
	SELECT '01/07/2010', 'Trading Holiday'

-- INSERT DATA INTO MAIN TABLE 
-- SELECT FROM HERE 
SET DATEFORMAT mdy      
INSERT INTO #table
SELECT '01/03/2010',      1,      7005
UNION ALL
SELECT '01/04/2010',      1,      6891
UNION ALL
SELECT '01/05/2010',      1,      6970
UNION ALL
SELECT '01/06/2010',      1,      6969
UNION ALL
SELECT '01/07/2010',      1,      7012
UNION ALL
SELECT '01/10/2010',      1,      7059
UNION ALL
SELECT '01/11/2010',      1,      7048
UNION ALL
SELECT '01/12/2010',      1,      7025
UNION ALL
SELECT '01/13/2010',      1,      6983
UNION ALL
SELECT '01/14/2010',      1,      6986
UNION ALL
SELECT '01/17/2010',      1,      7014
UNION ALL
SELECT '01/18/2010',      1,      6997
UNION ALL
SELECT '01/19/2010',      1,      7012
UNION ALL
SELECT '01/20/2010',      1,      7062
UNION ALL
SELECT '01/21/2010',      1,      7062
UNION ALL
SELECT '01/24/2010',      1,      7025
UNION ALL
SELECT '01/25/2010',      1,      7084
UNION ALL
SELECT '01/26/2010',      1,      7069
UNION ALL
SELECT '01/27/2010',      1,      6975
UNION ALL
SELECT '01/28/2010',      1,      7035
UNION ALL
SELECT '01/31/2010',      1,      7025
UNION ALL
SELECT '02/01/2010',      1,      7015
UNION ALL
SELECT '02/02/2010',      1,      7016
-- SELECT TILL HERE 

--------------------------------------------
--- TABLES AND DATA ARE READY NOW

SELECT * FROM #Holidays

SELECT * FROM #table


-- THIS IS THE FINAL QUERY
-- SELECT FROM HERE 
SET DATEFORMAT mdy
DECLARE @BASEDATE DATETIME
SET @BASEDATE = '01/10/2010' -- DATE BASED ON WHICH WE ARE FINDING LAST WEEKDAY

SELECT      CONVERT(VARCHAR(11), Valuedate, 106) AS RecentWeekDay, 
            Datename(dw,Valuedate) AS RecentWeekDayName,
            CONVERT(VARCHAR(11), @BASEDATE, 106) AS ParameterDate, 
            Datename(dw,@BASEDATE) AS ParameterDayName
FROM #table
WHERE valueDate =
            (SELECT MAX(valueDate) FROM #table 
                  WHERE      DATEPART (dw, Valuedate) NOT IN (1, 7) AND 
                              Valuedate < @BASEDATE AND valueDate NOT IN
							(SELECT  HolidayDate FROM #Holidays))
-- SELECT TILL HERE 


DROP TABLE #Holidays
DROP TABLE #table

Open in new window

0
 
feesuAuthor Commented:
Hi Raj,

I cannot have a table with list of holidays, I should look for an available date, if I don't find it, this means there was no trading on that date. Sometimes there are other circumstances where no trade happens, other than holidays, so I take the most recent date before that.
0
 
Rajkumar GsSoftware EngineerCommented:
We need to exclude from select query those days - holidays & non-trade days, right?

Then how do you tell the SQL Query to exclude those days that don't have trade ?

Raj
0
 
feesuAuthor Commented:
In general, I look for the MAX date available.

If I'm looking for the previous month closing, and my current date is a parameter @Date, then I do the following:
select max(valueDate) from table1 where valueDate  < year(@date)+month(@date)+'01'
So that I get the most recent trading before the beginning of the month, but it seems that "+" sign is summing up the numbers.

If it's the year closing, it's the same idea:
select max(valueDate) from table1 where valueDate  < year(@date)+'0101'

But I guess I need to replace the "+" with something.

In the case of my question, and because it's a weekend, I am not sure how to do it, but I can hard code the weekend days, in my case it's Friday/Saturday but I definitely need to choose the most recent trading before the most recent Saturday.

Hope it's clear,
0
 
Rajkumar GsSoftware EngineerCommented:
To identify a date is whether Friday or Saturday, you can add one more condition to your query itself.

1 represents sunday, provided it is the first day of the week.
similary 7 represents saturday

Similarly include in 'IN' case of the query, those values of days you want to exclude

eg:-
select max(valueDate) from table1 where valueDate  < year(@date)+month(@date)+'01'

Raj
0
 
gvkishorekumarCommented:
Hi Feesu,
Inorder to differenciate the workingday with zero trading Vs holiday, u should maintain the HOLIDAY master.
0
 
feesuAuthor Commented:
But there is a bug in your query Raj, it may select the week days before the basedate weekend.
0
 
Rajkumar GsSoftware EngineerCommented:
>> But there is a bug in your query Raj, it may select the week days before the basedate weekend

Which query ?

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
There are something confuse me, that if clarified clearly, you will get your solution.

I understood like this - correct me if wrong...

You need to find the previous day having trading based on a date we passed to the query. Need to exclude weekend dates. If the day before the date we passed is a holiday or non-trading day, we need to search again back .

So my question - We need to check that the date is holiday or non-trading day, right ? To check that we need a table like 'Holiday' table in which the holiday dates and non-trading dates should be entered. We need reference of this table to skip out those dates.

>> Sometimes there are other circumstances where no trade happens, other than holidays, so I take the most recent date before that.

How can we identity this situation ? So need to have a table for holidays & non-trading days.

If you still don't want to use a table, you need to specify those dates in query as ... NOT IN (date1, date2,...)

I think we are close to the solution, but we need to make clear of these...

Raj
0
 
feesuAuthor Commented:
1- I said I wanted to the most recent trading day in the previous week. I.e if today is Thursday, and I pass today's date to my query, I get last Thursday's trading, but if last Thursday was a holiday or whatever, then I get the day before "Wednesday", and so on, if there were no trades on Wednesday then the day before.

2- We may know the holidays and days off but looking up the max date, because the database I have as shown above doesn't include those days with no trade. So searching for the max date should do.
0
 
gvkishorekumarCommented:
I hope the below query may solve ur problem,

SELECT TOP 1 CONVERT(VARCHAR(10),yourdatefield,103)
FROM yourtablename
WHERE  CONVERT(VARCHAR(10),yourdatefield,103)  < yourdateselect
ORDER BY yourdatefield desc
0
 
LowfatspreadCommented:
like this

select * from yourtable as a
where valuedate = (select max(valuedate) from yourtable
 where valuedate <= '20100327')


if you want the date closest to the input date with data...
(and the weekend part of your question is a red herring... since you don't have
info for non working dates)
0
 
SharathData EngineerCommented:
Weekeds can be handled but not holidays. You need to have a table with a list of holidays as Raj mentioned. Otherwise how the system know if a particular day is holiday or not?
0
 
feesuAuthor Commented:
Sharath_123,

As explained above, the days that are holidays or weekends do not have records in the table. Therefore, if the specific date does not exist it means I take the previous one. Easy!
0
 
Rajkumar GsSoftware EngineerCommented:
I have removed Holiday table and modified the select query without it.

Run this script and check whether you get what you expect.

Remember onething. I have excluded now Saturday & Sunday from there search.
... DATEPART (dw, Valuedate) NOT IN (1, 7) ...

If you need another days to exclude modify the query's condition part.

1 - Sunday
2 - Monday
3 - Tuesday
4 - Wednesday
5 - Thursday
6 - Friday
7 - Saturday

eg:- If you want to exclude only Sunday, it will be
... DATEPART (dw, Valuedate) NOT IN (1) ...

This query will look backwards by excluding those days, based on the date you passed.

Let us know
Raj
-- MAIN TABLE 
CREATE TABLE #table 
( 
        valueDate       datetime, 
        Sector          int, 
        Amount          decimal(18,2) 
) 

-- INSERT DATA INTO MAIN TABLE  
-- SELECT FROM HERE  
SET DATEFORMAT mdy       
INSERT INTO #table 
SELECT '01/03/2010',      1,      7005 
UNION ALL 
SELECT '01/04/2010',      1,      6891 
UNION ALL 
SELECT '01/05/2010',      1,      6970 
UNION ALL 
SELECT '01/06/2010',      1,      6969 
UNION ALL 
SELECT '01/07/2010',      1,      7012 
UNION ALL 
SELECT '01/10/2010',      1,      7059 
UNION ALL 
SELECT '01/11/2010',      1,      7048 
UNION ALL 
SELECT '01/12/2010',      1,      7025 
UNION ALL 
SELECT '01/13/2010',      1,      6983 
UNION ALL 
SELECT '01/14/2010',      1,      6986 
UNION ALL 
SELECT '01/17/2010',      1,      7014 
UNION ALL 
SELECT '01/18/2010',      1,      6997 
UNION ALL 
SELECT '01/19/2010',      1,      7012 
UNION ALL 
SELECT '01/20/2010',      1,      7062 
UNION ALL 
SELECT '01/21/2010',      1,      7062 
UNION ALL 
SELECT '01/24/2010',      1,      7025 
UNION ALL 
SELECT '01/25/2010',      1,      7084 
UNION ALL 
SELECT '01/26/2010',      1,      7069 
UNION ALL 
SELECT '01/27/2010',      1,      6975 
UNION ALL 
SELECT '01/28/2010',      1,      7035 
UNION ALL 
SELECT '01/31/2010',      1,      7025 
UNION ALL 
SELECT '02/01/2010',      1,      7015 
UNION ALL 
SELECT '02/02/2010',      1,      7016 
-- SELECT TILL HERE  


SELECT * FROM #table 


-- THIS IS THE FINAL QUERY 
-- SELECT FROM HERE  
SET DATEFORMAT mdy 
DECLARE @BASEDATE DATETIME 
SET @BASEDATE = '01/10/2010' -- DATE BASED ON WHICH WE ARE FINDING LAST WEEKDAY 
 
SELECT      CONVERT(VARCHAR(11), Valuedate, 106) AS RecentWeekDay,  
            Datename(dw,Valuedate) AS RecentWeekDayName, 
            CONVERT(VARCHAR(11), @BASEDATE, 106) AS ParameterDate,  
            Datename(dw,@BASEDATE) AS ParameterDayName 
FROM #table 
WHERE valueDate = 
            (SELECT MAX(valueDate) FROM #table  
                  WHERE      DATEPART (dw, Valuedate) NOT IN (1, 7) AND  
                              Valuedate < @BASEDATE) 
-- SELECT TILL HERE  
 
 

DROP TABLE #table

Open in new window

0
 
feesuAuthor Commented:
gvkishorekumar/Lowfatspread,

Your query does not return desired results.


0
 
feesuAuthor Commented:
Raj,

On your recent query, I get this error:

Msg 242, Level 16, State 3, Line 68
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
 
Rajkumar GsSoftware EngineerCommented:
That's because of date format difference. You have  dd/MM/yyyy and mine have  MM/dd/yyyy

To overcome this error, try select the script
SET DATEFORMAT mdy
also

I have put it whereever necessary.

Raj
0
 
LowfatspreadCommented:
ok what did it return for March 27th?

select * from yourtable as a
where valuedate = (select max(valuedate) from yourtable
 where valuedate <= '20100327')


0
 
Rajkumar GsSoftware EngineerCommented:
Ok. SET DATEFORMAT is only needed where we insert data into temporary table. Delete SET DATEFORMAT query, just before the final query and try.

Raj
0
 
feesuAuthor Commented:
Raj,

I ran your code along with the table creation (full) after remarking 2 set date formats you had, and got the below output:


(23 row(s) affected)

(23 row(s) affected)
Msg 242, Level 16, State 3, Line 68
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(0 row(s) affected)
0
 
feesuAuthor Commented:
Lowfatspread,

Your query returned yesterday's date. April 5th!
0
 
gvkishorekumarCommented:
What is the result of my query?
0
 
Rajkumar GsSoftware EngineerCommented:
1. In this query, replace 'YourTable' with your actual table name.

2. Set the date based on which you want to search in
         SET @BASEDATE =

Let me know
Raj
DECLARE @BASEDATE DATETIME  
SET @BASEDATE = <<< Give your date here >>>
  
SELECT      Valuedate AS RecentWeekDay,   
            Datename(dw,Valuedate) AS RecentWeekDayName,  
            @BASEDATE AS ParameterDate,   
            Datename(dw,@BASEDATE) AS ParameterDayName  
FROM YourTable  
WHERE valueDate =  
            (SELECT MAX(valueDate) FROM YourTable   
                  WHERE      DATEPART (dw, Valuedate) NOT IN (1, 7) AND   
                              Valuedate < @BASEDATE)

Open in new window

0
 
feesuAuthor Commented:
Raj,

2010-03-17 00:00:00.000      Wednesday      2010-03-18 00:00:00.000      Thursday
0
 
Rajkumar GsSoftware EngineerCommented:
You passed 18th March, 2010 as parameter and it returned nearest previous non-saturday/sunday day, according to the query. It is 17th.

If needed, modify the query according to my instructions above so that it will suite your requirement.

Raj
0
 
feesuAuthor Commented:
Dear Raj,

You make me think that I've never been clear in all my explanations :D

If 18/03 is a Thursday, then the query should return the Thursday before, which is 11/03, but if on that particular day there was no records available, then it will take the nearest trading date (max), so it will be 10/03, and again if on 10/03 there was no records (means no trading, be it a holiday or whatever) then the day before.

Your first condition will consider getting the most nearest weekend, and the max function should take care of the nearest trading day if the recent weekend has no records.

Thanks for your time Raj,
0
 
Rajkumar GsSoftware EngineerCommented:
I believe now I understood you...

Modified the query... Used '#table' as tablename which you need to modify to yours.

Check the attached code-snipplet.


>> If 18/03 is a Thursday, then the query should return the Thursday before, which is 11/03
 ... Valuedate <=  dateadd(dd, -7, @BASEDATE)... - This will do this part


>> on that particular day there was no records available, then it will take the nearest trading date (max)
... (SELECT MAX(valueDate) FROM #table ... - This will do this part


  Note that I have excluded Saturday & Sunday
... WHERE      DATEPART (dw, Valuedate) NOT IN (1, 7) AND   ...


Hope this helps

Let me know
Raj
-- SELECT FROM HERE   
SET DATEFORMAT mdy  
DECLARE @BASEDATE DATETIME  
SET @BASEDATE = <<< Give your date here >>> 

  
SELECT      CONVERT(VARCHAR(11), Valuedate, 106) AS RecentWeekDay,   
            Datename(dw,Valuedate) AS RecentWeekDayName,  
            CONVERT(VARCHAR(11), @BASEDATE, 106) AS ParameterDate,   
            Datename(dw,@BASEDATE) AS ParameterDayName  
FROM #table  
WHERE valueDate =  
            (SELECT MAX(valueDate) FROM #table   
                  WHERE      DATEPART (dw, Valuedate) NOT IN (1, 7) AND   
                              Valuedate <=  dateadd(dd, -7, @BASEDATE) ) 
-- SELECT TILL HERE

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
If any CRASH, exclude this line
SET DATEFORMAT mdy
and execute

Raj
0
 
feesuAuthor Commented:
Raj,

I think it works fine. Give me some time to test it out.

Thanks,
0
 
feesuAuthor Commented:
Hi Raj,

There is a slight bug in that.

Since you have fixed the 7 days difference, what if the user calls this report before the weekend, i.e. if the user instead of calling the report on 18th March, he called in 14th March, it also should get him the most recent Thursday which is 11th March, but in your case, it jumps to 4th March which is the previous Thursday. That's due to the 7 days deduction.

Thanks,
0
 
Rajkumar GsSoftware EngineerCommented:
That I fixed for 7 days backwards because you said ... ( may be I misunderstood you )
>> If 18/03 is a Thursday, then the query should return the Thursday before, which is 11/03, but if on that particular day there was no records available

>> ...and because it's a weekend, I am not sure how to do it, but I can hard code the weekend days, in my case it's Friday/Saturday but I definitely need to choose the most recent trading before the most recent Saturday.

So I will update the query to return the most recent trading before the most recent Saturday.

I have updated this part of the query...
... Valuedate <=  dateadd(dd, -7, @BASEDATE) )  ...
to
... Valuedate <=  dateadd(dd, 0 - DATEPART (dw, GETDATE()), GETDATE())) ..

Also removed the restriction of Saturday & Sunday from query (... DATEPART (dw, Valuedate) NOT IN (1, 7) ...) Since if trading happen for a day, it will be in database you want that date to be returned.

This will return the trading day before the previous saturday based on any date we passed.

See the attached code.
Hope this is what you are looking for...

Raj







DECLARE @BASEDATE DATETIME  
SET @BASEDATE = '01/10/2010' -- DATE BASED ON WHICH WE ARE FINDING LAST WEEKDAY  
  
SELECT      CONVERT(VARCHAR(11), Valuedate, 106) AS RecentWeekDay,    
            Datename(dw,Valuedate) AS RecentWeekDayName,   
            CONVERT(VARCHAR(11), @BASEDATE, 106) AS ParameterDate,    
            Datename(dw,@BASEDATE) AS ParameterDayName   
FROM #table   
WHERE valueDate =   
            (SELECT MAX(valueDate) FROM #table    
                  WHERE   Valuedate <=  dateadd(dd, 0 - DATEPART (dw, @BASEDATE), @BASEDATE))

Open in new window

0
 
feesuAuthor Commented:
That was perfect, thank you very much for your time Raj!
0
 
Rajkumar GsSoftware EngineerCommented:
Very happy to hear that you got it :)

Raj
0
 
feesuAuthor Commented:
Raj,

I have an additional question, it is - I guess - a bit more complicated than this. I have opened it, and I hope you help me on it:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25745471.html?fromWizard=true
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 17
  • 16
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now