sql server 2005 - SQL Update most recent in table instead of most recent on selected record -


I have to update the records of those members who have not joined the previous 14 days. There are two tables: Members and presence. If Attend_Freq is less than 5 in the last 14 days, then most recently I have to update the member record with "Last_Attend_Date". 

  member table ID Attend_Freq Last_Attend_Date 123 4 2012-7-5 - it is the result of the query, the most recent date in the table rather than the newest member attendance ID Sdasy_aidi Antim_attendedt 987,123 2012- 6-5 888 123 2012-6-4 567 123 2012-6-3 456 234 2012-6-30 1909 292 2012-7-5   
 This is from the query, but to me that Instead of the most recent member of the attendance table, Last_Attend_Date is giving me the update which I need to update  
  Update M set Last_Attend_Date = (Selectable Mech Number (Last_Attend_Date) attendance include members M. presence where A.Member_ID = M.ID A.Member_ID = M.id) Join members M from a tourist '5' and Last_Attend_Date & lt; A.Member_ID = M.id Where Attend_Freq do getdate () - 14 and A.Member_ID = M.ID    
 
 
 The problem is that you are not associating your subkey with your external query. It helps in using different nicknames for all the tables, and it is unnecessary to join the  member  inside the subkey:  
  Create a table of members (ID int null, attend_freq int not null, Last_Attend_Date datetime nOT nULL) member (ID, Attend_Freq, Last_Attend_Date) values ​​(insert in 123.4, '19, 000,101 ') table appearance (id not make null integer, Member_ID empty int, Last_Attend_Date datetime No Taps) Attendance (ID, Member_ID, Last_Atten d_Date) VALUES (987123, 20, 120605), (888123, 20, 120604), (567123, 20, 120603), (456234, 20, 120 630 '), (L909,292, '20, 120,705') updated M set (presence A. choose from 2 Max (Last_Attend_Date) where A2.Member_ID = M.ID) Last_Attend_Date = --M M.ID = external table member M interior from attendance at A.Member_ID where m is a reference to a join. Attend_Freq & lt; 5 and A. Last_Attend_Date & lt; DATEADD (day 14, select CURRENT_TIMESTAMP) * FROM members   
 Results:  
  id Attend_Freq Last_Attend_Date --------- ---------------------------- 123 4 2012-06-05

Comments

Post a Comment