March 19, 2003 at 2:53 pm
I need to take a table like this:
user_id, change_date, oldPlanID, newPlanID
and generate a report based off data like this:
date, user_id, currentPlan
we have recorded the times when the user changed their "plan" from one integer value to another, but we need to know, over the course of a month or so, for each day, how many users had which "plan"s in effect. Hope that makes sense!
I have started to do this with nested cursors--i.e. get all the unique users, then for each user, start with the first change_date, add a row to a temp table, then increment until the user changes their plan or the date is greater than today. After building up a big temp table, you could select and group by date and planID to get a count of the users. But there are a lot of litte gotcha's involved in this...
Does anyone have an example of how to do this so I do not reinvent the wheel? This seems like a common kind of project, so I am hoping someone has a good idea of the best way to do it.
Thanks!
March 19, 2003 at 3:24 pm
So what is the primary key on this table? What is the exact table definition?
March 19, 2003 at 4:24 pm
The historical data is contained in this table,
[user_medplans] (
[unique_id] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [int] NULL ,
[oldMedPlan] [smallint] NULL ,
[newMedPlan] [smallint] NULL ,
[changedDate] [datetime] NULL
Unfortunately, one complication is that it is possible that a user will change their MedPlan multiple times in one day...in that case we should ignore all changes except the last one.
Here is a sample of the history of one user:
unique_id user_id oldMedPlan newMedPlan changedDate
----------- ----------- ---------- ---------- ------------------------------------------------------
1002 211651 0 8 2003-01-01 01:38:01.950
2827 211651 8 8 2003-01-02 23:57:53.193
4354 211651 8 8 2003-01-05 13:44:01.960
4661 211651 8 8 2003-01-06 00:55:39.763
9124 211651 8 8 2003-01-13 10:19:18.960
13143 211651 8 8 2003-01-21 01:26:04.140
16540 211651 8 8 2003-01-28 10:39:42.803
19426 211651 8 8 2003-02-04 11:55:52.230
22635 211651 8 8 2003-02-12 00:43:51.667
24959 211651 8 8 2003-02-19 01:01:16.960
27764 211651 8 8 2003-02-26 02:00:53.593
30580 211651 8 8 2003-03-05 10:38:51.487
33317 211651 8 8 2003-03-12 23:28:33.133
(13 row(s) affected)
In this particular case, the user does not actually change their plan. The system prompts the user every 7 days to verify if they have changed their plan or not...and records their response. It is also possible the data could look like this:
1002 211651 0 8 2003-01-01 01:38:01.950
2827 211651 8 5 2003-01-01 03:57:53.193
4354 211651 5 6 2003-01-05 13:44:01.960
4661 211651 6 8 2003-01-06 00:55:39.763
The ultimate goal of the report I am generating is to count this user as a person who has been on medPlan 8 from 1/1 to today. One way to accomplish this would be to insert ~80 rows in a temp table like this:
year, month, day, user_id, medPlan
2003, 1, 1, 211651, 8
2003, 1, 2, 211651, 8
2003, 1, 3, 211651, 8
...
2003, 3, 19, 211651, 8
then, do it for each user who has ever had a plan
then,
select year, month, day, medPlan, count(user_id)
group by year, month, day, medPlan
order by year, month, day, medPlan
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply