SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Date Table Function F_TABLE_DATE
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/21/2007 :  18:26:50  Show Profile  Reply with Quote
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

Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 05/22/2007 :  11:58:16  Show Profile  Reply with Quote
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?
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/22/2007 :  12:06:30  Show Profile  Reply with Quote
Maybe you should start a new thread in the appropriate forum (like T-SQL 2000 or 2005)


KH

Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 06/12/2007 :  07:03:06  Show Profile  Reply with Quote
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.
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 06/12/2007 :  07:22:39  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/12/2007 :  11:15:15  Show Profile  Reply with Quote
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
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 06/13/2007 :  03:22:10  Show Profile  Reply with Quote
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.
Go to Top of Page

ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 07/13/2007 :  17:06:03  Show Profile  Reply with Quote
very nice!

Ashley Rhodes
Go to Top of Page

tmaiden
Yak Posting Veteran

USA
86 Posts

Posted - 09/05/2007 :  09:50:59  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 09/05/2007 :  09:53:44  Show Profile  Reply with Quote

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
Go to Top of Page

tmaiden
Yak Posting Veteran

USA
86 Posts

Posted - 09/05/2007 :  15:41:08  Show Profile  Reply with Quote
duh. thanks
Go to Top of Page

scottbak
Starting Member

USA
16 Posts

Posted - 11/12/2007 :  19:43:50  Show Profile  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  19:53:15  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/17/2008 :  16:25:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/18/2008 :  01:51:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/18/2008 :  11:39:12  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/19/2008 :  16:41:22  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/19/2008 :  17:10:57  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/19/2008 :  17:39:37  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/19/2008 :  18:18:26  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000