Calculate the least Quarter amongst a list of Quarters

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

Old Forums

CLOSED - General SQL Server

Calculate the least Quarter amongst a list of Quarters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-30 : 07:07:03

ajay writes "Hi,

I get a list of Quarters as input parameter to my procedure.

Ex:'Q106/Q306/Q405/Q305/Q107' etc...

I need the find out which one is the least Quarter amongst all the Quarters.I may get 1 or 2 or 3 or any number of Quarters in the string...

The lowest Quarter is 'Q305' in the above example...

Any help with implementing this logic is greatly appreicated.

This is a very urgent requirement for me.

Thanks
Ajay"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 07:08:55

what is your definition of lowest Quarter ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 07:13:15

Earliest.
Make use of a CSV split function.

Peter Larsson
Helsingborg, Sweden

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-10-30 : 09:28:13

Just for fun...


DECLARE @quarter VARCHAR(100)
SET @quarter = 'Q106/Q306/Q405/Q305/Q107' 

SELECT TOP 1 x.Quartal
 FROM 
 (SELECT
  RIGHT(LEFT(@quarter,Number-1), CHARINDEX('/',REVERSE(LEFT('/'+@quarter,Number-1)))) AS Quartal
   FROM master..spt_values
  WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(@quarter)+1
    AND (SUBSTRING(@quarter,Number,1) = '/' OR SUBSTRING(@quarter,Number,1)  = '')) x
 ORDER BY ('20'+ RIGHT(x.Quartal,2)+LEFT(REPLACE(x.Quartal, 'Q', ''),1) ) 

Quartal            
------------------- 
Q305

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 09:30:53

quote:
Originally posted by Peso

Earliest.
Make use of a CSV split function.

Peter Larsson
Helsingborg, Sweden

Q106 is 2006 Q1
Q305 is 2005 Q3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 09:35:16

[code]
select top 1 stringval
from CSVTable('Q106,Q306,Q405,Q305,Q107')
order by right(stringval, 2) + left(stringval, 2)
[/code]

using CSVTable from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
can be easily modify from comman ',' separated to '/' sperated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources