Find last business day from table with date and day in SQL 2008 and 2012

Question

Post reply

Find last business day from table with date and day in SQL 2008 and 2012

prafullaahirrao

Old Hand

Points: 308
More actions
November 29, 2016 at 4:15 am

#401130

Hi All,
I need last business day from a table with ID,date and day column in it.
For e.g.
IDDate Day
11-Jan-2016 Friday
22-Jan-2016 Saturday
33-Jan-2016 Sunday
.
.
29 29-Jan-2016 Friday
30 30-Jan-2016 Saturday
31 31-Jan-2016 Sunday
32 1-Feb-2016 Monday
33 2-Feb-2016 Tuesday
.
.
60 29-Feb-2016 Monday
and so on....
so I need
29-Jan-2016 Friday(Excluding Saturday and Sunday)
29-Feb-2016 Monday
.
.
.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

A Calendar Table is really handy for this.

As an Inline calculation

the way i would tackle it is with DateMath, and a case statement that checks for Saturday or Sunday

here i created 64 months if dates, and used a calculation to confirm i was getting the last biz day of the month:

does this look right?

/*

TheDate LastDayOfMonth DatePartOfLastDay DateNameOfLastDay LastBizDay LastBizDayOfWeek

2016-01-15 00:00:00.000 2016-01-31 1 Sunday 2016-01-29 Friday

2016-02-15 00:00:00.000 2016-02-29 2 Monday 2016-02-29 Monday

2016-03-15 00:00:00.000 2016-03-31 5 Thursday 2016-03-31 Thursday

*/

WITH ExampleMonths

AS

(

SELECT DATEADD(mm,MiniTally.N,'2015-01-15') AS TheDate

FROM (select row_number() over (order by num) -1 as N

from (values (1)) t (num)

group by cube (num, num, num, num, num, num) --2^6 = 64 ,more num = another order power of 2)

) MiniTally

)

select TheDate,

DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)) AS ABitLessThanFirstOfMonth,

CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))) AS LastDayOfMonth,

DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) AS DatePartOfLastDay,

DATENAME(dw,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) AS DateNameOfLastDay,

CASE

WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN (7) --Saturday

THEN DATEADD(dd,-1,

CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

)

WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN(1) --Sunday

THEN DATEADD(dd,-2,

CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

)

ELSE CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

END AS LastBizDay,

DATENAME(dw,CASE

WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN (7) --Saturday

THEN DATEADD(dd,-1,

CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

)

WHEN DATEPART(weekday,(CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0))))) IN(1) --Sunday

THEN DATEADD(dd,-2,

CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

)

ELSE CONVERT(DATE,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,TheDate ) + 1, 0)))

END) AS LastBizDayOfWeek

FROM ExampleMonths

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Isn't it as simple as this?

SELECT DISTINCT

MAX(TheDate) OVER(PARTITION BY YEAR(MyDate), MONTH(MyDate)) AS LastBizofMonth

,MyDay

FROM MyTable

WHERE MyDay NOT IN ('Saturday', 'Sunday')

By the way, please don't double-post.

John

Thom A SSC Guru Points: 99130 More actions · Answer 3

John Mitchell-245523 (11/29/2016)
Isn't it as simple as this?
SELECT DISTINCT
MAX(TheDate) OVER(PARTITION BY YEAR(MyDate), MONTH(MyDate)) AS LastBizofMonth
,MyDay
FROM MyTable
WHERE MyDay NOT IN ('Saturday', 'Sunday')
By the way, please don't double-post.
John

Depends on the OP's definition of a Working Day. For example, I need to omit Public Holiday's as well as weekends. This also includes Observed holidays, such as Christmas Day this year, due to it falling on a Sunday (it is therefore observed on the Tuesday, as that is the next non public holiday weekday).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

prafullaahirrao Old Hand Points: 308 More actions · Answer 4

prafullaahirrao

Old Hand

Points: 308

November 29, 2016 at 8:20 am

#1914968

Thanks John

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Using this sample data:

CREATE TABLE #myTable (dt date UNIQUE NOT NULL);

WITH E AS (SELECT v FROM (VALUES (1),(1),(1),(1),(1),(1)) t(v))

INSERT #myTable

SELECT CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1, '20140101') AS date)

FROM E a, E b, E c, E d;

You could do this:

SELECT [date] = MAX(dt),

[day] = DATENAME(WEEKDAY,MAX(dt))

FROM #myTable

WHERE DATENAME(WEEKDAY,dt) NOT IN ('Saturday', 'Sunday')

GROUP BY DATEPART(YEAR,dt), DATEPART(MONTH,dt)

ORDER BY MAX(dt); -- not required, including for readability

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001