From: esquel@sommarskog.se   
      
   Deepak (dinesht15@gmail.com) writes:   
   > 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.   
      
   Here is a query:   
      
   DECLARE @trans TABLE (trans_id int NOT NULL, cust_id int NOT NULL, rcr_dt   
   datetime NOT NULL)   
   INSERT @trans VALUES   
   (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')   
      
   DECLARE @cust TABLE (cust_id int NOT NULL, eff_date datetime NOT NULL)   
   INSERT @cust VALUES   
   (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')   
      
    WITH CTE  AS (   
      SELECT t.trans_id, t.cust_id, t.rcr_dt, c.eff_date,   
             row_number() OVER (PARTITION BY t.cust_id, c.eff_date   
                                ORDER BY t.rcr_dt DESC) as rowno   
      FROM   @trans t   
      CROSS  APPLY (SELECT TOP 1 c.eff_date   
                    FROM   @cust c   
                    WHERE  t.cust_id = c.cust_id   
                      AND  c.eff_date > t.rcr_dt   
                   ORDER  BY c.eff_date ASC) AS c   
   )   
   SELECT trans_id, cust_id, rcr_dt, eff_date   
   FROM   CTE   
   WHERE  rowno = 1   
      
   First I get for each transaction the next matching row in @cust. Then I   
   number the rows with the same cust_id and eff_date by rcr_dt in reverse   
   order and filter out the most recent row.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)