Dear Friends,
I need your support to do a T-SQL...
For each row date field in a FactTable, I need to subtract 3 days or in some cases 2... the problem is not subtracting 3 or 2 days, but I need to verify in other table Holiday to check if some of the 3 previous dates are holiday or not...
In case of one of the 3 previous dates is holiday, I need to subtract 4 days to the main date in spite of 3
In case of two of the 3 previous dates are holidays, I need to subtract 5 days to the main date in spite of 3.
In case of all of the 3 previous dates are holidays, I need to subtract 6 days to the main date in spite of 3.
And I must garantee that I subtract 3 valid dates (not holidays) to the main date... for example, if one of the 3 previous is a holiday, i need to subtract 4 if the 4 day is not holiday... If it's a holiday I need to find the previous valid date...
How can do it? Could you help me?
here you go...
Code Snippet
/*
Create table Holidays
(
HolidayDate datetime
)
Insert Into Holidays values('12/28/2006')
Insert Into Holidays values('12/29/2006')
Insert Into Holidays values('12/30/2006')
Insert Into Holidays values('12/31/2006')
Insert Into Holidays values('01/01/2007')
Insert Into Holidays values('01/02/2007')
Insert Into Holidays values('01/10/2007')
Insert Into Holidays values('01/11/2007')
Insert Into Holidays values('01/20/2007')
Insert Into Holidays values('01/21/2007')
Insert Into Holidays values('01/22/2007')
*/
Create Function dbo.WorkingDateSubtract(@.NumberOfDays int, @.CurrentDate Datetime)
Returns DateTime
as
Begin
If Exists(Select 1 From Holidays Where HolidayDate = Dateadd(DD,-1,@.CurrentDate))
return dbo.WorkingDateSubtract(@.NumberOfDays, DateAdd(DD,-1,@.CurrentDate))
Else
If @.NumberOfDays = 0
return @.CurrentDate
Else
return dbo.WorkingDateSubtract(@.NumberOfDays-1, DateAdd(DD,-1,@.CurrentDate))
return null
End
Go
Select dbo.WorkingDateSubtract(3, '01/03/2007')
|||
Hi PedroCGD,
What about using the date dimension?. You can have a column in the dimension to know if a date is a holiday one or not, in that case you can use:
select
*
from
dbo.FactTable as f
cross apply
(
select top 3
d.date
from
dbo.dim_date as d
where
d.[date] < f.[date]
and d.IsHoliday = 0
order by
d.[date] DESC
)
go
BTW, which version of SS are you using?
AMB
|||
Hi Hunckback!
You gave me 2 good answers! Thanks...
I will think the better for my case!!
Thanks!!
|||
Customized function that works perfectly for variable holidays!
Code Snippet
ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))
ReturnsDateTime
as
Begin
IfExists(Select 1 From Holidays Where HolidayDate =Dateadd(DD,-1,@.CurrentDate)AND HV_City=@.City)
return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)
Else
If @.NumberOfDays = 0
return @.CurrentDate
Else
return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)
returnnull
End
Now, imagine if I have a table for fixed holidays, and the holidays will be repeated for all the years but in our table we have only
1998-12-25
It means, for each year the month/day 12-25 is a holiday...
how can I do it?
Thanks!
|||
Is there any spl flag then use the following query..
Code Snippet
If Exists(Select 1 From Holidays
Where (
(HolidayDate = Dateadd(DD,-1,@.CurrentDate) And RepeateFlag=0)
or
(Month(HolidayDate)=Month(@.CurrentDate) And Year(HolidayDate) = Year(@.CurrentDate)
And RepeateFlag=1)
) AND HV_City=@.City)
Return dbo.WorkingDateSubtract(@.NumberOfDays, DateAdd(DD,-1,@.CurrentDate),@.City)
|||
Why the use of RepeateFlag?
Thanks!
|||So, you mean to say all the holidays are common across all the year. If not how you will know from your table, this holiday is for all the year, this for only current year?|||
I changed the function to:
Code Snippet
ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))
ReturnsDateTime
as
Begin
IfExists(Select 1 From dbo.VariableHolidays
Where(
HolidayDate =Dateadd(DD,-1,@.CurrentDate)
OR((Month(HolidayDate)=Month(@.CurrentDate)AndYear(HolidayDate)=Year(@.CurrentDate)))
)
AND Cities_ShortName=@.City)
Return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)
Else
If @.NumberOfDays = 0
return @.CurrentDate
Else
return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)
returnnull
End
Does not work... :-(
If I have the date 1998-06-26 as holiday does not recognize date 2007-06-26 as holiday. and if I have the record 2007-06-26 in holiday table, does not recognize to.
But this statment works for all the dates for variableholidays:
Code Snippet
ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))
ReturnsDateTime
as
Begin
IfExists(Select 1 From dbo.VariableHolidays
Where HolidayDate =Dateadd(DD,-1,@.CurrentDate)
AND Cities_ShortName=@.City
)
return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)
Else
If @.NumberOfDays = 0
return @.CurrentDate
Else
return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)
returnnull
End
I was think to create 2 diferent tables, one for fixedHolidays and other for variableHolidays, but if I can do it in only one table, would be better!!
Other funtionality that is missing and I didn't told you before, and is to get only workdays... I tried the statment below but the result still return saturday and sunday... :-(
Code Snippet
ALTERFunction [dbo].[WorkingDateSubtract](@.NumberOfDays int, @.CurrentDate Datetime, @.City char(10))
ReturnsDateTime
as
Begin
IfExists(Select 1 From dbo.VariableHolidays
Where HolidayDate =Dateadd(DD,-1,@.CurrentDate)
AND Cities_ShortName=@.City
AND(DATEPART(DW,HolidayDate)=2
ORDATEPART(DW,HolidayDate)=3
ORDATEPART(DW,HolidayDate)=4
ORDATEPART(DW,HolidayDate)=5
ORDATEPART(DW,HolidayDate)=6
))
return dbo.WorkingDateSubtract(@.NumberOfDays,DateAdd(DD,-1,@.CurrentDate),@.City)
Else
If @.NumberOfDays = 0
return @.CurrentDate
Else
return dbo.WorkingDateSubtract(@.NumberOfDays-1,DateAdd(DD,-1,@.CurrentDate), @.City)
returnnull
End
How can I do it?
Thanks for all!!!
|||
Ok.. Things are getting complicated now rite 
Lets finish this now...
Code Snippet
Create Function [dbo].[WorkingDateSubtract]
(
@.NumberOfDays int,
@.CurrentDate Datetime,
@.City char(10)
)
Returns DateTime
as
Begin
If --Varibale Holidays
Exists(Select 1 From dbo.VariableHolidays
Where HolidayDate = @.CurrentDate
AND Cities_ShortName=@.City
)
Or --Fixed Holidays
Exists(Select 1 From dbo.FixedHolidays
Where Month(HolidayDate) = Month(@.CurrentDate)
And Day(HolidayDate) = Day(@.CurrentDate)
AND Cities_ShortName=@.City
)
Or --Saturday & Sundays
DateName(dw,@.CurrentDate) in ('Sunday', 'Saturday')
return dbo.WorkingDateSubtract(@.NumberOfDays, DateAdd(DD,-1,@.CurrentDate),@.City)
Else
If @.NumberOfDays = 0
return @.CurrentDate
Else
return dbo.WorkingDateSubtract(@.NumberOfDays-1, DateAdd(DD,-1,@.CurrentDate), @.City)
return null
End
|||
Dear Friend,
I delete the data in the tables FixedHolidays and VariableHolidays, in order to check if the funtion not return sundays and saturdays...
| … | | |
| 03-07-2007 | Tuesday | |
| 02-07-2007 | Monday | |
| 01-07-2007 | Sunday | |
| 30-06-2007 | Saturday | |
| 29-06-2007 | Friday | ok |
| 28-06-2007 | Thrusday | NOK |
| 27-06-2007 | Wendsday | ok |
| 26-06-2007 | Tuesday | ok |
| 25-06-2007 | Monday | |
| 24-06-2007 | Sunday | |
| 23-06-2007 | Saturday | NOK |
| 22-06-2007 | Friday | ok |
| 21-06-2007 | Thrusday | |
| 20-06-2007 | Tuesday | |
| 19-06-2007 | Wendsday | |
| 18-06-2007 | Tuesday | |
| 17-06-2007 | Monday | |
| 16-06-2007 | Sunday | |
| 15-06-2007 | Saturday | |
| … | | |
I was looking for each row, and for almost the days is correct, but for the day 28-06-2007 returns the saturday 23-06-2007
The numberofDays is 3 days for all rows in the example...
What you think about it?
Thanks for your important support!
|||Yes You are correct. I edited the code on my previous post. It should work fine now...
|||
Dear Manivannan,
The function recognize saturday/sunday, variable days, ans in the fixed holidays works but I edited:
Code Snippet
WhereMonth(HolidayDate)=Month(@.CurrentDate)
AndDay(HolidayDate)=Day(@.CurrentDate)
AND Cities_ShortName=@.City
I changed the YEAR to DAY in the function!
You were fantastic, You have here a friend!!!
If you need something from me, dont hesitate!
Regards and thanks!
|||
Dear Manivannan,
There is a bug... for the date 30-06-2007 that is a saturday... subtracting 3 days, the date returned is 26-06-2007 in spite of 27-06-2007...
The problem seems to be when the current date is a saturday/sunday... could you help me changing the function?
Thanks!!!!
|||
I thought the input always working day.
If you have the above requirement then we can't do it on the recursive method..
Here I edited the code..
Code Snippet
Create Function [dbo].[WorkingDateSubtract]
(
@.NumberOfDays int,
@.CurrentDate Datetime,
@.City char(10)
)
Returns DateTime
as
Begin
Declare @.Scope as Int;
Set @.Scope = 0;
While @.NumberOfDays <> 0
Begin
If --Varibale Holidays
Exists(Select 1 From dbo.VariableHolidays
Where HolidayDate = @.CurrentDate
AND Cities_ShortName=@.City
)
Or --Fixed Holidays
Exists(Select 1 From dbo.FixedHolidays
Where Month(HolidayDate) = Month(@.CurrentDate)
And Day(HolidayDate) = Day(@.CurrentDate)
AND Cities_ShortName=@.City
)
Or --Saturday & Sundays
DateName(dw,@.CurrentDate) in ('Sunday', 'Saturday')
Select @.NumberOfDays = Case When @.Scope=0 Then -1 Else 0 End + @.NumberOfDays,
@.CurrentDate = DateAdd(DD,-1,@.CurrentDate),
@.Scope = @.Scope +1
Else
If @.NumberOfDays = 0
Select @.CurrentDate = @.CurrentDate
Else
Select @.NumberOfDays = @.NumberOfDays-1,
@.CurrentDate = DateAdd(DD,-1,@.CurrentDate),
@.Scope = @.Scope +1
End
return @.CurrentDate
End
Please clarify me, the following all the dates will return '2007-06-27' as output. Is it correct?
Select [dbo].[WorkingDateSubtract](3,'2007-07-02','')
Select [dbo].[WorkingDateSubtract](3,'2007-07-01','')
Select [dbo].[WorkingDateSubtract](3,'2007-06-30','')
Suppose, if your requirement expect output ='2007-06-28' for input = '2007-07-01' then change the following line
(on the first IF condition)
Code Snippet
Select @.NumberOfDays = Case When @.Scope=0 Then -1 Else 0 End + @.NumberOfDays,
@.CurrentDate = DateAdd(DD,-1,@.CurrentDate),
@.Scope = @.Scope + Case When @.Scope= 0 Then 0 Else 1 End
