October 25, 2010 at 4:37 am
Hello,
I have a scenario where I want to add 4 working days to the start of a month.
Background:
My users currently have a transaction table where they filter on a YearMonth to view transactions for a Month for example '201001' will return all transactions for January 2010.
There is now a new requirement that transactions that fall in the first 4 working days of the Month should pertain to the previous month (Call it 'YearMonthMIS').
In the sql below I've described what I expect my result to look like however it only adds 4 days not work days.
*Note the second SELECT indicates the basis of my calcs.
--Temp table:
CREATE TABLE #tempTransactions
( ID int PRIMARY KEY
, TransactionDate DATETIME
, TransactionYearMonth int
)
INSERT INTO #tempTransactions
SELECT 1, '01/01/2010', 201001
UNION ALL
SELECT 2, '01/05/2010', 201001
UNION ALL
SELECT 3, '01/30/2010', 201001
UNION ALL
SELECT 4, '02/02/2010', 201002
UNION ALL
SELECT 5, '02/15/2010', 201002
--Retrieve results:
SELECT
ID
, TransactionDate
, TransactionYearMonth
-- IF Transaction date is Between the [FirstDayOfMonth] AND [FirstDayOfMonthMIS] THEN YearMonthMIS is = Previous YM
, CASE WHEN TransactionDate BETWEEN (DATEADD(month, DATEDIFF(month, 0, TransactionDate), 0)) AND (DATEADD(DAY, 4, (DATEADD(month, DATEDIFF(month, 0, TransactionDate), 0))))
THEN YEAR (DATEADD(MONTH, -1, TransactionDate)) *100 + MONTH(DATEADD(MONTH, -1, TransactionDate))
ELSE TransactionYearMonth
END AS [YearMonthMIS]
FROM
#tempTransactions
--Basis of Calculations:
SELECT
TransactionDate
, TransactionYearMonth
--(TransactionYearMonth -1):
, YEAR (DATEADD(MONTH, -1, TransactionDate)) *100 + MONTH(DATEADD(MONTH, -1, TransactionDate)) AS [PreviousTransactionYearMonth]
--FirstDay of Month:
, DATEADD(month, DATEDIFF(month, 0, TransactionDate), 0) AS [FirstDayOfMonth]
--FirstDay of Month +4 days):
, DATEADD(DAY, 4, (DATEADD(month, DATEDIFF(month, 0, TransactionDate), 0))) AS [FirstDayOfMonthMIS]
FROM #tempTransactions
DROP TABLE #tempTransactions
The crux is thus to replace the 4 below with 4 working days:unsure:.
DATEADD(DAY, 4, (DATEADD(month, DATEDIFF(month, 0, TransactionDate), 0))) AS [FirstDayOfMonthMIS]
If anyone has any suggestions or advice it would be greatly appreciated!
G
October 25, 2010 at 4:58 am
October 25, 2010 at 5:01 am
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.
October 25, 2010 at 5:04 am
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
October 25, 2010 at 5:08 am
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
October 25, 2010 at 5:38 am
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
October 25, 2010 at 5:42 am
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)
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
October 25, 2010 at 9:49 am
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
October 27, 2010 at 7:51 am
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, CASE WHEN DATENAME(weekday,@MyDate) = 'Sunday' THEN 4
WHEN DATENAME(weekday,@MyDate) IN ('Monday', 'Tuesday') THEN 3
ELSE 5
END,@MyDate )
Thanks again,
G
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply