Tricky T-SQL Request 1

[JayKusch]

Does anyone have code/SP that can accept a date and return the day/number of time happened in the current month as described below:

Example:

I input the date '2003-08-25' and it returns to me that the date is the 3rd Monday of the month.

Similarly, if I were to enter the date of '2003-08-31' it would tell me that it is the 5th Sunday of the month.

May have to ask the OLAP forum this one too!

Thanks

J. Kusch

 

[dmhirsch]

How do you want the return formatted? For example, do you want a single return, "3rd Tuesday" or do you want a 2-variable return, (3, 3) to represent the 3rd 3rd (Tuesday)? I should think formatting the ordinal would be the most painstaking part of producing the first example.

 

[JayKusch]

(3,3) would do fine!

Thanks

J. Kusch

 

[sbphelps]

Here you go...

declare
@date datetime,
@num int,
@result int,
@txt2 varchar(9),
@day_num tinyint,
@txt1 char(3),
@txt3 varchar(80)

set @date = '2003-08-25'

select @num = datepart(dd, @date)
set @result = (@num / 7) + 1

select @txt1 =
case
when @result = 1 then '1st'
when @result = 2 then '2nd'
when @result = 3 then '3rd'
when @result = 4 then '4th'
when @result = 5 then '5th'
end

set datefirst 7
select @day_num = datepart(dw, @date)

select @txt2 =
case
when @day_num = 1 then 'Sunday'
when @day_num = 2 then 'Monday'
when @day_num = 3 then 'Tuesday'
when @day_num = 4 then 'Wednesday'
when @day_num = 5 then 'Thursday'
when @day_num = 6 then 'Friday'
when @day_num = 7 then 'Saturday'
end

set @txt3 = @txt1 + ' ' + @txt2

print @txt3

-- sbphelps

 

[JayKusch]

Thanks!

Thanks

J. Kusch

 

[MDXer]

I'm not sure how much of a performance increase your'llget but you can take sbhelps code and cange the bottom section which reads:

select @day_num = datepart(dw, @date)

select @txt2 =
case
when @day_num = 1 then 'Sunday'
when @day_num = 2 then 'Monday'
when @day_num = 3 then 'Tuesday'
when @day_num = 4 then 'Wednesday'
when @day_num = 5 then 'Thursday'
when @day_num = 6 then 'Friday'
when @day_num = 7 then 'Saturday'
end

Replace Blue line
Delete Red Lines

and do it as

select @day_num = datename(dw, @date)

if you don't care about the 1st, 2nd and the week number is acceptable then you can compress sb's code to the follwoing

Declare @date datetime
set @date = '5/12/2003'

Select 
(Cast(DatePart(dd,Trandate) as int)/7) + 1,
DateName(dw,trandate) as [Date] 
from dimtime
where trandate = @date

the trandate reference is the date column in my Time Dimension. I don't know what you performance requirements are but it seems that the compressed version would be faster since it doesn't have to work through the case Statements.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck