From: dinesht15@gmail.com   
      
   Hi Experts   
   I am working on SQL server 2008 R2.   
   I am having two temp tables created which are having data like below.  Sample   
   data for two CUST_ID.  In actual there will be many CUST_ID.   
   TRANS_ID	CUST_ID	RCR_DT   
   1044	1001	3/5/2013 9:11   
   1045	1001	3/12/2013 10:20   
   1046	1001	3/16/2013 11:27   
   1047	1001	3/25/2013 7:23   
   1048	1002	2/2/2013 9:11   
   1049	1002	2/5/2013 17:22   
   1050	1002	3/21/2013 22:10   
   1051	1002	3/24/2013 23:15   
      
   CUST_ID	EFF_DT   
   1001	3/4/2013 13:45   
   1001	3/7/2013 12:45   
   1001	3/26/2013 7:26   
   1001	3/27/2013 9:16   
   1002	2/1/2013 9:11   
   1002	2/8/2013 17:22   
   1002	3/23/2013 22:10   
      
   In above temp tables I had stored data based on some criteria. Now I need to   
   write one query which will provide me the result data something like   
   TRANS_ID	CUST_ID	RCR_DT	EFF_DT	Days   
   1044	1001	3/5/2013 9:11	3/7/2013 12:45	2   
   1047	1001	3/25/2013 7:23	3/26/2013 7:26	1   
   1049	1002	2/5/2013 17:22	2/8/2013 17:22	3   
   1050	1002	3/21/2013 22:10	3/23/2013 22:10	2   
      
   Here days column is calculated as difference between EFF_DT and RCR_DT i.e   
   EFF_DT - RCR_DT.   
   In above result I need only those TRANS_ID for whom RCR_DT is less than EFF_DT   
   and RCR_DT is close to EFF_DT. If there are more than one EFF_DT for a CUST_ID   
   then I want EFF_DT which is close to RCR_DT. Filter other EFF_DT rows from   
   query result.   
   If there are no EFF_DT between two RCR_DT for a CUST_ID then first TRANS_ID   
   should not appear in result.   
   I am unable to achieve this in SQL query. Please help to write appropriate   
   query for the same.   
   Any help will be appreciated.   
      
   Thanks   
   Deepak   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)