June 30, 2012 at 11:16 am
Hi,
I am stuck with a very strange situation where my current records needs to be updated from previous , and the previous would be from its previous. So , in other words my current needs to get the value of the pervious-previous. The problem for me is , there could be N number of previous for the current ( according to our data model in a effective period) . Below is a description of what i am trying to do and the challenges i am facing.
The first time members comes into the system\ the first row is a new and will be assigned a status of 1.
Any changes in the business keys across \ with in the same effective period will be treated as a transfer.
If the member was a new in the same effective and changed the hospital, it will have a state of 2 which is new transfer.
The state of 3 is continuous
If it changed the hospital across effective, then that will be considered as pure transfer in which will have a state of 4.
Also, if the member did not change any business key with in the same effective period than the previous state will be inherited to the current state.
Below is a sample of the incoming data where I need to stamp the status for the member for each effective period i.e a new \continuous\Transfer
MembnoAccounting periodEffective DateExpiration DateHospital
ABC3/1/2012 3/1/20123/4/2012 26
ABC3/1/2012 3/5/20123/18/2012 19
ABC3/1/2012 3/19/20123/20/2012 19
ABC3/1/2012 3/21/201212/31/9999 26
ABC4/1/2012 3/21/201204/30/2012 26
ABC5/1/2012 5/01/20125/03/2012 26
ABC5/1/2012 5/04/20125/07/2012 30
ABC5/1/2012 5/08/20125/20/2012 30
ABC5/1/2012 5/21/20126/25/2012 26
My current program takes the entire data set and creates a row number and them I join with the previous row to apply the logic. The current with previous takes care of most of the transfers except when the member has more then 2 transfers in the same month.
And the change could be any number of times in the same month , so my comparison of current with previous does not stamp the states correctly.
I am facing the problem for the situations when the member comes in as new and changes the hospital in the same month N number of times. Because I need to get the previous state to current state if the hospital did not change and I can have 3 records with diff effective and exp dates with the same hospital before it changes to another.
MembnoAccounting periodEffective DateExpiration DateHospital rowkey status
ABC3/1/2012 3/1/20123/4/2012 26 1 1
ABC3/1/2012 3/5/20123/18/2012 19 2 2
ABC3/1/2012 3/19/20123/20/2012 19 3 2
ABC3/1/2012 3/21/201212/31/9999 26 4 2
ABC4/1/2012 3/21/201204/30/2012 26 5 3
ABC5/1/2012 5/01/20125/03/2012 26 6 3
ABC5/1/2012 5/04/20125/07/2012 30 7 4
ABC5/1/2012 5/08/20125/20/2012 30 8 4
ABC5/1/2012 5/21/20126/25/2012 26 9 4
June 30, 2012 at 4:14 pm
The bigest problem that I see is that there is no guarantee as to the order of the rows. Let's take just the rows from 3/21...
ABC 5/1/2012 3/21/2012 12/31/9999 26
ABC 5/1/2012 3/21/2012 12/31/9999 30
ABC 5/1/2012 3/21/2012 12/31/9999 30
ABC 5/1/2012 3/21/2012 12/31/9999 26
If each one of those were on it's own strip of paper and I put them into a hat and shook the hat, would you be able to put those rows back in the correct order with no prior knowledge?
Neither can SQL Server. There has to be another column that identifies the correct temporal order of the data before we can accurately solve this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2012 at 10:38 pm
I really loved your reply. I have updated my query . Please look at that and let me know your thoughts on how to proceed.
I need to refine the current programe which i have in place because i am not happy with the current solution.
Thanks for looking into this.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply