I’m using SQL Server.
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)