June 7, 2012 at 6:09 pm
How do you get the date for every Friday in a particular quarter?
June 7, 2012 at 6:41 pm
This will do it for you.
declare @qtr int, @year int,@Startdate datetime
select @qtr=2,@year=2012
select @startdate=dateadd(month,(@Qtr-1)*3,dateadd(year,@year-1900,0)) --0 is 1/1/1900 which is a monday
;with TallyCTE as (
select row_number() over (order by (select null))-1 RN from sys.columns)
select dateadd(day,rn,@startdate) Friday
from tallycte
where cast(dateadd(day,rn,@startdate) as int)%7 = 4 and --0 is 1/1/1900 which is a monday
dateadd(day,rn,@startdate) between @Startdate and dateadd(month,3,@Startdate)-1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 7, 2012 at 7:02 pm
Perhaps a little more concisely:
DECLARE @qtr INT, @year VARCHAR(4)
SELECT @qtr=2, @year='2012'
;WITH Tally AS (SELECT TOP 366 n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns)
SELECT Fridays=AllDays
FROM Tally
CROSS APPLY (SELECT DATEADD(day, n, @year)) x(AllDays)
WHERE DATEPART(quarter, AllDays) = @qtr AND DATENAME(weekday, AllDays) = 'Friday' AND
DATEPART(year, AllDays) = @year
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 8, 2012 at 5:21 am
Thank you 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply