| Author |
 Topic  |
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
 Posted - 08/20/2008 : 04:38:48
|
no. the day of the week start is irrelevant. this is what i wanted
June 2008
WEEK_NUM Mon Tue Wed Thu Fri Sat Sun
1 1
2 2 3 4 5 6 7 8
3 9 10 11 12 13 14 15
4 16 17 18 19 20 21 22
5 23 24 25 26 27 28 29
6 30
August 2008
WEEK_NUM Mon Tue Wed Thu Fri Sat Sun
1 1 2 3
2 4 5 6 7 8 9 10
3 11 12 13 14 15 16 17
4 18 19 20 21 22 23 24
5 25 26 27 28 29 30 31
6
but as it seems i woun't be needing this after all.. thanx for suggestions though.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 08/20/2008 : 10:11:42
|
quote:
Originally posted by spirit1
no. the day of the week start is irrelevant. this is what i wanted
June 2008
WEEK_NUM Mon Tue Wed Thu Fri Sat Sun
1 1
2 2 3 4 5 6 7 8
3 9 10 11 12 13 14 15
4 16 17 18 19 20 21 22
5 23 24 25 26 27 28 29
6 30
August 2008
WEEK_NUM Mon Tue Wed Thu Fri Sat Sun
1 1 2 3
2 4 5 6 7 8 9 10
3 11 12 13 14 15 16 17
4 18 19 20 21 22 23 24
5 25 26 27 28 29 30 31
6
but as it seems i woun't be needing this after all.. thanx for suggestions though.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Well, the week does start on a certain day of the week. In your example above, the weeks start on Monday except for the first week of the month.
If you run the code I posted last, you will see that the results for WEEK_OF_MONTH are an exact match to your example.
CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
PaulSalmon
Starting Member
United Kingdom
2 Posts |
Posted - 11/27/2008 : 07:58:07
|
Not as wizzy as Andy's calculation for holidays and public holidays but here's my addition for a BIT field to Michael's table to see if a date is part of the working week (Monday to Friday) :
[IS_WEEKDAY] = ((datediff(dd,'17530101',a.[DATE])%7))/5,
If your working week starts on Sunday then change the initial date to 17530107
Now I have a question - I wanted to add a field to track which Financial Year (FY) a date is in (I know this will vary from company to company so I wouldn't expect it to be standard part of the table). I had hoped to use a simple offset but I can't due to the start of FY occuring after the end of February, so I need to take leap years into account. I could do this via temporary table, like the ISO_WEEK table but that seems like overkill and I was hoping someone here could come up with a more elegant solution. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 11/27/2008 : 08:06:48
|
quote:
Originally posted by PaulSalmon
[IS_WEEKDAY] = ((datediff(dd,'17530101',a.[DATE])%7))/5
No. That will give you the weekends.
See thisSELECT d.theDate,
DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend,
1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday
FROM (
SELECT '20081124' AS theDate UNION ALL
SELECT '20081125' UNION ALL
SELECT '20081126' UNION ALL
SELECT '20081127' UNION ALL
SELECT '20081128' UNION ALL
SELECT '20081129' UNION ALL
SELECT '20081130'
) AS d
E 12°55'05.63"
N 56°04'39.26" |
Edited by - SwePeso on 11/27/2008 08:07:33 |
|
|
|
PaulSalmon
Starting Member
United Kingdom
2 Posts |
Posted - 11/28/2008 : 08:22:10
|
Thanks Peso, you are correct. I was actually using the flag to deduct the number of weekend days from an interval so should have called the field IS_WEEKEND.
Can you help with my question about calculating the Financial Year? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 11/28/2008 : 09:26:03
|
quote:
Originally posted by PaulSalmon
Thanks Peso, you are correct. I was actually using the flag to deduct the number of weekend days from an interval so should have called the field IS_WEEKEND.
Can you help with my question about calculating the Financial Year?
It would be better if you posted you question as a new topic, and provide an actual definition of your organization's Fiscal Year so that someone can help you without having to ask a lot of follow-up questions.
CODO ERGO SUM |
|
|
|
Weezy
Starting Member
USA
3 Posts |
Posted - 01/21/2009 : 09:26:23
|
Michael:
Thanks for this function! I have a quesiton, well probalby a couple, and I am not asking you to do my work but I have a unique wrinkle. A customer in the entertainment business starts their week on a Friday. This ends up being all kinds of fun as you may imagine. I have written a couple Friday-finding functions and DOW functions and so on, but I think a lookup table for some data warehousing is going to be better in the long run, hence I found your function.
Your opinion: Should I rewrite your function to support their view of the universe? Should I just run your function and write 50-60 UPDATE statements to tweak the table along with manually editing those 5 days in 2010 that really belong to 2009?
I started to tweak things in the funciton and kept thinking of all of their blasted exceptions and thought I would get your opinion first.
Thanks again.
Weezy |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 01/21/2009 : 09:56:05
|
quote:
Originally posted by Weezy
Michael:
Thanks for this function! I have a quesiton, well probalby a couple, and I am not asking you to do my work but I have a unique wrinkle. A customer in the entertainment business starts their week on a Friday. This ends up being all kinds of fun as you may imagine. I have written a couple Friday-finding functions and DOW functions and so on, but I think a lookup table for some data warehousing is going to be better in the long run, hence I found your function.
Your opinion: Should I rewrite your function to support their view of the universe? Should I just run your function and write 50-60 UPDATE statements to tweak the table along with manually editing those 5 days in 2010 that really belong to 2009?
I started to tweak things in the funciton and kept thinking of all of their blasted exceptions and thought I would get your opinion first.
Thanks again.
Weezy
The function already has columns for support of weeks that start on Friday (or any day of the week):
[START_OF_WEEK_STARTING_FRI_DATE] [datetime] not null ,
[END_OF_WEEK_STARTING_FRI_DATE] [datetime] not null ,
[WEEK_STARTING_FRI_SEQ_NO] [int] not null ,
It sounds like your issues really revolve around fiscal or business periods specific for a particular company. For that situation, I recommend leaving the function alone, since it is really about calendar dates that are not specific to any company, and creating another table with info about fiscal years, months, weeks, etc.
CODO ERGO SUM |
|
|
|
rwaldron
Posting Yak Master
129 Posts |
Posted - 11/09/2009 : 05:59:53
|
Hiya MVJ,
I have used your brilliant F_TABLE_DATE function for the last year wit great results..
However I now have a requirement to calculate business days EXCLUDING BANK HOLDAYS etc.
The way I could see this working is to create a table with a list of dates.These dates are to be excluded from the function.
Can you tell me how to encorporate this using your F_TABLE_DATE ?
Currently I use your func like below which gives me business days ie: not sat/sun.
f_get_work_days(startdate,enddate) as workdays |
|
|
|
AskQueAns
Starting Member
1 Posts |
Posted - 05/11/2010 : 04:13:05
|
Hi
This function helps me alot and solve my whole problem except one i.e. how to get half_year_start_date and half_year_end date between 2 dadtes?
Please reply asap. |
|
|
|
DavidChel
Constraint Violating Yak Guru
USA
474 Posts |
Posted - 08/26/2010 : 15:19:20
|
I really like this function and have customized it quite a bit.
I have a couple of questions.
1. I'd like to include it in a blog post. Does Mr. Jones have a blog that he'd like me to link to or should I simply link to this thread?
2. Is there a compelling reason why the YearDayOfYear and YearMonth can't be a numeric type to facilitate easier format so the 256th day of 2010 would be:
2010.256 rather than 2010256
Thanks for such a great script.
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 08/26/2010 : 15:46:54
|
Just link to this thread.
[YEAR], [MONTH], and [DAY_OF_YEAR] are already broken out to individual columns if you need. However, there is no reason why you can't customize your local version to suit your own needs.
CODO ERGO SUM |
|
|
|
DavidChel
Constraint Violating Yak Guru
USA
474 Posts |
Posted - 08/26/2010 : 15:53:38
|
| Thank you Sir. |
|
|
|
MashUp
Starting Member
1 Posts |
Posted - 10/07/2011 : 07:01:35
|
Has anyone seen similar in Oracle SQL?
Not everything that counts can be counted, and not everything that can be counted counts. |
|
|
|
MorrisK
Yak Posting Veteran
80 Posts |
Posted - 02/08/2012 : 15:40:08
|
Thanks for the great function. I noticed something and I wondered if it could be an error.
The column DATE_FORMAT_YYYY_M_D appears to actually return YYYY/D/M.
It looks like this
[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))),
should be replaced with this
[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))), |
Edited by - MorrisK on 02/08/2012 15:45:12 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 07/05/2012 : 02:55:33
|
quote:
Originally posted by MorrisK
Thanks for the great function. I noticed something and I wondered if it could be an error.
The column DATE_FORMAT_YYYY_M_D appears to actually return YYYY/D/M.
It looks like this
[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))),
should be replaced with this
[DATE_FORMAT_YYYY_M_D] =
convert(varchar(10),
convert(varchar(4),year(a.[DATE]))+'/'+
convert(varchar(2),month(a.[DATE]))+'/'+
convert(varchar(2),day(a.[DATE]))),
I made the suggested fix.
CODO ERGO SUM |
|
|
|
DennisTowers
Starting Member
USA
1 Posts |
Posted - 10/09/2012 : 23:06:38
|
| Best Darned Date Dimension, Ever! Thanks very much. Dennis. |
|
|
|
ienneg
Starting Member
France
3 Posts |
Posted - 11/20/2012 : 12:19:16
|
Hi,
This is a great function and it helps a lot.
Now, that said, could you help add to it the ISO Quarter number?
Thanks
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
Posted - 11/20/2012 : 23:58:17
|
quote:
Originally posted by ienneg
Hi,
This is a great function and it helps a lot.
Now, that said, could you help add to it the ISO Quarter number?
Thanks
I am unaware of any actual definition or standard for "ISO Quarter number". Do you have a link to a standard or definition?
Function F_TABLE_DATE does have a column for the calendar quarter, QUARTER, where months 1-3 are Q1, 4-6 are Q2, 7-9 are Q3 and 10-12 are Q4. There ia also a YEAR_QUARTER column in format YYYYQ, Example = 20052
CODO ERGO SUM |
|
|
|
Topic |
|
Date Table Function F_TABLE_DATE
