June 11, 2014 at 10:30 am
Please attached sample data. It has the following columns:
enc_idcust_idenc_dateenc_date_indexSet_id
837492185042/3/201411
7239640185042/21/201421
1500106227834/7/201411
6134871227834/18/201421
3256672639812/3/201311
129524992639812/10/201321
8964958263986/3/201432
2695236369069/4/201311
8233077369069/18/201321
2161084398085/22/201311
7675007398089/5/201322
76750083980812/10/201333
76750093980812/20/201343
76750193980812/22/201353
The data is populated in enc_id, cust_id, enc_date and enc_date_index columns. I want to populate data in Set_id column as follows.
I want to make sets of encounters for the same customer that are within 30 days of an earlier encounter. I want to populate numbers in the set_id column.
Example: customer 39808 has 5 encounters on following dates: 5/22/2013, 9/5/2013, 12/10/2013, 12/20/2013, 12/22/2013
The encounter on 9/5/2013 is not within 30 days of the previous encounter on 5/22/2013. So the first encounter is marked as 1 in set_id column, and encounter on 9/5/2013 is marked as 2 in set_id column. Similarly, encounter on 12/10/2013 is not within 30 days of 5/22/2013 or 9/5/2013, so it becomes set_id 3, encounter on 12/20/2013 is within 30 days of 12/10/2013, so this is also in set_id 3 and encounter on 12/22/2013 is also within 30 days of 12/10/2013 so this also gets set_id 3.
Please suggest how I can accomplish this. A sample sql will be highly appreciated.
Thanks.
Forum User:cool:
June 11, 2014 at 10:33 am
create table and insert data scripts for your example will be highly appreciated.......this will help us help you
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 11, 2014 at 11:12 am
Please find attached script files to create tables and data.
dbo.Cust_Encounters.Table.sql is the data to start with. dbo.Cust_Encounters_Sets.Table.sql shows what it will look like after creating sets.
Thank you for you help.
Forum User:cool:
June 11, 2014 at 2:18 pm
ForumUser3 (6/11/2014)
Please find attached script files to create tables and data.dbo.Cust_Encounters.Table.sql is the data to start with. dbo.Cust_Encounters_Sets.Table.sql shows what it will look like after creating sets.
Thank you for you help.
many thanks for the scripts....very helpful.
what have you tried so far?........quick look suggests a form of running total (maybe quirky!)
bit busy at the mo...maybe someone will chime in later.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply