Periods, Dates, Start and Finish

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Periods, Dates, Start and Finish

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-31 : 13:08:21

I want to expand my reporting interface. This will involve describing the ASP web interface, but this question has more to do about SQL...

I currently provide a web interface with a "StartDate" and "FinishDate" which provides reporting on a course in a specified window of time. Usually, people don't change the values, StartDate is the day the course launched, FinishDate is GETDATE().

I will be deploying courses soon which are to be taken every year, or every quarter. The course "renews" each period, and the reporting web interface will need to allow selecting a period.

I imagine there will be a web page drop-down listbox which allows picking a reporting period... which will set the start and finish dates...

Now here's the question. I'd like to know which "period" GETDATE() falls in. Given StartDate and Period (which is two columns as follows: )

Col1: "day", "week", "month", "quarter", "year"
Col2: intValue -- The number of days, weeks, months...

Write a SQL Query which returns the period (1, 2, ... N) given the above...

Here's a made-up table
CREATE TABLE MyTable (
Start DATETIME,
PeriodUnit VARCHAR (10), == "day", "week", etc..
PeriodLength INT , -- integer representing number of days, weeks..
)

What period is Today? GETDATE()?

Bonus points for the shortest answer.

(If there is a better way to represent the Period, suggestions are welcome.)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-31 : 13:17:37

How about Today.

Sorry sam I could not resist!

Jim
Users <> Logic

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-31 : 13:24:09

sufferin’ succotash!!!!

Good one. Now, how about a solution... ???

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-01-31 : 13:45:15

Define

"period"

Please

Jim
Users <> Logic

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-31 : 13:56:54

Maybe "interval" is a better word? If the reporting period is 2 "weeks", the first two weeks after the "Start" date is the first "period", the second two weeks after the Start date is the 2nd period.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-31 : 14:43:38

Does this do it for you??


Declare @StartDate datetime,
	@today datetime

Declare @myTable table (periodType varchar(100), periodLength int)
Insert Into @myTable
Select 'day', 10
Union Select 'day', 180
Union Select 'week', 1
Union Select 'week', 2
Union Select 'month', 1
Union Select 'quarter', 1
Union Select 'year', 1


Set @StartDate = '1/1/2004'
Set @today = convert(datetime,convert(varchar,getdate(),101))

Select 
	*,
	PeriodNum = case 
		when periodType = 'day' then ((datediff(dd,@startDate,@today)+(periodLength-1))/periodLength) 
		when periodType = 'week' then ((datediff(wk,@startDate,@today)+(periodLength-1))/periodLength) 
		when periodType = 'month' and day(@StartDate)=day(@today) then ((datediff(mm,@startDate,@today))/periodLength) 
		when periodType = 'month' then ((datediff(mm,@startDate,@today)+1)/periodLength) 
		when periodType = 'quarter' then ((datediff(q,@startDate,@today)+1)/periodLength) 
		when periodType = 'year' and day(@StartDate)=day(@today) and month(@StartDate)=month(@today) then ((datediff(yy,@startDate,@today))/periodLength) 
		when periodType = 'year' then ((datediff(yy,@startDate,@today)+1)/periodLength) 
		else null end
From @myTable

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-31 : 15:01:29

I had actually come up with something very similar:


set nocount on
Declare @tb Table
	(StartDate datetime
	,PeriodUnit varchar(10)
	,periodLength int)

insert @tb values ('1/1/2003', 'Day', 2)
insert @tb values ('1/1/2003', 'Week', 2)
insert @tb values ('1/1/2003', 'Month', 2)
insert @tb values ('1/1/2003', 'Quarter', 2)
insert @tb values ('1/1/2003', 'Year', 2)
insert @tb values ('1/1/2004', 'Day', 2)
insert @tb values ('1/1/2004', 'Week', 2)
insert @tb values ('1/1/2004', 'Month', 2)
insert @tb values ('1/1/2004', 'Quarter', 2)
insert @tb values ('1/1/2004', 'Year', 2)

Select	StartDate
	,PeriodUnit
	,PeriodLength
	,Period = Case 
		when periodunit = 'Day' then DateDiff(day, StartDate, getDate()) 		/ periodLength
		when periodunit = 'Week' then (DateDiff(day, StartDate, getDate()) / 7) 	/ periodLength
		when periodunit = 'Month' then DateDiff(month, StartDate, getDate()) 		/ periodLength
		when periodunit = 'Quarter' then (DateDiff(month, StartDate, getDate()) / 3) 	/ periodLength
		when periodunit = 'Year' then DateDiff(year, StartDate, getDate()) 		/ periodLength
		End
From	@tb

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-31 : 15:41:56

Thanks for both solutions. I tested both against every day in the year with a Tally Table. Always worried about boundry conditions...

set nocount on
Declare @tb Table
	(StartDate SMALLDATETIME
	,PeriodUnit varchar(10)
	,periodLength int)

insert @tb values ('1/1/2003', 'Day', 2)
insert @tb values ('1/1/2003', 'Week', 2)
insert @tb values ('1/1/2003', 'Month', 2)
insert @tb values ('1/1/2003', 'Quarter', 2)
insert @tb values ('1/1/2003', 'Year', 2)

-- Build a full year of dates to test this puppy
DECLARE	 @Dt Table (Today SMALLDATETIME)
INSERT INTO @DT (Today)
	SELECT	DATEADD(dd, ID-1, CAST('1/1/2003' As Datetime))
		FROM	Tally
		WHERE	ID BETWEEN 1 AND 367

Select	PeriodUnit, PeriodLength, StartDate, Today ,
	Period = Case 
		when periodunit = 'Day' then DateDiff(day, StartDate, Today) 		/ periodLength + 1
		when periodunit = 'Week' then (DateDiff(day, StartDate, Today)/7) 	/ periodLength + 1
		when periodunit = 'Month' then DateDiff(month, StartDate, Today) 	/ periodLength + 1
		when periodunit = 'Quarter' then (DateDiff(month, StartDate, Today) / 3)/ periodLength + 1
		when periodunit = 'Year' then DateDiff(year, StartDate, Today) 		/ periodLength + 1
		End

	FROM	@tb
	CROSS JOIN @Dt

	ORDER BY PeriodUnit, PeriodLength, StartDate, Today

TG - Ya need to add 1 to get the first period to start at 1, or you could do what Corey did and add (Period-1) to the numerator. Edit: Oops! You would need to add {Period} to the numerator.

I figured this out by testing, but I'll leave the answer as a teaser for readers: Why is Corey's solution incorrect to use

DATEDIFF(week, StartDate, FinishDate) in his answer?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-31 : 15:50:52

Right after I posted my response, I realized you needed it 1 based instead of 0 based.
Just add the "+ 1" at the end of the case statement, Sam.
ie:


	,Period = Case 
		when periodunit = 'Day' then DateDiff(day, StartDate, getDate()) 		/ periodLength
		when periodunit = 'Week' then (DateDiff(day, StartDate, getDate()) / 7) 	/ periodLength
		when periodunit = 'Month' then DateDiff(month, StartDate, getDate()) 		/ periodLength
		when periodunit = 'Quarter' then (DateDiff(month, StartDate, getDate()) / 3) 	/ periodLength
		when periodunit = 'Year' then DateDiff(year, StartDate, getDate()) 		/ periodLength
		End + 1

Be One with the Optimizer
TG

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-31 : 16:07:53

I know! I know!

It is 'incorrect' becuase it a week as an interval means 7 days beginning with the start date not @@datefirst

However, i used week on purpose, becuase it demonstrates the use for the week type, where as a 7-day interval is simply: 'day', 7

So, did you say mine worked or didn't. I got confused with your comments

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-31 : 16:28:23

quote:
Originally posted by Seventhnight

I know! I know!

It is 'incorrect' becuase it a week as an interval means 7 days beginning with the start date not @@datefirst

Yes! ding! ding! ding! ding! ding! ding! ding!

Confused comments are my trademark... I think you should be adding (PeriodLength) not: (PeriodLength-1) in the numerator...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-31 : 16:43:41

What date(s) were you using, that look incorrect? And which periodType was incorrect?

It seems to be correct to me.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-31 : 16:57:20

If you run the query I posted (you will need a table Tally with integers 1 through 500 or so) it is easy to see.

You can also look at the algebra like this:

If the following statement is correct:
when periodunit = 'Day' then DateDiff(day, StartDate, Today) / periodLength + 1

Then it is the same as:
when periodunit = 'Day' then (DateDiff(day, StartDate, Today) + periodLength) / periodLength

Boundrys are tricky. I ran tests and checked the boundry transitions visually to be sure. (I don't trust my math.)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-31 : 17:33:45

Yeah... I guess I can see that...

good call

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-26 : 11:00:34

I just got around to implementing this today. There's a problem

DateDiff(year, StartDate, getDate())

Returns a value of 1 not when StartDate and Getdate are a year apart, but when the January 1 boundary is crossed...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-26 : 11:06:45

DateDiff works strictly on boundaries, not intervals or duration. You'd need to use a smaller interval and divide:

SELECT DateDiff(day, StartDate, getdate())/365

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-26 : 11:31:45

I've been chewing on this. It's almost OK for years since leaps come only every 4 years. Less satisfactory for month or quarter incriments.

Given a day like 'Feb 26 2005' is @today in the following quarter, e.g. May 26, 2005. Single days make a difference.

I suppose there's a way of solving this, but it'll be unsatisfyingly complex.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-26 : 13:55:05

Sam, could you post the code you've implemented and a couple of example dates that aren't working along with the desired result?

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-26 : 15:24:21

quote:
Originally posted by TG

Sam, could you post the code you've implemented and a couple of example dates that aren't working along with the desired result?

My working code is similar to what I last posted above.

The basic element of the problem is given a start date, period ('year', 'quarter', 'week', 'day'), what period am in in on any given date e.g. @Today ? At this time, the interesting calculated parameters would be the StartDate, FinishDate and perhaps what period I am in.

For example:

CREATE MyTable (
  Start SMALLDATETIME ,
  PeriodUnit INT ,
  PeriodLength INT )

INSERT INTO MyTable (Start, PeriodUnit, PeriodLength)
 VALUES ('Feb 11 2005', 'qtr', 2)

-- First Start date is Feb 11, course renews every 2 quarters, so:
The first interval is Feb 11 to Aug 10 (period 1)
The second interval is Aug 11 to Feb 10 (period 2)

I'll need to find StartDate, FinishDate and PeriodNumber for any given date @Today.

I'm working on something that unfortunately uses Tally tables. I'll post it tomorrow.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-26 : 15:56:47

quote:
I'm working on something that unfortunately uses Tally tables

What's unfortunate about it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources