June 10, 2012 at 5:07 pm
Hi
I need a sql that takes the input as date from user and calculates the no of weeks for last two months and then display every friday/saturday of those months. Below is exact thing that is required:
Input will be either getdate() or a user entered.
If Input is provided by user use that date else use getdate()
Lets say user provides 5/18/2012
Now, as the date provided is in May (5th month), I need to calculate the weeks in March and april and then display the fridays of those weeks.
So according to the input provided the output should be:
3/2/2012 3/9/2012 3/16/2012 3/23/2012 3/30/2012 4/6/2012 4/13/2012 4/20/2012 4/27/2012
June 10, 2012 at 6:09 pm
How about this?
DECLARE @UserInputDate DATE
,@DateMinus2Months DATE
,@NumOfDays INT
,@SQLCaseStmt VARCHAR(MAX) = ''
,@FinalSelect VARCHAR(MAX) = ''
;
SELECT @UserInputDate = '5/18/2012'
;
SELECT @UserInputDate = ISNULL ( @UserInputDate , DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0))
;
SELECT @UserInputDate = DATEADD(MM,DATEDIFF(MM,0,@UserInputDate),0)
;
SELECT @DateMinus2Months = DATEADD(MM, -2, @UserInputDate)
;
SELECT @NumOfDays = DATEDIFF (DD ,@DateMinus2Months ,@UserInputDate )
;
IF OBJECT_ID('tempdb..#OnlyFridays') IS NOT NULL
DROP TABLE #OnlyFridays;
;WITH DateTable (Dates) AS
(
SELECT TOP (@NumOfDays) Dates = DATEADD(DD , ROW_NUMBER() OVER (ORDER BY (SELECT 0))-1, @DateMinus2Months)
FROM sys.columns T1
CROSS JOIN sys.columns T2
)
SELECT RN = ROW_NUMBER() OVER (ORDER BY Dates)
,Dates
,NameOfDay = DATENAME(WEEKDAY , Dates)
INTO #OnlyFridays
FROM DateTable
WHERE DATENAME(WEEKDAY , Dates) IN ('Friday')
;
SELECT @SQLCaseStmt = ', SUM( CASE WHEN Dates = ''' + CONVERT(VARCHAR,ofr.Dates,101) + ''' THEN 0 ELSE 0 END ) AS ' + QUOTENAME(CONVERT(VARCHAR,ofr.Dates,101)) + @SQLCaseStmt
FROM #OnlyFridays ofr
ORDER BY ofr.RN DESC
;
SELECT @FinalSelect = 'SELECT ' + STUFF (@SQLCaseStmt ,1,1,'' ) + ' FROM #OnlyFridays'
;
--SELECT @FinalSelect
EXEC (@FinalSelect)
;
{Edit : fixed bugs}
June 10, 2012 at 6:36 pm
How about something like this?
DECLARE @InputDate DATETIME
,@DesiredDay VARCHAR(9)
SET @InputDate = '2012-05-18'
SET @DesiredDay = 'Friday'
;WITH Tally AS (
SELECT TOP 65
Dates=DATEADD(day
,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,DATEADD(month, DATEDIFF(month, 0, @InputDate), 0))
FROM sys.all_columns
)
SELECT Dates
FROM Tally
WHERE @DesiredDay = DATENAME(weekday, Dates) AND
DATEPART(month, @InputDate) - 2 <= DATEPART(month, Dates)
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 10, 2012 at 6:45 pm
OP needs it to be pivoted, Dwain 🙂
June 10, 2012 at 7:35 pm
ColdCoffee (6/10/2012)
OP needs it to be pivoted, Dwain 🙂
I didn't interpret it that way but you may be right.
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 10, 2012 at 11:37 pm
select A.[Date] as Friday
from
(
select top 200
DATEADD(DD,
-ROW_NUMBER ()over (order by (select null)),
GETDATE()) as [Date]
from sys.columns
)A
where 'Friday'=datename (weekday,[Date])
and month([Date]) in (
select MONTH(dateadd(month,-1, getdate()))
union
select MONTH(dateadd(month,-2, getdate()))
)
June 11, 2012 at 7:52 am
yes it needs to be pivoted and show the dates in reverse order.. I mean beginning from the first month and first friday..
June 11, 2012 at 9:06 am
SELECT [Output] = STUFF(
(SELECT ', ' + CONVERT(VARCHAR(10),DATEADD(dd,0-(7*n),LastFridayOfThisMonth),101)
FROM (
SELECT
FirstDayOfFirstMonth,
LastFridayOfThisMonth = DATEADD(dd,0-(DATEDIFF(dd,-3,x.LastDayOfLastMonth)%7),x.LastDayOfLastMonth)
FROM (
SELECT
FirstDayOfFirstMonth = DATEADD(month,DATEDIFF(month,0,GETDATE())-2,-0), -- April
LastDayOfLastMonth = DATEADD(month,DATEDIFF(month,0,GETDATE())+0,-1) -- May
) x
) y
CROSS JOIN (VALUES (0), (1),(2),(3),(4),(5),(6),(7),(8),(9)) d (n)
WHERE FirstDayOfFirstMonth < DATEADD(dd,0-(7*n),LastFridayOfThisMonth)
ORDER BY d.n DESC
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,2,'')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2012 at 11:02 am
This gives the perfect result. how can I store the result set in a temp table. I want to store these values in temp table and use that temp table in my function.
June 12, 2012 at 1:40 am
nehaCS (6/11/2012)
This gives the perfect result. how can I store the result set in a temp table. I want to store these values in temp table and use that temp table in my function.
What does the function do with the comma-delimited string output by my query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply