Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
0
Guests Online:
36

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

Retrieve historical data

Microsoft SQL Server
Please wait...

Retrieve historical data

Postby Chopstik on Fri Jan 17, 2014 9:11 pm

Ok, I've been spending time going back and forth on my approach to this and getting nothing more than frustrated. Hopefully someone can assist.

I need to be able to pull claims based on a specific status at a specific point in time. I have two tables that I will need to use. A brief display is below of the relevant tables/columns:

Claim table:
- ClaimID
- ClaimTotal
- ClaimCreateDate

ClaimAudit table:
- ClaimID
- UpdateDate
- BeginningStatus
- EndingStatus

My problem is that the status can change all the time, going from open to pend to adjudicate to closed or any other number of options. More precisely, it can be in a pend status multiple times during its lifetime. For example, this is not an uncommon situation:

ClaimID UpdateDate BegStatus EndStatus
101 10/1/13 Open Pend
101 10/3/13 Pend Pend
101 10/15/13 Pend Adjudicate
101 10/15/13 Adjudicate Open
101 10/18/13 Open Pend
101 11/1/13 Pend Closed

What I want to be able to do is to pull 101 if it was in pend status as of 10/31/13. I've gone through so many variations at this point that I don't even have a good example that I can use to demonstrate what I've tried. I'm open to suggestions if anyone can point me in the right direction. Any help is greatly appreciated. Thanks.
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: Retrieve historical data

Postby gmmastros on Fri Jan 17, 2014 11:08 pm

Do you need to get all the claims that were pending on a certain date? Or do you already know the claim ID?

If a claim changed status on the date, what then? For example, I see multiple rows for 10/15/13, pend, adjudicate, open. If you wanted claims with pend on 10/15/13, would this qualify?
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Retrieve historical data

Postby tarwn on Fri Jan 17, 2014 11:20 pm

A bit of warning, I've been sick for several days and I'm waiting for the ibuprofen to kick in and bring my fever back under 100, so this may not be the best possible answer :P


In effect, we want to know when each status starts and ends. Using an OUTER APPLY, we can look up the next record that will change the status for each record and capture that date:

  1. SELECT CA.ClaimID,
  2.     CA.BeginningStatus,
  3.     CA.EndingStatus,
  4.     CA.UpdateDate,
  5.     CANext.NextChangeDate
  6. FROM ClaimAudit CA
  7.     OUTER APPLY (
  8.         SELECT TOP 1
  9.             CA2.UpdateDate AS NextChangeDate
  10.         FROM ClaimAudit CA2
  11.         WHERE CA2.BeginningStatus = CA.EndingStatus
  12.             AND CA2.EndingStatus <> CA2.BeginningStatus
  13.             AND CA2.UpdateDate >= CA.UpdateDate
  14.             AND CA2.ClaimID = CA.ClaimID
  15.         ORDER BY CA2.UpdateDate
  16.     ) CANext
  17. WHERE CA.BeginningStatus <> CA.EndingStatus
  18.  


The OUTER APPLY is looking up the next future record with a matching beginning status but a different end status (this way we can skip the records that don't change status, like the 10/3/13 Pend Pend record in your example) and we do this for every record that has a status change (again, we want to skip the pend/pend one). The end result is the set of records from claim audit with the date they will change status.

Now that we have that information, we can drop this into a CTE and query from it, like so:
  1. WITH ClaimAuditForTime AS (
  2.     SELECT CA.ClaimID,
  3.         CA.BeginningStatus,
  4.         CA.EndingStatus,
  5.         CA.UpdateDate,
  6.         CANext.NextChangeDate
  7.     FROM ClaimAudit CA
  8.         OUTER APPLY (
  9.             SELECT TOP 1
  10.                 CA2.UpdateDate AS NextChangeDate
  11.             FROM ClaimAudit CA2
  12.             WHERE CA2.BeginningStatus = CA.EndingStatus
  13.                 AND CA2.EndingStatus <> CA2.BeginningStatus
  14.                 AND CA2.UpdateDate >= CA.UpdateDate
  15.                 AND CA2.ClaimID = CA.ClaimID
  16.             ORDER BY CA2.UpdateDate
  17.         ) CANext
  18.     WHERE CA.BeginningStatus <> CA.EndingStatus
  19. )
  20. SELECT *
  21. FROM ClaimAuditForTime
  22. WHERE UpdateDate <= '2013-10-31'
  23.     AND (NextChangeDate >= '2013-10-31' OR NextChangeDate IS NULL)
  24.     AND EndingStatus = 'Pend'
  25.  


Because we have the date range now, we can simply ask for any records that have the matching EndingStatus we are looking for, starting at or before the date we are querying for, and changing at or after the date we are querying for (the NULL in this part is indicative of the last status each record was in, so you could read that as 'today' or even add an IsNull check into the CTE to specifically default it to today's date).

There's some newer functions in SQL 2012 that would make this even easier (LAG and LEAD) but I suspect you aren't on 2012 yet.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA

Re: Retrieve historical data

Postby Chopstik on Sat Jan 18, 2014 3:16 am

George,

I need all of the claims that were in a pending status as of a certain date. If the scenario I laid out occurred on the day in question, I would take the last status of the day. So, in your case, it would not qualify because I would want the last status which would be open as you laid out in your example. I also should have clarified that the date field is a date/time field - I just didn't list it in my example.

Tarwn,

I'm going to give that a whack and see what I come up with. I'm going to have to go back and do some studying as I've forgotten about the WITH statement (though it seems pretty self-explanatory from your example). And I only recently learned about the OUTER APPLY but that is also pretty self-explanatory in your example.

Thanks for the assistance!
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: Retrieve historical data

Postby gmmastros on Sat Jan 18, 2014 3:32 pm

I would approach this using the Row_Number function. This allows us to easily get the last row that qualifies.

For example:

  1. Declare @Temp Table(ClaimID Int, UpdateDate DateTime, BegStatus VarChar(20), EndStatus VarChar(20))
  2.  
  3. Insert Into @Temp Values(101, '10/1/13',  'Open',       'Pend')
  4. Insert Into @Temp Values(101, '10/3/13',  'Pend',       'Pend')
  5. Insert Into @Temp Values(101, '10/15/13', 'Pend',       'Adjudicate')
  6. Insert Into @Temp Values(101, '10/15/13', 'Adjudicate', 'Open')
  7. Insert Into @Temp Values(101, '10/18/13', 'Open',       'Pend')
  8. Insert Into @Temp Values(101, '11/1/13',  'Pend',       'Closed')
  9.  
  10. Insert Into @Temp Values(102, '10/1/13',  'Open',       'Pend')
  11. Insert Into @Temp Values(102, '10/3/13',  'Pend',       'Pend')
  12. Insert Into @Temp Values(102, '10/15/13', 'Pend',       'Adjudicate')
  13. Insert Into @Temp Values(102, '10/15/13', 'Adjudicate', 'Open')
  14. Insert Into @Temp Values(102, '10/18/13', 'Open',       'Pend')
  15. Insert Into @Temp Values(102, '10/21/13 10:00 AM',  'Pend',       'Open')
  16. Insert Into @Temp Values(102, '10/21/13 11:00 AM',  'OPen',       'Closed')
  17.  
  18. Insert Into @Temp Values(103, '10/1/13',  'Open',       'Pend')
  19. Insert Into @Temp Values(103, '10/3/13',  'Pend',       'Pend')
  20. Insert Into @Temp Values(103, '10/15/13', 'Pend',       'Adjudicate')
  21. Insert Into @Temp Values(103, '10/15/13', 'Adjudicate', 'Open')
  22. Insert Into @Temp Values(103, '10/18/13', 'Open',       'Pend')
  23. Insert Into @Temp Values(103, '10/31/13 10:00 AM',  'Pend',       'Open')
  24. Insert Into @Temp Values(103, '10/31/13 11:00 AM',  'Open',       'Pend')
  25.  
  26. Select  *,
  27.     Row_Number() Over(Partition By ClaimId Order By UpdateDate DESC) As RowId
  28. From    @Temp
  29. Where   UpdateDate < DateAdd(Day, 1, '2013-10-31')
  30.  


Note the where clause. I assume you wanted the end of day on your selected day. This is why I add one day and use the less than operator.

Also notice that the Row_Number function is partitioned by ClaimId so that the numbering restarts every time a claim id changes. Lastly, notice that I use order by updatedate desc. This will cause the RowId to be 1 for the last row that qualifies.

Of course, this isn't the solution, but with the data looking like this, the rest is kinda easy.

  1. Declare @Temp Table(ClaimID Int, UpdateDate DateTime, BegStatus VarChar(20), EndStatus VarChar(20))
  2.  
  3. Insert Into @Temp Values(101, '10/1/13',  'Open',       'Pend')
  4. Insert Into @Temp Values(101, '10/3/13',  'Pend',       'Pend')
  5. Insert Into @Temp Values(101, '10/15/13', 'Pend',       'Adjudicate')
  6. Insert Into @Temp Values(101, '10/15/13', 'Adjudicate', 'Open')
  7. Insert Into @Temp Values(101, '10/18/13', 'Open',       'Pend')
  8. Insert Into @Temp Values(101, '11/1/13',  'Pend',       'Closed')
  9.  
  10. Insert Into @Temp Values(102, '10/1/13',  'Open',       'Pend')
  11. Insert Into @Temp Values(102, '10/3/13',  'Pend',       'Pend')
  12. Insert Into @Temp Values(102, '10/15/13', 'Pend',       'Adjudicate')
  13. Insert Into @Temp Values(102, '10/15/13', 'Adjudicate', 'Open')
  14. Insert Into @Temp Values(102, '10/18/13', 'Open',       'Pend')
  15. Insert Into @Temp Values(102, '10/21/13 10:00 AM',  'Pend',       'Open')
  16. Insert Into @Temp Values(102, '10/21/13 11:00 AM',  'OPen',       'Closed')
  17.  
  18. Insert Into @Temp Values(103, '10/1/13',  'Open',       'Pend')
  19. Insert Into @Temp Values(103, '10/3/13',  'Pend',       'Pend')
  20. Insert Into @Temp Values(103, '10/15/13', 'Pend',       'Adjudicate')
  21. Insert Into @Temp Values(103, '10/15/13', 'Adjudicate', 'Open')
  22. Insert Into @Temp Values(103, '10/18/13', 'Open',       'Pend')
  23. Insert Into @Temp Values(103, '10/31/13 10:00 AM',  'Pend',       'Open')
  24. Insert Into @Temp Values(103, '10/31/13 11:00 AM',  'Open',       'Pend')
  25.  
  26. ;With Data As
  27. (
  28.     Select  *,
  29.         Row_Number() Over(Partition By ClaimId Order By UpdateDate DESC) As RowId
  30.     From    @Temp
  31.     Where   UpdateDate < DateAdd(Day, 1, '2013-10-31')
  32. )
  33. Select  *
  34. From    Data
  35. Where   RowId = 1
  36.     And EndStatus = 'Pend'
  37.  


Hope this helps.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm

Re: Retrieve historical data

Postby tarwn on Sat Jan 18, 2014 5:16 pm

George's solution is going to be lower impact/better performing than mine and because he thought to ask you some questions, will provide a better answer (I think mine would still work w/ you end of the day requirement if you gave it a timestamp w/ 11:59:59 or altered it to add a day like George did).

I recently had to do something where we showed number of days in each stage for a something similar to your claim, so in that case I needed the timespan for each audit-equivalent record and we used something similar to the pattern I posted above.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: Retrieve historical data

Postby Chopstik on Mon Jan 20, 2014 12:58 am

Thanks, George. I'm actually unfamiliar with the Row_Number() function so will also be researching this to get a better understanding. I'll play with this when I'm back in the office and let you know how it works out.

Tarwn, I did try yours and, with a bit of fiddling, it does appear to give what I need but there is a bit of a performance hit (even if it is just a reporting database). But I still need to do some research to understand some of the pieces that were a bit more unfamiliar to me. Actually, I may play with these tomorrow depending on how my day goes (it's a holiday for us)... Thanks!
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: Retrieve historical data

Postby gmmastros on Mon Jan 20, 2014 2:29 pm

The Row_Number() function is relatively straight forward and easy to understand (once you have been exposed to it).

Basically, this function will return a sequential number starting at 1 and increments by 1 for each row (similar to identity columns).

There are 2 things you can control about the Row_Number function. You can control the order of the numbering and you can cause it to start renumbering at one under certain conditions.

The Partition By clause will cause the row_number function to start over when the value changes. If you notice the query I suggested above, I use Partition By ClaimId. This means that every claim id in your table will have a row_number of 1. More importantly (for this case), there can only be 1 row for each claim id with a row_number of 1.

You can also control which row in the table (for each claim id) is numbered with a value of 1. This is controlled by the Order BY clause (in the row_number function). Notice that I order by UpdateDate DESC. This will cause the newest UpdateDate to have a value of 1 and the oldest UpdateDate to have a value equal to the number of rows in the table for that claim id.

Since there is a where clause on the UpdateDate column, we end up with a row for each claim where the row numbered with 1 is the newest UpdateDate prior to the cut-off date specified in the where clause. Put another way, it is the last row in the table for each claim id before a certain date. If I am not mistaken, this is exactly what you were looking for.

The query gets a little more complicated (but not much). I used a WITH block because we only want the last row (rows numbered with a 1). This can be done in a derived table, with block, temp table or table variable. Of these options, the WITH block and the derived table have better performance. I suspect they would both generate the same execution plan so are therefore (performance wise) the same.

I hope this explanation helps the query to make more sense to you. I encourage you to try it. If it returns the correct output and there is anything about this that you don't understand, please let me know. The concept behind this query is really quite powerful and will come in handy in many different situations.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm

Re: Retrieve historical data

Postby Chopstik on Tue Jan 21, 2014 2:04 pm

George, that is an absolutely wonderful explanation and makes a lot more sense to me than the dry presentations I found in BOL and other online sources. Thank you very much for that! :thumright:

And yes, I think I will find these to be very helpful in the future. I love when I can learn new things that both make sense to me as well as can be beneficial in other areas.

:)
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated