SQL Query to find previous records

0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/6949672/sql-query-to-find-previous-records

Joomla Meta

Q&A about the site for Joomla! administrators, users, developers and designers

-- SQL Query to find previous records
-- http://stackoverflow.com/questions/6949672/sql-query-to-find-previous-records

declare @aTable table (TranDate datetime, Location  varchar(50), BadRec int)
insert @aTable 
select '2010-08-25 00:00:00.000',  'STN-2',     0
union all select'2010-08-26 00:00:00.000',  'STN-2',     1
union all select'2010-08-27 00:00:00.000',  'STN-1',     1
union all select'2010-08-28 00:00:00.000',  'STN-1',     0
union all select'2010-08-28 00:00:00.000',  'STN-2',     1
union all select'2010-08-29 00:00:00.000',  'STN-1',     0
union all select'2010-08-30 00:00:00.000',  'STN-1',     1
union all select'2010-08-31 00:00:00.000',  'STN-1',     0
union all select'2010-09-01 00:00:00.000',  'STN-1',     0

SELECT TranDate, Location, BadRec
FROM
    (
    SELECT pr.TranDate, pr.Location, pr.BadRec,
        ROW_NUMBER() OVER (PARTITION BY r.TranDate ORDER BY pr.TranDate DESC) AS SEQUENCE,
        ROW_NUMBER() OVER (ORDER BY r.TranDate DESC) as RankOrder
    FROM @aTable r
    JOIN @aTable pr ON r.Location = pr.Location
                    AND r.TranDate >= pr.TranDate
    WHERE r.BadRec = 1
    ) x
WHERE SEQUENCE <= 4
ORDER BY RankOrder

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results