Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1418

Re: Heelp needed in SQL query

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Heelp needed in SQL query Deepak <dinesht15@gmail.com> - 2013-03-26 07:00 -0700
  Re: Heelp needed in SQL query Erland Sommarskog <esquel@sommarskog.se> - 2013-03-27 00:04 +0100

csiph-web