StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest Approach to Processing SQL Data problem
text
Body
copied!<p>I have a Data intensive problem which requires a lot of massaging and data manipulation and I'm putting this out there to see if anyone has an idea as to how to approach it. </p> <p>In simplest form. I have a lot of tables which can be joined together to give me a price listing for dentists and how much each charges for a procedure.</p> <p>so we have multiple tables that looks like this.</p> <pre><code>Dentist | Procedure1 | Procedure2 | Procedure3 | .........| Procedure? John | 500 | 342 | 434 | .........| 843 Dave | 343 | 434 | 322 | NULLs....| Mary | 500 | 342 | 434 | .........| 843 Linda | 500 | 342 | Null | .........| 843 </code></pre> <p>Dentists can have different number of procedures and different pricing for each procedures. But there are a lot of Dentists that have the same number of procedures and the same rates that goes with it. Internally, we create a unique ID for each of these so-called fee listings.</p> <p>like John would be 001, Dave would be 002, but Mary would be fee 001 and Linda would be 003 It's not so bad if I have to deal with this data once but these fee listings comes in flat files (csvs) which i basically have to DTS up to a SQL server to work with. and they come on a monthly bases. The pricing could change from month to month for each dentist which then would put them in a different unique ID internally. </p> <p>Can someone shed some light on as to how to best approach this problem so that it's most efficient to process on a monthly basis without having to do tons of data manipulation?</p> <ol> <li>what's the best approach to finding out the duplicates of the fee listings?</li> <li>How do i keep track of updating a Dentist's fee listing incase they change their rates the next month? if Mary decides to charge a different fee for procedure2, then she would have a different unique ID internally. how do i keep track of that on a monthly bases without having to delete everything and re-insert?</li> <li>There are a few million fee listings that I'm working with and some have standard rules that are based on zipcodes and some are just unique fee listings, what's the approach here?</li> <li>I can write some kind of ad-hoc .net program to work with it but it's a lot of data and working straight in SQL server would be easier for me.</li> </ol> <p>any help would be great, thanks guys.</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