April 7, 2016 at 11:36 am
Example
IF CharField = 'A' add 14 working days to DateField ELSE
IF CharField = 'B' add 15 working days to DateField ELSE
IF CharField = 'C' add 19 working days to DateField ELSE
IF CharField = 'D' add 17 working days to DateField ELSE
END AS 'NewDateField
I have done this in the past but can't seem to get SQL to like me today, GRRRR.
Help Please
April 7, 2016 at 12:33 pm
maybe....
SELECT CharField,
DateField,
CASE
WHEN CharField = 'A' then dateadd(day,14, datefield)
WHEN CharField = 'B' ......... else NULL end as yournewdate
FROM mytable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 7, 2016 at 1:07 pm
Thanks for the reply, I need it to add working days, skipping weekends
April 7, 2016 at 1:36 pm
K430 (4/7/2016)
Thanks for the reply, I need it to add working days, skipping weekends
so...other than weekends, what other days do you wish to skip...for example public holidays?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 7, 2016 at 1:39 pm
try a search on this site ....plenty of q/a's
GOOGLE> http://www.sqlservercentral.com add working days">site:http://www.sqlservercentral.com add working days
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 7, 2016 at 2:00 pm
Already Googled
June 10, 2016 at 9:08 am
I've created a table which lists dates whether they are a week day or bank holiday. I then create my own version of juilan dates which skips weekends and bankholidays so given a date I can refer to the working days add number of days and determine new date.
June 10, 2016 at 9:32 am
-- example code for calculating working days
declare @StartDate date = '2000-01-01';
set DATEFIRST 1;
WITH
bh as
(
SELECT '2016-01-01' BankHoliday UNION ALL
SELECT '25-03-2016' BankHoliday UNION ALL
SELECT '28-03-2016' BankHoliday UNION ALL
SELECT '02-05-2016' BankHoliday UNION ALL
SELECT '30-05-2016' BankHoliday UNION ALL
SELECT '29-05-2016' BankHoliday UNION ALL
SELECT '28-08-2016' BankHoliday UNION ALL
SELECT '25-12-2016' BankHoliday UNION ALL
SELECT '26-12-2016' BankHoliday UNION ALL
SELECT '02-01-2017' BankHoliday UNION ALL
SELECT '14-04-2017' BankHoliday UNION ALL
SELECT '17-04-2017' BankHoliday UNION ALL
SELECT '01-05-2017' BankHoliday UNION ALL
SELECT '29-05-2017' BankHoliday UNION ALL
SELECT '28-08-2017' BankHoliday UNION ALL
SELECT '25-12-2017' BankHoliday UNION ALL
SELECT '26-12-2017' BankHoliday
),
Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10000
),
DateRange as
(
SELECT
DATEADD(dd,n,@StartDate) ActualDate,
datepart(dw,DATEADD(d,n,@StartDate)) dow,
bh.BankHoliday
FROM Numbers
left join bh
on bh.BankHoliday =DATEADD(dd,n,@StartDate)
),
WorkingDays as
(
SELECT
ActualDate,
row_number() over(order by ActualDate) WorkingDays
from DateRange
where dow not in (6,7) and BankHoliday is null
)
select
a.ActualDate,
a.dow,
a.BankHoliday,
wd.WorkingDays
from DateRange a
left join WorkingDays wd
on a.ActualDate=wd.ActualDate
OPTION (MAXRECURSION 10000)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply