MaxDate
-
:((I have 3 tables: table1: DateMod (smalldatetime) StId Eid table2: DateMod (smalldatetime) StId Wid table3: stid I am selecting the max(Wid) and max(eid) based onstid: select max(Wid),date from table2 a,table 3 where a.stid=b.stid group by a.stid union select max(eid),date from table1 a,table 3 where a.stid=b.stid group by a.stid At this point I need to compare the dates between the results (if sid listed in both results) and select the most resent value from one of the tables for each stid. How do I do this?
-
:((I have 3 tables: table1: DateMod (smalldatetime) StId Eid table2: DateMod (smalldatetime) StId Wid table3: stid I am selecting the max(Wid) and max(eid) based onstid: select max(Wid),date from table2 a,table 3 where a.stid=b.stid group by a.stid union select max(eid),date from table1 a,table 3 where a.stid=b.stid group by a.stid At this point I need to compare the dates between the results (if sid listed in both results) and select the most resent value from one of the tables for each stid. How do I do this?
If this is against SQL Server or Oracle, you could create a stored proc to do this in two passes or get real complex using subqueries in one pass. TWO PASS: create procedure GetLastModDate AS declare @tStidDates (stid int NULL, LastModDate smalldatetime NULL) set nocount on insert @tStidDates ( stid, LastModDate ) select A.stid, MAX(A.Wid) from table2 a, table 3 b where a.stid = b.stid group by a.stid insert @tStidDates ( stid, LastModDate ) select A.stid, MAX(A.eid) from table1 a, table 3 b where a.stid = b.stid group by a.stid select stid, MAX(LastModDate) from @tStidDates group by stid order by 1 onwards and upwards...
-
If this is against SQL Server or Oracle, you could create a stored proc to do this in two passes or get real complex using subqueries in one pass. TWO PASS: create procedure GetLastModDate AS declare @tStidDates (stid int NULL, LastModDate smalldatetime NULL) set nocount on insert @tStidDates ( stid, LastModDate ) select A.stid, MAX(A.Wid) from table2 a, table 3 b where a.stid = b.stid group by a.stid insert @tStidDates ( stid, LastModDate ) select A.stid, MAX(A.eid) from table1 a, table 3 b where a.stid = b.stid group by a.stid select stid, MAX(LastModDate) from @tStidDates group by stid order by 1 onwards and upwards...
-
for some reason SQL is not taking this line: declare @tStidDates (stid int NULL, LastModDate smalldatetime NULL)
Sorry, it should be declare @tStidDates table (stid int NULL, LastModDate smalldatetime NULL) onwards and upwards...