question

Larry_Krone avatar image
Larry_Krone asked

I need to figure out the oldest date in an interesting scenario

I am dealing with the following set of tables: tbl_contact_history which has contactid and historyid as columns tbl_contact_note which has contactid and noteid as columns tbl_history which has historyid as uniq identifier and createdate as datetime tbl_note which has noteid as uniq identifier and createdate as datetime tbl_contact which has contactid as uniq identifier cust_DateStamp as createdate What I need to do is to find the oldest contact date for each contact (a contact can be defined as either a note or history) and then write that date out to cust_DateStamp in the tbl_contact. The SQL would have, I think, have to go through the tbl_contact for each contact, somehow get the date from tbl_history and associate it via the same historyid in tbl_contact_history with the contact and compare that date with the first note for that contact, which would involve getting the date from tbl_note and associating it via the same noteid in tbl_contact_note with the contact, and then writing the oldest date from either note or history out to cust_dateStamp in tbl_contact. Is there a way to do this? All I seem to get out of it is a major headache :) Thank you!
sqlserver2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
DenisT avatar image
DenisT answered
If I understood your question correctly, then something like this should work: DECLARE @tbl_contact_history TABLE ( contactid INT NOT NULL , historyid INT NOT NULL ); DECLARE @tbl_contact_note TABLE ( contactid INT NOT NULL , noteid INT NOT NULL ); DECLARE @tbl_history TABLE ( historyid INT NOT NULL , createdate DATETIME NOT NULL ); DECLARE @tbl_note TABLE ( noteid INT NOT NULL , createdate DATETIME NOT NULL ); DECLARE @tbl_contact TABLE ( contactid INT NOT NULL , cust_DateStamp DATETIME NULL ); INSERT INTO @tbl_contact SELECT 1 , NULL UNION ALL SELECT 2 , NULL UNION ALL SELECT 3 , NULL; INSERT INTO @tbl_note SELECT 10 , GETDATE() UNION ALL SELECT 11 , DATEADD(MONTH, -2, GETDATE()) UNION ALL SELECT 12 , GETDATE(); INSERT INTO @tbl_history SELECT 20 , DATEADD(MONTH, -1, GETDATE()) UNION ALL SELECT 21 , GETDATE() UNION ALL SELECT 22 , DATEADD(MONTH, -3, GETDATE()); INSERT INTO @tbl_contact_note SELECT 1 , 10 UNION ALL SELECT 2 , 11 UNION ALL SELECT 3 , 12; INSERT INTO @tbl_contact_history SELECT 1 , 20 UNION ALL SELECT 2 , 21 UNION ALL SELECT 3 , 22; SELECT * FROM @tbl_contact; UPDATE c SET c.cust_DateStamp = CASE WHEN h.createdate > n.createdate THEN n.createdate ELSE h.createdate END FROM @tbl_contact AS c INNER JOIN @tbl_contact_history AS ch ON ch.contactid = c.contactid INNER JOIN @tbl_contact_note AS cn ON cn.contactid = c.contactid INNER JOIN @tbl_history AS h ON h.historyid = ch.historyid INNER JOIN @tbl_note AS n ON n.noteid = cn.noteid; SELECT * FROM @tbl_contact; This query only updates assuming that all rows exist.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you. That appears to work!. I really appreciate the help.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.