May 31, 2012 at 9:07 am
Hi,
I need help on below issue:
I need to pick the dates for last 8 Fridays.
The input will always be getdate(). Using current date I need to display the dates for Friday's for last 8 weeks.
For ex: Today is 5/31/2012. I need to display the below in columns:
4/6 4/13 4/20 4/27 5/4 5/11 5/18 5/25
The format should be mm-dd-yyyy (101)
Thanks in advance !!
May 31, 2012 at 9:14 am
Tomorrow is Friday, would you display 6/1/2012 or would you still go back 8 weeks from the previous Friday?
May 31, 2012 at 9:20 am
I would still go with the past 8 weeks.
In most cases the input will be getdate. If the user inputs any date lets say '5/30/2012' or even calculates on friday of current week it should display last 8 weeks friday dates.
May 31, 2012 at 9:40 am
DECLARE @dt DATETIME
SET @dt = GETDATE()
;WITH rns56
AS
(
SELECT TOP 56 (ROW_NUMBER() OVER (ORDER BY c) - 1) * -1 rn
FROM (SELECT 1 c FROM (VALUES (1),(2),(3),(4))q1(c)
,(VALUES (1),(2),(3),(4))q2(c)
,(VALUES (1),(2),(3),(4))q3(c)) a
)
, fri8
AS
(
SELECT 'Friday ' + cast((ROW_NUMBER() OVER (ORDER BY rn)) AS VARCHAR) AS Friday
, CONVERT(VARCHAR,DATEADD(DAY,rn,@dt),101) AS DayVal
FROM rns56
WHERE DATENAME(WEEKDAY,DATEADD(DAY,rn,@dt)) = 'Friday'
)
SELECT [Friday 1], [Friday 2], [Friday 3], [Friday 4], [Friday 5], [Friday 6], [Friday 7], [Friday 8]
FROM fri8
PIVOT
(
MAX(DayVal)
FOR Friday IN ([Friday 1], [Friday 2], [Friday 3], [Friday 4], [Friday 5], [Friday 6], [Friday 7], [Friday 8])
) P
May 31, 2012 at 9:56 am
Thanks for the reply. I got the correct result. But can u explain a little bit. what approach did u follow?
Specially this part:
;WITH rns56
AS
(
SELECT TOP 56 (ROW_NUMBER() OVER (ORDER BY c) - 1) * -1 rn
FROM (SELECT 1 c FROM (VALUES (1),(2),(3),(4))q1(c)
,(VALUES (1),(2),(3),(4))q2(c)
,(VALUES (1),(2),(3),(4))q3(c)) a
)
What is rns56?
May 31, 2012 at 10:05 am
I need 8 weeks * 7 days numbers (56), so I can go back up to 56 days (8 weeks) and find last 8 fridays.
This one can be replaced with anything which can return 56 rows eg:
;WITH rns56
AS
(
SELECT TOP 56 (ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) * -1 rn
FROM sys.objects
)
...
rns56 is just a name I gave to CTE expression (;with...) You can name it as you wish 🙂
May 31, 2012 at 10:12 am
Eugene Elutin (5/31/2012)
I need 8 weeks * 7 days numbers (56), so I can go back up to 56 days (8 weeks) and find last 8 fridays.This one can be replaced with anything which can return 56 rows eg:
;WITH rns56
AS
(
SELECT TOP 56 (ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) * -1 rn
FROM sys.objects
)
...
rns56 is just a name I gave to CTE expression (;with...) You can name it as you wish 🙂
Slightly different version but gets the same results:
WITH
e8(n) AS (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * -1
FROM (SELECT 1 n UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
) dt
),
wks(Friday, CalendarDate) AS (
SELECT
'Friday' + CAST(ROW_NUMBER() OVER (ORDER BY n) AS VARCHAR),
CONVERT(VARCHAR(10),DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -3)),101) FROM e8
)
SELECT [Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8]
FROM wks
PIVOT
(
MAX(CalendarDate)
FOR Friday IN ([Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8])
) P;
May 31, 2012 at 10:13 am
Thanks a lot!!
May 31, 2012 at 10:29 am
Lynn Pettis (5/31/2012)
Eugene Elutin (5/31/2012)
I need 8 weeks * 7 days numbers (56), so I can go back up to 56 days (8 weeks) and find last 8 fridays.This one can be replaced with anything which can return 56 rows eg:
;WITH rns56
AS
(
SELECT TOP 56 (ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) * -1 rn
FROM sys.objects
)
...
rns56 is just a name I gave to CTE expression (;with...) You can name it as you wish 🙂
Slightly different version but gets the same results:
WITH
e8(n) AS (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * -1
FROM (SELECT 1 n UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
) dt
),
wks(Friday, CalendarDate) AS (
SELECT
'Friday' + CAST(ROW_NUMBER() OVER (ORDER BY n) AS VARCHAR),
CONVERT(VARCHAR(10),DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -3)),101) FROM e8
)
SELECT [Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8]
FROM wks
PIVOT
(
MAX(CalendarDate)
FOR Friday IN ([Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8])
) P;
Slight modification so that on Saturday it will end with the previous Friday (on 6/2/2012 it will go to 6/1/2012).
WITH
e8(n) AS (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * -1
FROM (SELECT 1 n UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
) dt
),
wks(Friday, CalendarDate) AS (
SELECT
'Friday' + CAST(ROW_NUMBER() OVER (ORDER BY n) AS VARCHAR),
CONVERT(VARCHAR(10),DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0, dateadd(dd, 1, GETDATE())), -3)),101) FROM e8
)
SELECT [Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8]
FROM wks
PIVOT
(
MAX(CalendarDate)
FOR Friday IN ([Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8])
) P;
May 31, 2012 at 10:41 am
Thats a good answer. It will help lot. Thankyou 🙂
May 31, 2012 at 12:30 pm
How can I store the values from this result set in a temp table?
I created below table:
create table #tempfriday(datevalues datetime)
Now I am trying to declare 8 variables and and put the values in those or set some counter and do this.
May 31, 2012 at 12:33 pm
I am trying the values for the result set in a temp table.
I created the below temp table:
create table #temp (datevalues datetime)
May 31, 2012 at 12:37 pm
Nidhi G (5/31/2012)
I am trying the values for the result set in a temp table.I created the below temp table:
create table #temp (datevalues datetime)
WITH
e8(n) AS (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * -1
FROM (SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
) dt(n)
),
wks(Friday, CalendarDate) AS (
SELECT
'Friday' + CAST(ROW_NUMBER() OVER (ORDER BY n) AS VARCHAR),
-- CONVERT(VARCHAR(10),DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0,DATEADD(dd, 1, GETDATE())), -3)),101) FROM e8
DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0,DATEADD(dd, 1, GETDATE())), -3)) FROM e8
)
INSERT INTO #temp(datevalues)
SELECT CalendarDate FROM wks;
May 31, 2012 at 12:42 pm
Nidhi G (5/31/2012)
I am trying the values for the result set in a temp table.I created the below temp table:
create table #temp (datevalues datetime)
To put the dates into 8 variables.
DECLARE @Date1 DATETIME,
@Date2 DATETIME,
@Date3 DATETIME,
@Date4 DATETIME,
@Date5 DATETIME,
@Date6 DATETIME,
@Date7 DATETIME,
@Date8 DATETIME;
WITH
e8(n) AS (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * -1
FROM (SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
) dt(n)
),
wks(Friday, CalendarDate) AS (
SELECT
'Friday' + CAST(ROW_NUMBER() OVER (ORDER BY n) AS VARCHAR),
-- CONVERT(VARCHAR(10),DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0,DATEADD(dd, 1, GETDATE())), -3)),101) FROM e8
DATEADD(wk, n, DATEADD(wk, DATEDIFF(wk, 0,DATEADD(dd, 1, GETDATE())), -3)) FROM e8
)
--INSERT INTO #tempfriday(datevalues)
--SELECT CalendarDate FROM wks;
SELECT
@Date1 = [Friday1],
@Date2 = [Friday2],
@Date3 = [Friday3],
@Date4 = [Friday4],
@Date5 = [Friday5],
@Date6 = [Friday6],
@Date7 = [Friday7],
@Date8 = [Friday8]
FROM wks
PIVOT
(
MAX(CalendarDate)
FOR Friday IN ([Friday1], [Friday2], [Friday3], [Friday4], [Friday5], [Friday6], [Friday7], [Friday8])
) P;
SELECT @Date1, @Date2, @Date3, @Date4, @Date5, @Date6, @Date7, @Date8;
May 31, 2012 at 12:45 pm
This is not working.. Did u make any change to code?
What if we declare 8 variables and then put the values in those. That will be good for me.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply