| Author |
 Topic  |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
 Posted - 05/21/2007 : 18:26:50
|
quote:
Originally posted by duhaas
quick question, just tried to build this function in my sql 2000 database, and keep getting the following:
Server: Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the variable '@start_date'.
Server: Msg 137, Level 15, State 1, Procedure F_TABLE_DATE, Line 473
Must declare the variable '@end_date'.
the database is configuration for a case sensitve collation
Just make sure the variable declaration and reference are all of the same case. Quick way is to use FIND and REPLACE in Query Analyzer to do it. Make sure you don't check the match case
KH
|
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 05/22/2007 : 11:58:16
|
| thanks, that was the answer that i need. one other quick question, and maybe this isnt the right place to ask it, but how do i use the function now? if i have a date field that i want to pass into here and group by one of the output columns, whats the syntax? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 05/22/2007 : 12:06:30
|
Maybe you should start a new thread in the appropriate forum (like T-SQL 2000 or 2005)
KH
|
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2052 Posts |
Posted - 06/12/2007 : 07:03:06
|
Here are some long promised features that I have added to Michaels function.
As mentioned elsewhere I used the function to create a table DATEINFO.
I then stole used Andy and Rockmooses code from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49711
To add a column to my table UK_WORKING_DAY CHAR(1) which has the value 'N' if the date is a weekend or Bank Holiday (as determined by the link above) or 'Y' otherwise.
This allows me to create the next working date e.g. if the date is a saturday and the following monday is a bank holiday then it provides the date of the Tuesday. -
-- Create the field for the next working date
ALTER TABLE DATEINFO
ADD
NEXT_UK_WORKING_DATE DATETIME NULL -- Date of next UK work day
GO
-- Now work out the next working date
-- NOTE THAT this fails for the last date in the table
--
-- CREATE TEMPORARY TABLE for NEXT WORKING DATE
CREATE TABLE #NextWorkingDate
(
DATE DATETIME NOT NULL,
NEXT_WORKING_DATE DATETIME NULL, -- must be NULL for final entry
NEXT_WORKING_DATE_ID INT NULL -- coming soon, an ID that will allow you to calculate the number of working days between 2 dates
)
INSERT #NextWorkingDate(DATE,NEXT_WORKING_DATE)
SELECT d1.DATE,
NEXT_WORKING_DATE = (SELECT MIN(d2.DATE) FROM DATEINFO d2 WHERE d2.UK_WORKING_DAY = 'Y' AND D2.DATE > d1.DATE)
FROM DATEINFO d1
GROUP BY d1.DATE
-- Do update
UPDATE DATEINFO
SET NEXT_UK_WORKING_DATE = n.NEXT_WORKING_DATE
FROM dateinfo t inner join #NextWorkingDate n
On t.date = n.DATE
GO
The next stage for me is to work out a process that allows the number of working days between the two dates to be easily calculated
steve
-----------
Don't worry head. The computer will do all the thinking from now on. |
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2052 Posts |
Posted - 06/12/2007 : 07:22:39
|
quote:
The next stage for me is to work out a process that allows the number of working days between the two dates to be easily calculated
Hmm a little thought and I came up with this
DECLARE @START DATETIME
DECLARE @END DATETIME
SET @START = '20000109'
SET @END = '20000116'
SELECT COUNT(1) from DATEINFO
WHERE UK_WORKING_DAY = 'Y'
AND DATE > @START and DATE <= @END
All of my code here and in my previous post depends critically on the UK_WORKING_DAY column. As long as I can populate that (Thanks Andy and Rockmoose) then I can do whatever calculations I need in this vein.
This could be used for other applications that need to count(or whatever mathematical operation you need) events between two dates as long as the events column (in this case UK_WORKING_DAY) can be suitably populated.
steve
-----------
Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 06/12/2007 : 11:15:15
|
I am not sure about your organization, but one thing you might consider is that different sites or organizational structures can have different working days, and you may want to include this in your table design.
One site might have a local holiday or work rules that make their workdays different than another site. Examples: US and Canadian holidays, state and provincial holidays, religious holidays, etc. One place I worked had an office in Minnesota that shut down on the first day of deer season because so many people took the day off.
That is a big reason why I left working day information out of F_TABLE_DATE.
CODO ERGO SUM |
|
|
|
elwoos
Flowing Fount of Yak Knowledge
United Kingdom
2052 Posts |
Posted - 06/13/2007 : 03:22:10
|
Michael thanks for your feedback.
I do understand completely why you left this sort of information out. There is some variance even within the UK. I suspect (as I think you do) that to make a completely generic "working day" function would be impossible
Having said that I needed to calculate the working days (by my criteria) between two dates and I felt that it may be of general use to post it so that people could see the general principles I used and adjust them to their own circumstances. Particularly people who are relatively new to SQL Server.
Certainly no implicit (or explicit) criticism was intended.
steve
-----------
Don't worry head. The computer will do all the thinking from now on. |
|
|
|
ashley.sql
Constraint Violating Yak Guru
USA
299 Posts |
Posted - 07/13/2007 : 17:06:03
|
very nice!
Ashley Rhodes |
|
|
|
tmaiden
Yak Posting Veteran
USA
86 Posts |
Posted - 09/05/2007 : 09:50:59
|
How can I create a table named: dbo.datetable and insert the results of this function into it, based on a date range.
insert this_function('01/01/2005' to '12/31/2010') into datetabel
Thanks |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 09/05/2007 : 09:53:44
|
insert into datetable( <col list> )
select <col list>
from F_TABLE_DATE(<start date>, <end date>)
KH
Time is always against us
|
Edited by - khtan on 09/05/2007 09:54:28 |
|
|
|
tmaiden
Yak Posting Veteran
USA
86 Posts |
Posted - 09/05/2007 : 15:41:08
|
| duh. thanks |
|
|
|
scottbak
Starting Member
USA
16 Posts |
Posted - 11/12/2007 : 19:43:50
|
How can I make this work with more than one row in a table?
I have this:
declare @checkin datetime, @checkout datetime
set @checkin =(select checkin from bookings)
set @checkout=(select checkout from bookings)
select date
from dbo.F_TABLE_DATE (@checkin,@checkout)
but I get this error:
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Scott |
|
|
|
dataguru1971
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 11/12/2007 : 19:53:15
|
scottbak--keep it to the other thread where this was answered..
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92444
as was told there, your variable can only contain ONE date, so you can't select the entire column from bookings table as one value for the procedure. I posted a cursor sample using your described requirement that does what you need it to...
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11752 Posts |
Posted - 08/17/2008 : 16:25:24
|
add the code for WEEK_NUMBER_IN_MONTH to the function. It comes in handy at times
datepart(week, dateadd(DAY, @@datefirst - 1, DATE)) - datepart(week, dateadd(day, @@datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/18/2008 : 01:51:06
|
Or, depending on your business rules, (DATEPART(DAY, DATE) - 1) / 7 + 1 .
E 12°55'05.25"
N 56°04'39.16" |
Edited by - SwePeso on 08/18/2008 02:16:10 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/18/2008 : 11:39:12
|
I avoided defining any column in F_TABLE_DATE that has a meaning that varies widely from one organization to the next. Week of year, week of month, work days, and holidays are examples of this. I have seen so many different definitions of week of year and week of month that is seems pointless.
However, someone using F_TABLE_DATE may want to customize it by removing or adding columns that have a particular meaning for their organization, so week of year and week of month are probably good candidates for this.
I also avoided any dependency on the setting of @@datefirst, because that is a parameter that can be changed at runtime. The code in Mladen's example could avoid this by just replacing @@datefirst with a constant. It you are customizing F_TABLE_DATE for a particular organization, I would do it this way.
CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11752 Posts |
Posted - 08/19/2008 : 16:41:22
|
aha ok.. good to know the reason.
acctually my code can't use a constant.
@@datefirst setting doesn't matter here because it returns the same results for every setting. It's used just to get the correct weeks for the - operator.
I've chosen 3 as a constant for no particular reason.
look at this:
declare @startIntervalDate datetime, @endIntervalDate datetime
select @startIntervalDate = GETDATE(), @endIntervalDate = dateadd(m, 2, @startIntervalDate)
select datepart(week, dateadd(DAY, @@datefirst - 1, DATE)) - datepart(week, dateadd(day, @@datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH,
datepart(week, dateadd(DAY, 3, DATE)) - datepart(week, dateadd(day, 3, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH1,
DATE, START_OF_MONTH_DATE
from dbo.F_TABLE_DATE(@startIntervalDate, @endIntervalDate)
or did you mean something else, MVJ?
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
Edited by - spirit1 on 08/19/2008 16:42:16 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/19/2008 : 17:10:57
|
I don't think your code will do what you want in all cases; I am fairly sure that -47 is not the WEEK_NUMBER_IN_MONTH you wanted. See results below.
I am not eactly sure what you want the code to do; maybe you could explain that.
select [@@datefirst] = @@datefirst
declare @datefirst int
set @datefirst = 1
select
*
from
(
select
datepart(week, dateadd(DAY, @@datefirst - 1, DATE)) - datepart(week, dateadd(day, @@datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH_1,
datepart(week, dateadd(DAY, @datefirst - 1, DATE)) - datepart(week, dateadd(day, @datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH_2,
DATE,
START_OF_MONTH_DATE
from dbo.F_TABLE_DATE('20081201', '20081231')
) a
where
WEEK_NUMBER_IN_MONTH_1 <> WEEK_NUMBER_IN_MONTH_2
set @datefirst = 2
select
*
from
(
select
datepart(week, dateadd(DAY, @@datefirst - 1, DATE)) - datepart(week, dateadd(day, @@datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH_1,
datepart(week, dateadd(DAY, @datefirst - 1, DATE)) - datepart(week, dateadd(day, @datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH_2,
DATE,
START_OF_MONTH_DATE
from dbo.F_TABLE_DATE('20081201', '20081231')
) a
where
WEEK_NUMBER_IN_MONTH_1 <> WEEK_NUMBER_IN_MONTH_2
set @datefirst = 7
select
*
from
(
select
datepart(week, dateadd(DAY, @@datefirst - 1, DATE)) - datepart(week, dateadd(day, @@datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH_1,
datepart(week, dateadd(DAY, @datefirst - 1, DATE)) - datepart(week, dateadd(day, @datefirst - 1, START_OF_MONTH_DATE)) + 1 as WEEK_NUMBER_IN_MONTH_2,
DATE,
START_OF_MONTH_DATE
from dbo.F_TABLE_DATE('20081201', '20081231')
) a
where
WEEK_NUMBER_IN_MONTH_1 <> WEEK_NUMBER_IN_MONTH_2
Results:
@@datefirst
-----------
7
(1 row(s) affected)
WEEK_NUMBER_IN_MONTH_1 WEEK_NUMBER_IN_MONTH_2 DATE START_OF_MONTH_DATE
---------------------- ---------------------- ------------------------------------------------------ ------------------------------------------------------
1 2 2008-12-07 00:00:00.000 2008-12-01 00:00:00.000
2 3 2008-12-14 00:00:00.000 2008-12-01 00:00:00.000
3 4 2008-12-21 00:00:00.000 2008-12-01 00:00:00.000
-48 4 2008-12-26 00:00:00.000 2008-12-01 00:00:00.000
-48 4 2008-12-27 00:00:00.000 2008-12-01 00:00:00.000
-48 5 2008-12-28 00:00:00.000 2008-12-01 00:00:00.000
-47 5 2008-12-29 00:00:00.000 2008-12-01 00:00:00.000
-47 5 2008-12-30 00:00:00.000 2008-12-01 00:00:00.000
-47 5 2008-12-31 00:00:00.000 2008-12-01 00:00:00.000
(9 row(s) affected)
WEEK_NUMBER_IN_MONTH_1 WEEK_NUMBER_IN_MONTH_2 DATE START_OF_MONTH_DATE
---------------------- ---------------------- ------------------------------------------------------ ------------------------------------------------------
1 2 2008-12-06 00:00:00.000 2008-12-01 00:00:00.000
1 2 2008-12-07 00:00:00.000 2008-12-01 00:00:00.000
2 3 2008-12-13 00:00:00.000 2008-12-01 00:00:00.000
2 3 2008-12-14 00:00:00.000 2008-12-01 00:00:00.000
3 4 2008-12-20 00:00:00.000 2008-12-01 00:00:00.000
3 4 2008-12-21 00:00:00.000 2008-12-01 00:00:00.000
-48 4 2008-12-26 00:00:00.000 2008-12-01 00:00:00.000
-48 5 2008-12-27 00:00:00.000 2008-12-01 00:00:00.000
-48 5 2008-12-28 00:00:00.000 2008-12-01 00:00:00.000
-47 5 2008-12-29 00:00:00.000 2008-12-01 00:00:00.000
-47 5 2008-12-30 00:00:00.000 2008-12-01 00:00:00.000
(11 row(s) affected)
WEEK_NUMBER_IN_MONTH_1 WEEK_NUMBER_IN_MONTH_2 DATE START_OF_MONTH_DATE
---------------------- ---------------------- ------------------------------------------------------ ------------------------------------------------------
(0 row(s) affected)
CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11752 Posts |
Posted - 08/19/2008 : 17:39:37
|
hmm... interesting! haven't come accross that one yet
simply put if you open up the windows calendar it has 6 rows in which calendar data is displayed
those six rows are weeks and those are the numbers (1-6) i want for each day.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
Edited by - spirit1 on 08/19/2008 17:40:03 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/19/2008 : 18:18:26
|
Does that mean the week always starts on a particular day of the week?
So if it always started on Monday, and the first day of the month was Sunday, then day 1 would be week 1, days 2-8 would be week 2, days 9-15 would be week 3, etc.
If that is what you want, this will do it:
select
a.Date,
WEEK_OF_MONTH =
(datediff(dd,dateadd(dd,(datediff(dd,'17530101',a.START_OF_MONTH_DATE)/7)*7,'17530101'),a.DATE)/7)+1
from
dbo.F_TABLE_DATE('20080101', '20081231') a
order by
a.DATE
Note that if your week does not start on Monday, you will have to replace 17530101 with 17530102 for Tuesday, 17530103 for Wednesday, etc.
The algorithm is to find the latest Monday (or whatever day the week starts) that is on or before the first day of the month, get the difference in days from that date, divide by 7 to get weeks, and add 1 to start the week number from 1 instead of zero.
The logic for the week start date is here:
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 08/19/2008 18:29:10 |
|
|
|
Topic |
|
|
|
Date Table Function F_TABLE_DATE