May 12, 2015 at 6:29 am
Hi,
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
Any help is appreciated!
May 12, 2015 at 7:13 am
Try PARTITION
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (PARTITION BY IsBusinessDay ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
May 12, 2015 at 7:29 am
SELECT A. Date, MIN(B.DatePlus3BusinessDays) DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (PARTITION BY IsBusinessDay ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey <= B.DateKey
GROUP BY a.Date
May 12, 2015 at 8:25 am
That didn't work I'm afraid. The data is still being restricted by IsBusinessDay, hence removing the non business days.
I've taken that where clause out and it still doesn't work 🙁
May 12, 2015 at 10:19 am
You can try something like this... (Note... I built this using our Calendar table which uses isWeekdat & isHoliday to determine working days).
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
TestDate DATE
)
INSERT #temp (TestDate) VALUES
('2015-05-01'),('2015-05-02'),('2015-05-03'),('2015-05-04'),
('2015-05-11'),('2015-05-12'),('2015-05-13'),('2015-05-14'),
('2015-05-21'),('2015-05-22'),('2015-05-23'),('2015-05-24')
SELECT
t.TestDate,
c2.dt AS EndDate
FROM
#temp t
CROSS APPLY (
SELECT TOP 3
c.dt,
ROW_NUMBER() OVER (ORDER BY c.dt) AS rn
FROM
dbo.Calendar c
WHERE 1 = 1
AND t.TestDate < c.dt
AND c.isWeekday = 1
AND c.isHoliday = 0
ORDER BY
c.dt
) c2
WHERE 1 = 1
AND c2.rn = 3
I don't love the use of the triangular join but a cleaner solution isn't popping into my head at the moment.
Hopefully someone will post something a little cleaner. If not, this should at least get the desired results.
May 12, 2015 at 11:05 am
SELECT A.Date,
( --get the last of the business dates from below
SELECT TOP (1) DateKey
FROM (
--get next three business dates past outer query's date
SELECT TOP (3) c.DateKey
FROM Calendar c
WHERE c.DateKey > A.DateKey
AND c.IsBusinessDay = 1
ORDER BY c.DateKey
) AS derived
ORDER BY DateKey DESC
) AS DatePlus3BusinessDays
FROM TableA A
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2015 at 2:58 am
Thanks for the help guys. Scott, your suggestion seems to work! Thanks 🙂
May 13, 2015 at 4:06 am
Had a slightly different requirement come in!
Need to get the previous business day of a date if that date is not a business day... again I would like to use the Calendar table..
May 13, 2015 at 7:41 am
mm7861 (5/13/2015)
Had a slightly different requirement come in!Need to get the previous business day of a date if that date is not a business day... again I would like to use the Calendar table..
SELECT A.Date,
( --get the previous business date before the outer query's date
SELECT TOP (1) c.DateKey
FROM Calendar c
WHERE c.DateKey < A.DateKey
AND c.IsBusinessDay = 1
ORDER BY c.DateKey DESC
) AS DateMinus1BusinessDay
FROM TableA A
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2015 at 7:46 am
Wow, so simple! Thanks 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply