sql server 2008 - SQL query to display records with no related record within a specific date range -


sql server 2008 r2 have 2 tables. 1 main records , other notes related main common key field sysid , main_id. (there can many notes 1 main record)

i want list records in main table not have record created in notes table in last 30 days related record.

main table fields > file_number, sysid

notes table fields > note_date, main_id

i have tried hundred different suggestions searching , nothing gives me need. think below closest gives records no note exists @ , dont see main records have existing notes dont have in last 30 days. same results if take out and db.notes.[date] > dateadd(dd,-30,getdate())) line.

select *   db.main  not exists(select null        db.notes        db.main.sysid = db.notes.main_id , db.notes.[date] > dateadd(dd,-30,getdate()))  order db.main.ile_number

any suggestions appreciated.

** client has asked additional column. last note date. want list of main records no notes in x days , date of last note.

i left outer join:

select *  db.main m left outer join db.notes n on m.sysid = n.main_id      , n.[date] > dateadd(dd, -30, getdate()))  n.main_id null order m.ile_number

Comments

Post a Comment

Popular posts from this blog

asp.net mvc 3 - Using mvc3, I need to add a username/password to the sql connection string at runtime -

i see how build string using stringbuilders (sql/entity framework); do cause connection string used in dbcontext? example context: public class pophistorydbcontext : dbcontext { public dbset<pophistory> histories {get;set} } public class pophistoryrepository: ihistoryrepository { private pophistorydbcontext context = new pophistorydbcontext(); public iqueryable<pophistory> histories{ {return context.histories;} } } example usage in controller: public class historycontroller : controller { private ihistoryrepository repository = new pophistoryrepository(); var histories = h in repository.histories (h.modifytimestamp > today) select h; if (!string.isnullorempty(searchstring)) { histories = histories.where(h => h.dn.toupper().contains(searchstring.toupper())); } thank you
Read more

kineticjs - draw multiple lines and delete individual line -

i can draw multiple lines on canvas; added layer using drawscene dlayera1.add(line); line.getpoints()[0].x = mousepos.x; line.getpoints()[0].y = mousepos.y; line.getpoints()[1].x = mousepos.x; line.getpoints()[1].y = mousepos.y; moving = true; dlayera1.drawscene(); http://jsfiddle.net/user373721/xzead/1/ . is there way delete individual line? currently, you'll have modify points array this: line.getpoints()[0].splice(index, 1); since arrays modified reference, modifying returned array modifies "source of truth" points array attr
Read more

thumbnails - jQuery image rotate on hover -

any chance change image src on hover , count 10 , again first? this need, default thumb, flv-3.jpg ! <img src="/thumbs/e/b/d/6/d/ebd6d2d6d99e/ebd6d2d6d99e.flv-3.jpg" /> on mouse hover need change number flv-1.jpg flv-2.jpg flv-3.jpg flv-4.jpg etc.. up 10, , again , start number 1, when move mouse thumbs need default thumb flv-3.jpg this im use not solutions, var _thumbs = new array(); function changethumb(index, i, num_thumbs, path) { if (_thumbs[index]) { document.getelementbyid(index).src = path + + ".jpg"; preload = new image(); preload_image_id = (i + 1 > num_thumbs) ? 1 : + 1; preload.src = path + preload_image_id + ".jpg"; = % num_thumbs; i++; settimeout("changethumb('" + index + "'," + + ", " + num_thumbs + ", '" + path + "')", 800) } } function startthumbchange(index, nu
Read more