Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
korssane
Posting Yak Master
104 Posts |
 Posted - 2010-05-08 : 15:52:06
|
| hi guys, Today i have been helped a lot by the guys in this forum..and i am really thankful .hre is my problem :i have 2 tables table1 (c1,c2,c3) & table 2 (c11,c22,c33) with same fields format ( c1,c11:text , c2,c22:text, c3,c33:date (int)).I need to compare table 1 to table 2 and for all matching c1 = c11, i want to pull only 1st oldest record (c11,c22,c33) from table2. within the last 6 days time frame.any help will be appreciated..thanks |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-08 : 16:49:54
|
Try this:SELECT TOP(1) c11, c22, c33 FROM table2 AS T2 WHERE DATEDIFF(DAY, T2.c33, GETDATE()) <= 6 AND EXISTS(SELECT * FROM table1 AS T1 WHERE T1.c1 = T2.c11 AND DATEDIFF(DAY, T2.c3, GETDATE()) <= 6) ORDER BY T2.c33 |
 |
|
|
korssane
Posting Yak Master
104 Posts |
Posted - 2010-05-08 : 16:57:52
|
| hey malpashaa,thanks for the quick turnaround..will try it and get back to you..Why you are using the GETDATE() "CURRENT DATE" .My dates are c3 & c33 ( epoch time (INT)).for each c1 in table 1 matching c11 in table 2, i need to pull only one record where c33 (date ) is right before c3 (date) within the last 6 days. i have done something but getting many records with the last 6 days..but do not kow how to take the only only one closed to c3.thanks |
|
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-08 : 18:40:01
|
Based on your last reply then I think that the query should become:SELECT T1.c1, T1.c2, T1.c3, T2.c11, T2.c22, T2.c33 FROM table1 AS T1 OUTER APPLY (SELECT TOP(1) c11, c22, c33 FROM table2 AS T2 WHERE T1.c1 = T2.c11 AND T1.c3 >= T2.c33 AND DATEDIFF(DAY, T2.c33, T1.c3) <= 6 ORDER BY T2.c33 DESC) AS T2 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Need help ont his one please ??