SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Second Last work day of month


Second Last work day of month

Author
Message
praveen.arokiam
praveen.arokiam
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 13
Hi,
I would like to get the second last work day of the month. Can anyone help me with that.
Bill Talada
Bill Talada
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2884 Visits: 2125
Is a workday Monday thru Friday? Do you need to be concerned with holidays?
Michael L John
Michael L John
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5905 Visits: 8227
See this link.
http://www.sqlservercentral.com/scripts/Date/68389/

A calendar table will probably work well for this use.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61717 Visits: 17954
Or if you don't need the flexibility of a calendar table (which is probably what I would use) and you don't care about holidays you could do some simple date math. This will return the last 7 days of any given month.


declare @ThisDate datetime = getdate()

select DATEADD(day, -DayNum, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) as MyDay
from
( Values(1),(2),(3),(4),(5),(6),(7)) d(DayNum)
order by DayNum desc



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
praveen.arokiam
praveen.arokiam
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 13
Hi,
Thanks for the reply. Yes the workday runs through Monday to Friday
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61717 Visits: 17954
praveen.arokiam (5/2/2014)
Hi,
Thanks for the reply. Yes the workday runs through Monday to Friday


So a minor tweak to what I posted will get you the last Friday of any given month.


declare @ThisDate datetime = getdate();

with LastSeven as
(
select DATEADD(day, -DayNum, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) as MyDay
from
( Values(1),(2),(3),(4),(5),(6),(7)) d(DayNum)
)
select *
from LastSeven
where DATENAME(weekday, MyDay) = 'Friday'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19365 Visits: 7410


DECLARE @date_with_month datetime
SET @date_with_month = GETDATE()

;WITH
cteDays AS (
SELECT 1 AS day# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 7 UNION ALL
SELECT 7
)
SELECT TOP (1) day_of_month
FROM (
SELECT TOP (2) day_of_month
FROM cteDays
CROSS APPLY (
SELECT DATEADD(DAY, -day#, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date_with_month) + 1, 0)) AS day_of_month
) AS assign_column_name_to_day_of_month_calc
WHERE
DATEDIFF(DAY, 0, day_of_month) % 7 <= 4
ORDER BY
day_of_month DESC
) AS derived
ORDER BY day_of_month ASC




SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7125 Visits: 2033
For the current month ...


WITH MYCTE AS (
SELECT row_number() over (order by [dates] DESC ) as dayNum, [dates], datename(weekday, dates) as [Day Name]
FROM
( /* the last seven days in the month*/
SELECT dateadd(dd, ROW_NUMBER() OVER( ORDER BY (SELECT 'a')) -1, dateadd(m,1,dateadd(d,-day(getdate())+1, dateadd(d, 0, datediff(d, 0 , getdate()))))-7) as [Dates]
from (VALUES(1),(2),(3),(3),(4),(5),(6))L(s)
) as v
WHERE datename(weekday, dates) not in ('saturday','sunday')
)
SELECT * from MYCTE where dayNum = 2




You can substitute getdate() with a datetime or date variable and make it any day in the month you wish to analyse.

----------------------------------------------------
How to post forum questions to get the best help
Sachin Nandanwar
Sachin Nandanwar
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 2633
Since you have posted the question in SQL Server 2012 TSQL category I assume you are using SQL Server 2012.If that's the case then you can simply use the EOMONTH function

select  case datename(WEEKDAY,EOMONTH(getdate())) 
when 'Saturday' then convert(datetime,(EOMONTH(getdate())))-2
when 'Sunday' then convert(datetime, (EOMONTH(getdate())))-3
else convert(datetime,EOMONTH(getdate()))-1 end



--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search