StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You probably need to unpivot the data to normalize it - so that you end up with:</p> <pre><code>Doctor: DoctorID, DoctorDetails... FeeSchedule: DoctorID, ScheduleID, EffectiveDate, OtherDetailAtThisLevel... FeeScheduleDetail: ScheduleID, ProcedureCode, Fee, OtherDetailAtThisLevel... </code></pre> <p>When the data comes in for a doctor, it is pivoted, a new schedule is created and the detail rows are created from the unpivoted data.</p> <p>SSIS has an unpivot component which is fine - you would load the schedule first and then the detail. If the format varies significantly, you might need a custom data source or just avoid SSIS.</p> <p>This system would keep track of new schedules for doctors. If the schedule is identical for a doctor, you could simply not insert it.</p> <p>If this logic is extensive, you could load the data to staging tables (SSIS or whatever) and do all this in SQL (T-SQL also has an UNPIVOT operator). That can have advantages in that the code is all in one place and can do all its operations in sets.</p> <p>Regarding the zip codes, if the doctor doesn't have a fee, are these like usual and customary fee? This could simply be determined from the zip code of the doctor row. In this case you have a few options. You can overlay the doctor fee schedule over a zip code fee schedule:</p> <pre><code>ZipCodeSchedule: ZipScheduleID, ZipCode, EffectiveDate ZipCodeScheduleDetail: ZipScheduleID, ProcedureCode, Fee </code></pre> <p>Or you could save this in the regular feeschedule (potentially with some kind of flag that it was defaulted to the UCR).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload