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

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

Heelp needed in SQL query

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next 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