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

• 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

• 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!

• 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

• 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

• Thanks John

• 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

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