how to calculate the least anmongst a list of Quarters

Hi,

i get a list of Quarters that look like following as an input to my proc.

'Q306/Q106/Q406/Q405'

I need to find out whihc one of the above is the least Quarter value.

In the above example it is 'Q405'

The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...

Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...

Can some one please help on this....

Hello,

There is no "quarter" datatype in sql server.

For your example, the parameter string will need to first be parsed (to split the quarters - you can use a recursive cte for this) then ordered. If you are using SQL 2005, you can also roll your own "quarter" datatype that could include ranking functions such as MIN/MAX etc.

What version of sql are you using?

Cheers

Rob

|||

Thanks for the reply...

I am using SQL Server2000. Can you please be more elaborate on this as I am a novice.what exactly is recursive cte...

Can you please send some sample code if possible...

Thanks...

|||

This function will help split up string with a delimited character. It was written in SQL 2005, but you should be able to change MAX to a length less than 8000 to make it work.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[SplitData] (@.Data VARCHAR(MAX), @.delimeter CHAR(1))

RETURNS @.v_Values TABLE ([Value] VARCHAR(MAX), [Position] BIGINT IDENTITY(1,1))

AS

BEGIN

DECLARE @.v_value VARCHAR(MAX),

@.v_pos BIGINT

SET @.v_Pos = 1

WHILE @.data > '' AND @.v_Pos > 0

BEGIN

SELECT @.v_pos = CHARINDEX(@.delimeter, @.data,1),

@.v_Value = CASE @.v_Pos

WHEN 0 THEN

@.data

ELSE

LEFT(@.data, @.v_pos-1)

END,

@.data = CASE

WHEN @.v_Value = @.data THEN

NULL

ELSE

SUBSTRING(@.data, @.v_pos + 1, LEN(@.data))

END

INSERT INTO @.v_Values ([Value])

VALUES (@.v_Value)

END

return

END

|||

I couldnt really understand how to use the above function...

Is it possible to get the seperate values by splitting the following data

'Q106/Q206/Q306 Version1/Q406 Version2/Current'

I need to be able to spilt the above Data...the delimiter is '/'...

is there something like a spilt function in Sql Server2000....

Please get back.

Thanks

Swapna

|||

The split function is the function I posted. The first parameter is the value and the second parameter is the delimiter.

SELECT Value

FROM dbo.SplitData('Q106/Q206/Q306 Version1/Q406 Version2/Current','/')

Hope this helps...

|||

Here is the version for 2000.

Run it with this:

SELECT * FROM dbo.SplitData('Q106/Q206/Q306 Version1/Q406 Version2/Current','/')

/*

Results:

Value Position

Q106 1

Q206 2

Q306 Version1 3

Q406 Version2 4

Current 5

*/

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[SplitData] (@.Data VARCHAR(8000), @.delimeter CHAR(1))

RETURNS @.v_Values TABLE ([Value] VARCHAR(1000), [Position] BIGINT IDENTITY(1,1))

AS

BEGIN

DECLARE @.v_value VARCHAR(1000),

@.v_pos BIGINT

SET @.v_Pos = 1

WHILE @.data > '' AND @.v_Pos > 0

BEGIN

SELECT @.v_pos = CHARINDEX(@.delimeter, @.data,1),

@.v_Value = CASE @.v_Pos

WHEN 0 THEN

@.data

ELSE

LEFT(@.data, @.v_pos-1)

END,

@.data = CASE

WHEN @.v_Value = @.data THEN

NULL

ELSE

SUBSTRING(@.data, @.v_pos + 1, LEN(@.data))

END

INSERT INTO @.v_Values ([Value])

VALUES (@.v_Value)

END

return

END

GO

|||

Thanks a lot....this almost saved my project...

Thanks