Prev: Place a message in the outbox of Outlook
Next: perfmon counter
From: Cismail via SQLMonster.com on 23 Feb 2010 11:27
Hello,

I'm trying to create an SQL statement that will retrieve the previous 52
weeks for every week defined in my calender weeks table.

Let's say the calender_weeks table contains 1 field called week_no - numeric
(6,0).

The following SQL statement works fine for a given week. I would like to know
if it's possible and how to get the same results for every week defined in my
calender_weeks table. In other words, how can I make this work without
specifying a specific week??

SELECT TOP(52) a.week_no as week, b.week_no as rolling_week
FROM calender_weeks a, calender_weeks b
WHERE b.week_no<=a.week_no
AND a.week_no=200901
ORDER BY a.week_no, b.week_no DESC

Thank you for your help.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1

From: Plamen Ratchev on 23 Feb 2010 13:04
Here is one solution:

SELECT a.week_no AS [week], c.week_no AS rolling_week
FROM calender_weeks AS a
CROSS APPLY(SELECT TOP(52) week_no
FROM calender_weeks AS b
WHERE b.week_no <= a.week_no
ORDER BY b.week_no DESC) AS C(week_no)
ORDER BY a.week_no, c.week_no;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Cismail via SQLMonster.com on 23 Feb 2010 13:22
Works like a charm!
Thank you so much.

Plamen Ratchev wrote:
>Here is one solution:
>
>SELECT a.week_no AS [week], c.week_no AS rolling_week
>FROM calender_weeks AS a
>CROSS APPLY(SELECT TOP(52) week_no
> FROM calender_weeks AS b
> WHERE b.week_no <= a.week_no
> ORDER BY b.week_no DESC) AS C(week_no)
>ORDER BY a.week_no, c.week_no;
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1

 | 
Pages: 1
Prev: Place a message in the outbox of Outlook
Next: perfmon counter