Returning the correct date/periodsequence

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 2005 Forums

Transact-SQL (2005)

Returning the correct date/periodsequence

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-07-05 : 20:25:07

Hi guys,
Im having a little difficulty returning some results from the table shown below:


periodseq	description		startdate	enddate		periodtypeid	parentseq	name		generation
113		Q3 FY 2006-7		1/01/2007	31/03/2007	quarter		104		Q3 FY 2006-7	1
114		Jan-07			1/01/2007	31/01/2007	month		113		Jan-07		1
115		Feb-07			1/02/2007	28/02/2007	month		113		Feb-07		1
116		Mar-07			1/03/2007	31/03/2007	month		113		Mar-07		1
117		Q4 FY 2006-7		1/04/2007	30/06/2007	quarter		104		Q4 FY 2006-7	1
118		Apr-07			1/04/2007	30/04/2007	month		117		Apr-07		1
119		May-07			1/05/2007	31/05/2007	month		117		May-07		1
120		Jun-07			1/06/2007	30/06/2007	month		117		Jun-07		1
121		Fiscal Year 2007-8	1/07/2007	30/06/2008	year		1		FY 2007-8	1
122		Q1 FY 2007-8		1/07/2007	30/09/2007	quarter		121		Q1 FY 2007-8	1
123		Jul-07			1/07/2007	31/07/2007	month		122		Jul-07		1
124		Aug-07			1/08/2007	31/08/2007	month		122		Aug-07		1

I wish to get the last periodseq, given the current month (sysdate-as I use an oracle DTS package for the import).
the current code I have to do this is:


select max(periodseq-1) from period where periodtypeid = 'month' and startdate <sysdate

Which returns the number 122, and is a problem because I wanted 120 (this being the last months date (i.e: Jun-07), and as there are other sequences in there to confuse the matter (i.e: Fiscal years and quarters, i cannot use a generic max(periodseq-1), that is for this month I had to use max(periodseq-3) and in other months i use -1.
So my question is, is there a way to find the last month (periodseq) given the system date (or current month)? maybe something to do with the startdate?
Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 21:15:42

this will give you a hint

select dateadd(month, datediff(month, 0, current_timestamp), 0)

Peter Larsson
Helsingborg, Sweden

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-07-05 : 21:31:53

sorry im not sure how.
this only returns the current month.
i need the periodseq before the current month, but not including ones (periodseq) where periodtypeid is not 'month'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-05 : 21:51:20

This should do what you want:


select
	max(periodseq)
from
	period
where
	periodtypeid = 'month' and
	periodseq <
	(
	select
		min(periodseq)
	from
		period
	where
		periodtypeid = 'month' and
		getdate() >= startdate
	)

By the way, the design of that table is what is causing your problem. It would be better to have a different table for each type of period. Then you could depend on having a simple numeric sequence for each type of period.

Seperate sequence numbers for each type of period is what I implemeted in the function on the link below for just that reason. There are sequence numbers for quarters, months, and weeks (starting on all 7 days of the week).

Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
"*** Sequence No columns are intended to allow easy offsets by
*** Quarter, Month, or Week for applications that need to look at
*** Last or Next Quarter, Month, or Week. Thay can also be used to
*** generate dynamic cross tab results by Quarter, Month, or Week.

QUARTER_SEQ_NO
Sequential Quarter number as offset from Quarter starting 1753/01/01

MONTH_SEQ_NO
Sequential Month number as offset from Month starting 1753/01/01

WEEK_STARTING_SUN_SEQ_NO
Sequential Week number as offset from Week starting Sunday, 1753/01/07

WEEK_STARTING_MON_SEQ_NO
Sequential Week number as offset from Week starting Monday, 1753/01/01

WEEK_STARTING_TUE_SEQ_NO
Sequential Week number as offset from Week starting Tuesday, 1753/01/02

WEEK_STARTING_WED_SEQ_NO
Sequential Week number as offset from Week starting Wednesday, 1753/01/03

WEEK_STARTING_THU_SEQ_NO
Sequential Week number as offset from Week starting Thursday, 1753/01/04

WEEK_STARTING_FRI_SEQ_NO
Sequential Week number as offset from Week starting Friday, 1753/01/05

WEEK_STARTING_SAT_SEQ_NO
Sequential Week number as offset from Week starting Saturday, 1753/01/06"

CODO ERGO SUM

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-07-05 : 22:10:06

hmmm....this seems to return null.

yeah i realised the table structure is pretty crappy. i was thinking of creating a temp table but this would be a pain for me as have an automatic DTS package that runs this script.

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-07-05 : 23:58:21

Peso will the below work??? I know it works for this timestamp (i.e: we are in July), but im not sure if it will work when we are in August, i.e: will it return July periodsequence of 123?


select max(periodseq) from period 
where periodtypeid = 'month' 
and startdate <dateadd(MONTH, datediff(MONTH, 0, current_timestamp), 0)

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-07-06 : 01:32:21

Peso will the below work??? I know it works for this timestamp (i.e: we are in July), but im not sure if it will work when we are in August, i.e: will it return July periodsequence of 123?


select max(periodseq) from period 
where periodtypeid = 'month' 
and startdate <dateadd(MONTH, datediff(MONTH, 0, current_timestamp), 0)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 03:11:44

Yes, it will work flawlessly.

CURRENT_TIMESTAMP is a function that returns exactly the same value as GETDATE().
I prefer CURRENT_TIMESTAMP because

1) Less paranthesis to keep track of
2) CURRENT_TIMESTAMP is standard across DBMS

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources