Determine 4 working days from Start Of Month

Question

Post reply

Determine 4 working days from Start Of Month

Viewing 8 posts - 1 through 9 (of 9 total)

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 1

In order to have working days you will need to create a calendar like in this article

Fun with business days, calendar tables, and test-driven development - By Alex Kuznetsov, 2010/09/23 [/url]

Or are you considering working days: Monday to Friday?

Regards,

Iulian

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

What is your definition of a working day? Does this exclude public holidays as well as weekends?

If so, a date table will be the simplest solution, keyed on date, specifying the month that it relates to.

Grinja Hall of Fame Points: 3590 More actions · Answer 3

Hi,

To calrify Work Day is Monday to Friday. Holidays are not material for the purpose of this report.

I would like to avoid the use of calender table if at all possible but at the moment I can't see a solution.

Thanks for taking time to reply.

G

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 4

Here you will need to determine and add the numbers of weekend days.

and the code will be

4 + [weekend days in the first 4 days of the month]

Regards,

Iulian

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 5

The number of the weekend days in the first 4 days of the month can be determined using datepart function, something like this: CASE WHEN DATEPART( WEEKDAY, GETDATE()) BETWEEN 2 AND 4 THEN 0

ELSE 2

END

It depends when your week starts.

Regards,

Iulian

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

If the first day of the month is 'saturday', add 5 days. If it's a 'sunday', add 4 days. What if it's wednesday?

-- How might this work longhand?

DECLARE @MyDate DATETIME

SET @MyDate = GETDATE()

SELECT

[Weekday] = DATENAME(weekday,@MyDate+0),

[4 wd later] = DATENAME(weekday,DATEADD(dd,4,@MyDate+0)) UNION ALL

SELECT DATENAME(weekday,@MyDate+1), DATENAME(weekday,DATEADD(dd,6,@MyDate+1)) UNION ALL

SELECT DATENAME(weekday,@MyDate+2), DATENAME(weekday,DATEADD(dd,6,@MyDate+2)) UNION ALL

SELECT DATENAME(weekday,@MyDate+3), DATENAME(weekday,DATEADD(dd,6,@MyDate+3)) UNION ALL

SELECT DATENAME(weekday,@MyDate+4), DATENAME(weekday,DATEADD(dd,6,@MyDate+4)) UNION ALL

SELECT DATENAME(weekday,@MyDate+5), DATENAME(weekday,DATEADD(dd,5,@MyDate+5)) UNION ALL

SELECT DATENAME(weekday,@MyDate+6), DATENAME(weekday,DATEADD(dd,4,@MyDate+6))

-----------------------------------------------------------------------------

-- tidy it up a little

DECLARE @Increment TINYINT

SELECT @Increment = CASE

WHEN DATENAME(weekday,@MyDate) = 'Monday' THEN 4

WHEN DATENAME(weekday,@MyDate) = 'Tuesday' THEN 6

WHEN DATENAME(weekday,@MyDate) = 'Wednesday' THEN 6

WHEN DATENAME(weekday,@MyDate) = 'Thursday' THEN 6

WHEN DATENAME(weekday,@MyDate) = 'Friday' THEN 6

WHEN DATENAME(weekday,@MyDate) = 'Saturday' THEN 5

WHEN DATENAME(weekday,@MyDate) = 'Sunday' THEN 4

END

SELECT DATEADD(dd, @Increment, @MyDate)

-----------------------------------------------------------------------------

-- final version

SET @MyDate = GETDATE()

SELECT Today = @MyDate, ReportStartDate = DATEADD(dd, CASE

WHEN DATENAME(weekday,@MyDate) = 'Saturday' THEN 5

WHEN DATENAME(weekday,@MyDate) IN ('Monday','Sunday') THEN 4

ELSE 6 END, @MyDate)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 7

Replace "4" with:

4 + SUBSTRING('0012221', DATEDIFF(DAY, '19000101',

DATEADD(month, DATEDIFF(month, 0, '20100301'), 0)) % 7 + 1, 1)

SQL's base date, 19000101, is a Monday. So the string is the # of days to add for MonTueWedThu.... Then a quick computation determines the number of days to add.

Scott Pletcher, SQL Server MVP 2008-2010

Grinja Hall of Fame Points: 3590 More actions · Answer 8

Thank you everyone for taking time to post, you have been most helpful!

I went with Chris's solution as I found it easier to read (at least for me anyway :P)

I made some adjustments to the actual days; I realised that I should only add 3 working days as the current transaction day is included.

So it came down to: Sunday3+1; Mon and Tue 3+0; Everything else 3+2

DECLARE @MyDate DateTime

SET @MyDate = GetDate()

SELECT DATEADD(dd, CASEWHEN DATENAME(weekday,@MyDate) = 'Sunday' THEN 4

WHEN DATENAME(weekday,@MyDate) IN ('Monday', 'Tuesday') THEN 3

ELSE 5

END,@MyDate )

Thanks again,

G