June 11, 2014 at 10:30 am
Please attached sample data. It has the following columns:
enc_id cust_id enc_date enc_date_index Set_id
837492 18504 2/3/2014 1 1
7239640 18504 2/21/2014 2 1
1500106 22783 4/7/2014 1 1
6134871 22783 4/18/2014 2 1
325667 26398 12/3/2013 1 1
12952499 26398 12/10/2013 2 1
8964958 26398 6/3/2014 3 2
2695236 36906 9/4/2013 1 1
8233077 36906 9/18/2013 2 1
2161084 39808 5/22/2013 1 1
7675007 39808 9/5/2013 2 2
7675008 39808 12/10/2013 3 3
7675009 39808 12/20/2013 4 3
7675019 39808 12/22/2013 5 3
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