Our company tracks plans of health services for consumers. For any given consumer the plans are consecutively numbered, starting with 1. Each plan has a begin date. I need to return a result set of service plan numbers and dates as follows:
Return the last (latest) 3 plans by plan date plus the next plan number (which will have a null date). For instance consumer A has 4 plans as follows
Plan # Begin date
1 01/01/2001
2 01/01/2002
3 12/15/2002
4 12/01/2003
My result set should look like
Plan # Begin date
5 null
4 12/01/2003
3 12/15/2002
2 01/01/2002
When I use a TOP statement with a union as follows
Select top 3
blah, blah
union
select next number
order by begin_date desc
I end up with
Plan # Begin date
5 null
3 12/15/2002
2 01/01/2002
1 01/01/2001
Any ideas?
TIA
John