sql server 2012 - SQL Find latest record only if COMPLETE field is 0 -

i have table multiple records submitted user. in each record field called complete indicate if record completed or not.

i need way latest records of user complete 0, location, date same , no additional record exist complete 1. in each record there additional fields such type, amount, total, etc. these can different, though user, location, , date same.

there sub_date field , id field denote day submission made , auto incremented id number. here table:

id  name  location  date       complete  sub_date   type1  amount1  type2  amount2  total 1   user1   loc1    2017-09-15    1      2017-09-10  food   12.25   hotel  65.54   77.79 2   user1   loc1    2017-09-15    0      2017-09-11  food   12.25   null      0   12.25 3   user1   loc2    2017-08-13    0      2017-09-05  flight 140     food      5   145.00 4   user1   loc2    2017-08-13    0      2017-09-10  flight 140     null      0   140 5   user1   loc3    2017-07-14    0      2017-07-15  taxi   25      null      0   25 6   user1   loc3    2017-08-25    1      2017-08-26  food   45      null      0   45 

the results retrieve id 4, because sub_date later id 3. has same name, location, , date information , there no complete 1 value.

i retrieve id 5, since latest record user, location, date, , complete 0.

i appreciate if explain answer me understand happening in solution.

not sure if understood try this

select  *    (             select *,                     max(convert(int,complete)) on (partition name,location,date) completefornamelocationanddate,                     max(sub_date) on (partition name, location, date) lastsubdate                your_table t         )   completefornamelocationanddate = 0 ,         sub_date = lastsubdate 

so have done here:

first, if run inner query in management studio, see does:

the first max function partition data in table each unique name,location,date set. in case of data, id 1 & 2 first partition, 3&4 second partition, 5 3rd partition , 6 4th partition. each of these partitions max value in complete column. therefore partition 1 it's max value has been completed.

note also, convert function. because complete of datatype bit (1 or 0) , max function not work datatype. therefore convert int. if complete column type int, can take convert out.

the second max function partitions unique name, location , date again getting max_sub date time give date of latest record name,location,date

so take query , add derived table simplicity call a. need because sql server doesn't allowed windowed functions in clause of queries. windowed function 1 makes use of on keyword have done. in ideal world, sql let

select *,        max(convert(int,complete)) on (partition name,location,date) completefornamelocationanddate,        max(sub_date) on (partition name, location, date) lastsubdate   your)table t   max(convert(int,complete)) on (partition name,location,date) = 0 ,         sub_date = max(sub_date) on (partition name, location, date) 

but doesn't allow have use derived table.

so select our derived table

completefornamelocationanddate = 0 

which name,location, date partitions not have record marked complete.

then filter further asking latest record each partition

sub_date = lastsubdate 

hope makes sense, not sure level of detail need?

as side, @ restructuring tables (unless of course have simplified better explain problem) follows:

(assuming table in examples called booking)

tblbooking bookingid personid locationid date complete subdate  tblperson personid personname  tbllocation locationid locationname  tbltype typeid typename  tblbookingtype bookingtypeid bookingid typeid amount 

this way if ever want add type3 or type4 booking information, don't need alter table layout