I need to return a result set which only shows rows that have the latest date, presently I’m getting duplicate rows that have different pl.end dates (which is correct based on my script) however I need to exclude the row with the earlier pl.end date. Any ideas? Many thanks in advance.
Here is my script:
select
sqe.user,
(select name from table3 where userid = pl.made_by),
pl.firstname+' '+pl.lastname,
pl.host,
pl.start,
pl.end,
'',
pl.pid
from
table1 pl,
table2 sqe
where
pl.shown_by = sqe.userid
and pl.host_id not in (316, 51310, 51333)
and pl.pid > 0
and (pl.end >= '2015-05-01 00:00:00:00' and pl.end <= '2015-05-31 23:59:59:59')
and pl.type = 0
and pl.auth = 1
and pl.dept = 'DEPTXXXX'
and pl.parid not in
(select pl2.parid from table4 pl2 where pl2.start >= '2015-05-01 00:00:00:00' and pl2.end > '2015-05-31 23:59:59:59' and pl2.auth = 1)
select sqe.user
, ( select name
from table3
where userid = xx.made_by ) AS table3name
, xx.firstname+' '+xx.lastname AS fullname
, xx.host
, xx.start
, xx.end
, '' AS empty_string
, xx.pid
from table2 AS sqe
INNER
JOIN ( SELECT pl.firstname
, pl.lastname
, pl.host
, pl.start
, pl.end
, pl.pid
, pl.made_by
, pl.shown_by
, ROW_NUMBER() OVER
( PARTITION BY shown_by
ORDER BY end DESC ) AS rownum
FROM table1 AS pl
WHERE pl.end >= '2015-05-01'
and pl.end < '2015-06-01'
and pl.host_id not in (316, 51310, 51333)
and pl.pid > 0
and pl.type = 0
and pl.auth = 1
and pl.dept = 'DEPTXXXX'
and pl.parid not in
( select pl2.parid
from table4 pl2
where pl2.start >= '2015-05-01'
and pl2.end >= '2015-06-01'
and pl2.auth = 1 )
) AS xx
ON xx.shown_by = sqe.userid
AND xx.rownum = 1
