Selecting shifted date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting shifted date

Author

Topic

dkekesi
Starting Member

38 Posts

Posted - 2010-09-01 : 03:41:05

Hi,

I'd like to ask your help with a query.
I have a table with two columns. The first column is a date stored as string in the format of YYYY.MM.DD (do not ask my why it is a string, but it is so and the data owners don't want to change that), the second column is a single character (it is either "W" or "N"). Data in the date column is unique (it's like a calendar).
I need to write a query or a stored procedure (query is more preferred) which has two parameters: a date in string format of YYYY.MM.DD and an integer which can be -1, 0, +1, +2. What the query should do is to return a single record in which the second column is "W" and the date provided in the first parameter is shifted with the amount of days provided in the second parameter.
An example. Say we have the following table:
DATE ;CHAR
2010.09.01;W
2010.09.02;N
2010.09.03;W
2010.09.04;N
2010.09.05;W

If I call the procedure/query with the following parameters:
"2010.09.05" , -1
it should return the 3rd row (2010.09.03;W)

If I call the procedure/query with the parameters
"2010.09.05" , -2
it should return the 1st row (2010.09.01;W)

How may I accomplish this task?

Thanks for your help in advance.

Best Regards,
Daniel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 04:31:20

[code]DECLARE @Sample TABLE
(
dt DATE NOT NULL,
chr CHAR(1) NOT NULL
)

INSERt @Sample
VALUES ('20100901', 'W'),
('20100902', 'N'),
('20100903', 'W'),
('20100904', 'N'),
('20100905', 'W')

-- Prepare user supplied parameters
DECLARE @Date DATE = '20100905',
@Offset INT = -1

SELECT f.dt,
f.chr
FROM (
SELECT dt,
chr
FROM @Sample
WHERE dt = @Date
) AS s
CROSS APPLY (
SELECT x.dt,
x.chr,
ROW_NUMBER() OVER (ORDER BY x.dt DESC) AS RecID
FROM @Sample AS x
WHERE x.chr = s.chr
AND x.dt < s.dt
) AS f(dt, chr, RecID)
WHERE f.RecID = ABS(@Offset)[/code]

N 56°04'39.26"
E 12°55'05.63"

dkekesi
Starting Member

38 Posts

Posted - 2010-09-01 : 05:45:29

Peso, this is exactly what I need.
Thanks a lot for your help!

Best Regards,
Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources