May 31, 2012 at 12:49 pm
CREATE TABLE #temp
(
[Friday1] CHAR(10) ,
[Friday2] CHAR(10) ,
[Friday3] CHAR(10) ,
[Friday4] CHAR(10) ,
[Friday5] CHAR(10) ,
[Friday6] CHAR(10) ,
[Friday7] CHAR(10) ,
[Friday8] CHAR(10)
)
GO
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
)
INSERT INTO #temp
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;
SELECT *
FROM #temp
May 31, 2012 at 12:50 pm
Nidhi G (5/31/2012)
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.
Sorry, but "This is not working" doesn't work for me. Yes, I made a change and it should be self evident looking at the code. Second, I ran the code myself on my system here, and it works just fine loading a table or updating variables (which is slightly different from the table version).
May 31, 2012 at 12:52 pm
andersg98 (5/31/2012)
CREATE TABLE #temp
(
[Friday1] CHAR(10) ,
[Friday2] CHAR(10) ,
[Friday3] CHAR(10) ,
[Friday4] CHAR(10) ,
[Friday5] CHAR(10) ,
[Friday6] CHAR(10) ,
[Friday7] CHAR(10) ,
[Friday8] CHAR(10)
)
GO
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
)
INSERT INTO #temp
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;
SELECT *
FROM #temp
The temporary table you defined above is not the same as the one you defined in the earlier post. The earlier post had a single column defined.
May 31, 2012 at 12:54 pm
andersg98 (5/31/2012)
CREATE TABLE #temp
(
[Friday1] CHAR(10) ,
[Friday2] CHAR(10) ,
[Friday3] CHAR(10) ,
[Friday4] CHAR(10) ,
[Friday5] CHAR(10) ,
[Friday6] CHAR(10) ,
[Friday7] CHAR(10) ,
[Friday8] CHAR(10)
)
GO
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
)
INSERT INTO #temp
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;
SELECT *
FROM #temp
Ran this code and it worked just fine, by the way.
May 31, 2012 at 12:58 pm
Sorry, just couldn't copy and paste as fast as Lynn. 🙂
May 31, 2012 at 1:02 pm
How about something like this?:
SELECT
/*@date1 = */ last_friday,
/*@date2 = */ DATEADD(DAY, -07, last_friday),
/*@date3 = */ DATEADD(DAY, -14, last_friday),
/*@date4 = */ DATEADD(DAY, -21, last_friday),
/*@date5 = */ DATEADD(DAY, -28, last_friday),
/*@date6 = */ DATEADD(DAY, -35, last_friday),
/*@date7 = */ DATEADD(DAY, -42, last_friday),
/*@date8 = */ DATEADD(DAY, -49, last_friday)
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000105', DATEADD(DAY, -2, GETDATE())) / 7 * 7, '19000105') AS last_friday
) AS last_friday
Naturally just uncomment the "@date = code" to assign to variables.
This calc:
DATEADD(DAY, -2, GETDATE())
may need adjusted, based on what you want to do.
For example, that calc means that for Sat, you would go the Fri before (on Jun 2, the last_friday would be May 25, not Jun 1).
If you want Sat to get the day before as the last_friday (on Jun 2, the last_friday should be Jun 1), change -2 to -1.
If you want through, say, Mon, to skip/ignore the last Fri (on Jun 4, you still the last_friday to be May 25), change -2 to -4.
etc.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 31, 2012 at 1:03 pm
Really sorry.. It was mistake at my side. Bt ur replying speed was much faster than fixing my mistake here.. It is working without any issues.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply