declare @aTable table (TranDate datetime, Location varch...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla Meta

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

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
    FROM @aTable r
    JOIN @aTable pr ON r.Location = pr.Location
                    AND r.TranDate >= pr.TranDate
    WHERE r.BadRec = 1
    ) x
WHERE SEQUENCE <= 4

Enter Parameters

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