June 10, 2008 at 4:03 am
I want the count of number of weeks between two given dates.My week starts on Monday and ends on Sunday.
IF Startdate = '2008-06-01' AND Enddate = '2008-06-04' The count should be 2 since startdate is on sunday which belongs to previous week and enddate is on Wednesday which belongs to this week.
IF Startdate = '2008-06-01' AND Enddate = '2008-06-01' OR
Startdate = '2008-06-02' AND Enddate = '2008-06-02' OR
Startdate = '2008-06-02' AND Enddate = '2008-06-08' OR
Startdate = '2008-06-05' AND Enddate = '2008-06-07' Then count should be 1 since they all lie within one week Monday to Sunday range.
IF Startdate = '2008-06-01' AND Enddate = '2008-06-09' COUNT = 3 (3 weeks)
Startdate = '2008-06-02' AND Enddate = '2008-06-16' COUNT = 3 (3 weeks)
Startdate = '2008-06-01' AND Enddate = '2008-07-18' COUNT = 8 (8 weeks)
CREATE PROCEDURE WEEK_COUNT @Startdate datetime,@enddate datetime,@count INT output
as
SET NOCOUNT ON
SET NOCOUNT OFF
--DROP PROC WEEK_COUNT
June 10, 2008 at 4:08 am
HI,
Have you tried looking up the use of Tally tables which will give you a continuious list of running dates and then applying your logic to that?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 10, 2008 at 4:31 am
Is tally table the only option to get the results? I do'nt want to use any temp tables in my procedure.
June 10, 2008 at 5:32 am
TALLY tables are not temp tables (they do not need to be and are most often not). It is a table used for operations in which you need large lists.
Look them up on this site, Jeff Moden has a number of really good articles about them and probably one that will describe almost exactly what you are trying to do.
You do not need to use a tally table for what you are doing, but it is going to be the most efficient and easiest way to accomplish the task.
And you will probably get a post from Jeff on this. I think we gets an email when someone posts anything with "tally" in it.
June 10, 2008 at 6:15 am
CREATE FUNCTIONdbo.fnWeekDiff
(
@Date1 DATETIME,
@Date2 DATETIME
)
RETURNS INT
AS
BEGIN
RETURN1 + ABS(DATEDIFF(DAY, '19000101', @Date1) / 7 - DATEDIFF(DAY, '19000101', @Date2) / 7)
END
GO
DECLARE@Date1 DATETIME,
@Date2 DATETIME
SELECT@Date1 = '20080602',
@Date2 = '20080608'
SELECTdbo.fnWeekDiff(@Date1, @Date2),
dbo.fnWeekDiff(@Date2, @Date1)
N 56°04'39.16"
E 12°55'05.25"
June 10, 2008 at 6:21 am
SUPERB!! this is really amazing just one query, this is the reason I like this forum. I have done this but it looks very complicated to the above query. THANKS.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply