February 13, 2015 at 2:17 am
Hi,
I am newbie in SQL, I got a requirement from my Business Analyst asking me "We have a requirement to show 'Working Date or Not ' as a output for the below input parameters".
Input
a) Input Date (Date format)
b) Factory Calendar for different countries (2 digit char)
c) After No of Days (Either minus or plus in days)
Output
Date (Date format)
Could please give advice on this requirements. Many Thanks in Advance.
February 13, 2015 at 2:25 am
This will be a lot easier if you've got a calendar table. This one[/url] is a good place to start. You might have to modify it to show business days for your location.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 13, 2015 at 2:36 am
The requirement is to see more than 10 Countries, at the moment I have standard dim_date table in my system. Do you want to add 10 extra columns to existing table with each Country holidays, Weekends and all. Could you able to give structure of the table the one you talking about?
Many Thanks
February 13, 2015 at 3:01 am
Sangeeth878787 (2/13/2015)
The requirement is to see more than 10 Countries, at the moment I have standard dim_date table in my system. Do you want to add 10 extra columns to existing table with each Country holidays, Weekends and all. Could you able to give structure of the table the one you talking about?Many Thanks
Adding the extra columns is one way to go, we use 1 or 0 to indicate working days or not. The table structure is essentially going to be another column added to your existing table and I don't know what that looks like. You could also create a function to whether or not a day is a business day or not. There is definitely more than one way of going about this and the best way of doing it will depend on your and the business's requirements.
The most important thing you need to do is make sure that you have the logic for establishing business days correct. For ten countries this is no small task and here Google is certainly going to be your friend. This site[/url] was a big help when I was doing something similar recently.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 13, 2015 at 3:23 am
Hi,
What kind of requirement you had to do, do you have script for that, As a newbie I will try to learn and analyse which will helpful to improve my TSQL skills.
Many Thanks in Advance
February 13, 2015 at 4:38 am
I had to update an existing date table to show whether a particular day was a bank holiday in England or Wales or not. The table already had a column that showed if the day was a working day or not but it was inaccurate. It simply showed a 1 for Monday to Friday or a 0 for Sunday and didn't account for holidays. I also had to account for Easter so I found a function to calculate that online because the logic for that is extremely complex, fortunately somebody has already done that for us. I've included that function for you too.
I've included the basic function below, it calculates whether or not a day is a Bank Holiday. Bear in mind that this only works for England and Wales. You should be able to adapt it to your needs though.
select
Fulldate
,case
when ------New Years Day when not a Saturday or Sunday
DateOfMonth = 1
and ShortMonthOfYear= 'Jan'
and DayNumberOfWeek not in (6,7)
then 0
when ---If NYD falls on a weekend, finds the first Monday of the year
fulldate = DATEADD(day,DATEDIFF(day,'19000101',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'0101')),2))/7*7,'19000101') then 1
when ----Christmas Day when not a Saturday or Sunday
dateOfMonth = 25
and ShortMonthOfYear= 'Dec'
and DayNumberOfWeek not in (6,7)
then 1
when --If Christmas day falls on Saturday or Sunday sets the first Monday after as BH
fulldate = DATEADD(day,DATEDIFF(day,'19000129',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'1201')),30))/7*7,'19000129')
and DateOfMonth < 29then 1
when ----Boxing Day when not a Saturday or Sunday
DateOfMonth = 26
and ShortMonthOfYear= 'Dec'
and DayNumberOfWeek not in (6,7) then 1
when --If Boxing day falls on Saturday or Sunday sets the first Tuesday after as BH
fulldate = DATEADD(day,DATEDIFF(day,'19000130',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'1201')),31))/7*7,'19000130')
and DateOfMonth < 29then 1
when --Early May BH, first Monday of May
FullDate = DATEADD(day,DATEDIFF(day,'19000101',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'0501')),6))/7*7,'19000101') then 0
when --Spring Bank Holiday (last Monday in May) when year not 2002 or 2012
fulldate = DATEADD(day,DATEDIFF(day,'19000129',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'0501')),30))/7*7,'19000129')
and dateyear not in (2002,2012)then 1
when FullDate in ( -----Moved Spring Bank Holiday to give long w\e for Queen's Jubilee
'2002-06-03 00:00:00.000'
,'2012-06-04 00:00:00.000'
) then 1
when FullDate in ( -----Extra days added for Queens's Jubilees
'2002-06-04 00:00:00.000'
,'2012-06-05 00:00:00.000'
) then 1
when ---August BH, last Monday in August
FullDate = DATEADD(day,DATEDIFF(day,'19000101',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'0801')),30))/7*7,'19000101')then 1
when FullDate = Mydatabase.dbo.year2easter(datepart(year,FullDate)) - 2/*Good Friday, 2 days before Easter Sunday*/then 1
when FullDate = Mydatabase.dbo.year2easter(datepart(year,FullDate)) +1/*Easter Monday, 1 day after Easter Sunday*/then 0
when FullDate = '2011-04-29 00:00:00.000' /*Extra day for Royal Wedding*/ then 1
else 0
end
from yourdatabase.dbo.D_Date dd
The key line is this one:
DATEADD(day,DATEDIFF(day,'19000101',DATEADD(month,DATEDIFF(MONTH,0,(CAST(YEAR(fulldate) AS VARCHAR(4))+'0801')),30))/7*7,'19000101')
This can be used to find holidays that always fall on a fixed day for example the last Tuesday in July or the first Monday in December. The example calculates the last Monday in August. If you break down how it works it will certainly help.
The function below calculates non-Orthodox Easter and you'll need to create this before you run the code above.
create Function [dbo].[Year2Easter] (
@Year int
)
RETURNS datetime
As
Begin
/*
Source of algorithm : Nature, 1876 April 20, vol. 13, p. 487
Converted to T-SQL : Robert Davis, February 6, 2005
*/
/* Integer variables for mathematical computations */
Declare @a int, @b-2 int, @C int, @d int, @e int, @f int, @g int, @h int, @i int, @k int, @l int, @m int, @p int
/* Integer variables for mathematical results */
Declare @EasterMonth int, @EasterDay int
--declare @year int = 2015
Declare @Easter varchar(45)
If @Year < 1582 -- oldest year for which algorithm works
Begin
Set @Easter = 'Year2Easter() expects a 4 digit year => 1582.'
End
Else
Begin
Set @a = @Year % 19
Set @b-2 = @Year / 100
Set @C = @Year % 100
Set @d = @b-2 / 4
Set @e = @b-2 % 4
Set @f = (@b + 8) / 25
Set @g = (@b - @f + 1) / 3
Set @h = (19 * @a + @b-2 - @d - @g + 15) % 30
Set @i = @C / 4
Set @l = (32 + 2 * @e + 2 * @i - @h - @k) % 7
Set @m = (@a + 11 * @h + 22 * @l) / 451
Set @EasterMonth = (@h + @l - 7 * @m + 114) / 31-- [3 = March, 4 = April]
Set @p = (@h + @l - 7 * @m + 114) % 31
set @EasterDay = @p + 1-- Day of the month
Select @Easter = @EasterMonth
Select @Easter = cast(Cast(@Year as varchar)+'-0'+ @Easter +'-' + case
when @EasterDay < 10 then '0'+ cast(@EasterDay as varchar)
else Cast(@EasterDay as varchar)
end
as datetime)
End
return @easter
End
GO
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 13, 2015 at 4:55 am
Sangeeth878787 (2/13/2015)
Hi,What kind of requirement you had to do, do you have script for that, As a newbie I will try to learn and analyse which will helpful to improve my TSQL skills.
Many Thanks in Advance
What have you tried?
If you look at the link provided by BWFC, this is one example of a calendar table. You may not need all of these columns, or you may need to add more.
Your current dim_date table? Is this a dimension in a data warehouse? If so, I likely would not change this table.
As for adding 10 columns for each country, that will probably be a headache.
I would create a second table in addition to the calendar table, This would consist of the date and country to indicate a holiday.
You can then join to the calendar table to get the working days.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 13, 2015 at 8:52 am
I am glad for your script, Many Thanks
February 13, 2015 at 8:54 am
You're welcome. Hopefully it pointed you in the right direction.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
February 13, 2015 at 9:00 am
Hi,
I wrote a function, which gives the next working day with by looking into holiday table for one country. But I am not able to succeed to back the date, I mean if i gave the date as ('13-02-2015, -2), The requirement is to see the past date as well.
It is a migration project from SAP to SQL, In SAP they have calendar table with more than 50 countries with holidays list as well. We are trying to achieve this in SQL. I will post the script once we achieve the requirement in terms of SQL.
Many Thanks
February 13, 2015 at 9:35 am
Michael L John (2/13/2015)
Sangeeth878787 (2/13/2015)
Hi,What kind of requirement you had to do, do you have script for that, As a newbie I will try to learn and analyse which will helpful to improve my TSQL skills.
Many Thanks in Advance
What have you tried?
If you look at the link provided by BWFC, this is one example of a calendar table. You may not need all of these columns, or you may need to add more.
Your current dim_date table? Is this a dimension in a data warehouse? If so, I likely would not change this table.
As for adding 10 columns for each country, that will probably be a headache.
I would create a second table in addition to the calendar table, This would consist of the date and country to indicate a holiday.
You can then join to the calendar table to get the working days.
Based on your requirement this would be my suggestion. You can then have a table valued function that joins your dim_date to this new table based on a country code that is being passed it, which would give you a calendar "table" with a "Is working day" column.
February 13, 2015 at 9:38 am
Sangeeth878787 (2/13/2015)
Hi,I wrote a function, which gives the next working day with by looking into holiday table for one country. But I am not able to succeed to back the date, I mean if i gave the date as ('13-02-2015, -2), The requirement is to see the past date as well.
It is a migration project from SAP to SQL, In SAP they have calendar table with more than 50 countries with holidays list as well. We are trying to achieve this in SQL. I will post the script once we achieve the requirement in terms of SQL.
Many Thanks
Can you actually give us a full example of what would be passed as input and what you would expect to see as output?
I am a bit unclear as to what the plus minus days are for, and as to what you expect to be returned. This shouldn't be hard but we can't be exact if we dont know what you need.
February 13, 2015 at 1:46 pm
Nevyn (2/13/2015)
Sangeeth878787 (2/13/2015)
Hi,I wrote a function, which gives the next working day with by looking into holiday table for one country. But I am not able to succeed to back the date, I mean if i gave the date as ('13-02-2015, -2), The requirement is to see the past date as well.
It is a migration project from SAP to SQL, In SAP they have calendar table with more than 50 countries with holidays list as well. We are trying to achieve this in SQL. I will post the script once we achieve the requirement in terms of SQL.
Many Thanks
Can you actually give us a full example of what would be passed as input and what you would expect to see as output?
I am a bit unclear as to what the plus minus days are for, and as to what you expect to be returned. This shouldn't be hard but we can't be exact if we dont know what you need.
+1 on examples.
Also keep in mind that some of those "working days" examples are trying to figure out where to route stuff based on timezone etc...depending on how your 10 countries are in relation to each other, the working day isn't the same. For example - the "working day" in EST is winding down right now, but is in full swing in Hawaii and is not yet started in Japan. You'd probably want to know if you're solving for that level of complexity.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 13, 2015 at 1:52 pm
Sangeeth878787 (2/13/2015)
Hi,[font="Arial Black"]I wrote a function,[/font] which gives the next working day with by looking into holiday table for one country. But I am not able to succeed to back the date, I mean if i gave the date as ('13-02-2015, -2), The requirement is to see the past date as well.
It is a migration project from SAP to SQL, In SAP they have calendar table with more than 50 countries with holidays list as well. We are trying to achieve this in SQL. I will post the script once we achieve the requirement in terms of SQL.
Many Thanks
Understood on your requirements. Please post your current function and the CREATE TABLE statement for your current calendar table and holiday table(s).
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2015 at 2:14 pm
NA
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply