Why is this SQL statement not retrieving DISTINCT records -

Why is this SQL statement not retrieving DISTINCT records -

i have 2 tables, jobs , adminuserslog. adminuserslog contains log of actions performed on each job record in jobs.

i trying retrieve list of jobs have been idle longer 7 days. sql below seems pulling out log entries each job , hence pulling out older entries log of course of study older 7 days.

i need retrieve 1 record each job adminuserslog.uldate older 7 days.

select distinct jobs.id, jobs.bstatus jstatus, jobs.sid, jobs.insdate, jobs.statuslabel, jobs.cid, adminuserslog.uldate, adminuserslog.uljobtype adminuserslog left bring together jobs on (adminuserslog.uljobid = jobs.id , adminuserslog.uljobtype = 1) adminuserslog.uljobtype = 1 , (select top(1) adminuserslog.uldate adminuserslog adminuserslog.uljobid = jobs.id , adminuserslog.uljobtype = 1) < dateadd(dd,-7,getdate()) , jobs.bstatus < 8

top x results; http://pastebin.com/rcvxbxcv

you set uldate column

as

select convert(date,uldate) uldate

on select field

select cast('2014-01-23 15:23:42.093' date)

beause uldate field not distinct

query set as

select distinct jobs.id, jobs.bstatus jstatus, jobs.sid, jobs.insdate, jobs.statuslabel, jobs.cid, convert(date,adminuserslog.uldate) uldate, adminuserslog.uljobtype adminuserslog left bring together jobs on (adminuserslog.uljobid = jobs.id , adminuserslog.uljobtype = 1) adminuserslog.uljobtype = 1 , (select top(1) adminuserslog.uldate adminuserslog adminuserslog.uljobid = jobs.id , adminuserslog.uljobtype = 1) < dateadd(dd,-7,getdate()) , jobs.bstatus < 8

sql