Prev: Copying Information
Next: How do I get scalar value of a child stored procedure?
From: Cismail via SQLMonster.com on 1 Mar 2010 13:35
Hello,

I have a calender table that contains the field week_number numeric(6,0).

I would like to know if there is a way to retrieve the previous week for
every week that is defined in the calender table without using the CROSS
APPLY command.

Thank you for your help.

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

From: Plamen Ratchev on 1 Mar 2010 16:52
It is not clear in what format you store the weeks, but assuming it is YYYYWW, then something like this will do:

WITH Ranked AS (
SELECT week_number, ROW_NUMBER() OVER(ORDER BY week_number) AS rk
FROM Calendar)
SELECT A.week_number, B.week_number
FROM Ranked AS A
LEFT OUTER JOIN Ranked AS B
ON A.rk = B.rk + 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 2 Mar 2010 14:47
>> I have a calender table that contains the field [sic: columns are not fields] week_number numeric(6,0). <<

It is impossible to write DML without DDL. Plamen made one guess about
your data. My guess is that it is Julianized week_number rather than
an ISO-8601 week date. The resason for my guess is that if you were
using the ISO format, it should be declared as CHAR(6) NOT NULL CHECK
(iso_week_nbr LIKE '[1-2][0-9][0-9][0-9][0-5][0-9]'). You only use
Numeric data types for computations, not names and tags.

A Julianzed week number would be a count of weeks over all the years
in the Calendar table. A 100 year calendar table will have only 36524
rows in it.
From: Cismail via SQLMonster.com on 2 Mar 2010 15:04
Hello,

Sorry for not being clearer and yes, the week format is indeed YYYYWW.
Thank you once again. Really appreciate your help!

Plamen Ratchev wrote:
>It is not clear in what format you store the weeks, but assuming it is YYYYWW, then something like this will do:
>
>WITH Ranked AS (
>SELECT week_number, ROW_NUMBER() OVER(ORDER BY week_number) AS rk
>FROM Calendar)
>SELECT A.week_number, B.week_number
>FROM Ranked AS A
>LEFT OUTER JOIN Ranked AS B
> ON A.rk = B.rk + 1;
>

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

 | 
Pages: 1
Prev: Copying Information
Next: How do I get scalar value of a child stored procedure?