January 18, 2009 at 9:28 pm
I have a detail file which contains over 200,000 records.
I need to read each detail record sequentialy and then I need to read another file to all the eligibility records that fall within the service date of the first detail record.
For example, I read a detail record that has a service date of 12/18/2008
Then I need to read every eligibility record where that dates falls between the elibibility dates.
I need to create a result record set with the keys , service date and three eligibility fields.
I have been googling for awhail but cannot fine an example that works for me. I am using SQL 2005.
Could someone help me?
Thanks
January 18, 2009 at 9:38 pm
Simple query will do it.
Something like this:
SELECT *
FROM Details D
INNER JOIN Eligibility E ON D.[Service Date] BETWEEN E.[From Date] AND E.[To Date]
No cursors needed.
_____________
Code for TallyGenerator
January 19, 2009 at 6:23 am
Definitely try to solve this with a set-based solution. Cursoring through 200,000 rows will kill the server. If you post your DDL and some sample data (see the links in my signature), and I am sure someone will offer a solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 19, 2009 at 11:54 am
Do everything you can to avoid a cursor in a cursor for your process. Two-layered cursors yield dreadful, loathesome performance. You might be almost as well off having your dog write the code than to use a two-layered cursor.
January 19, 2009 at 1:34 pm
I can see from my last question that I cannot spell. Anyway we had a SQL DBA consultant working with us for about a year.
I called him last week and he thought the only way I could get the results I need is with a cursor. Believe me I would LOVE to avoid them. I am learning T-SQL as I go.
I tried using SQL Server Integrations services also.
I maybe should explain this better.
I would like to update the detail file with up to three eligbility codes.
For example I could have 10 detail records for clientid 1234
For each detail record I need to read a client record to get a PMI Nmber
then I use the PMI number to get the elibility ID and then I read the eligbility table using the eligibility id.
Many eligibility records can fit the one detail record.
service date of detail record is 12/18/2008 for clientid 1234
pmi number for client 1234 is 5678
elibility id for pmi number is 9999
Elig ID begin date end date eligibility code
9999 1/1/2008 null MA (medical assistance)
9999 6/30/2007 1/10/2009 SL ( Blue Cross)
9999 7/11/2008 12/31/2008 UC (UCare)
9999 1/1/2007 6/29/2007 PH
I want to update three fields on the first detail file (elig1, elig2, elig3) where the service dates falls within the begin and end date.(MA, SL, US)
We have created couple views we use to create these billing reports.
The user wants to see on every detail record that prints the eligibilty the client has for that date so they know where to bill.
We load the service detail every night(from a purchased finance system) into a data warehouse. I would just run the process to update those fields every night after the detail is loaded.
January 19, 2009 at 1:54 pm
shellyr (1/19/2009)
I can see from my last question that I cannot spell. Anyway we had a SQL DBA consultant working with us for about a year.I called him last week and he thought the only way I could get the results I need is with a cursor. Believe me I would LOVE to avoid them. I am learning T-SQL as I go.
I tried using SQL Server Integrations services also.
I maybe should explain this better.
I would like to update the detail file with up to three eligbility codes.
For example I could have 10 detail records for clientid 1234
For each detail record I need to read a client record to get a PMI Nmber
then I use the PMI number to get the elibility ID and then I read the eligbility table using the eligibility id.
Many eligibility records can fit the one detail record.
service date of detail record is 12/18/2008 for clientid 1234
pmi number for client 1234 is 5678
elibility id for pmi number is 9999
Elig ID begin date end date eligibility code
9999 1/1/2008 null MA (medical assistance)
9999 6/30/2007 1/10/2009 SL ( Blue Cross)
9999 7/11/2008 12/31/2008 UC (UCare)
9999 1/1/2007 6/29/2007 PH
I want to update three fields on the first detail file (elig1, elig2, elig3) where the service dates falls within the begin and end date.(MA, SL, US)
We have created couple views we use to create these billing reports.
The user wants to see on every detail record that prints the eligibilty the client has for that date so they know where to bill.
We load the service detail every night(from a purchased finance system) into a data warehouse. I would just run the process to update those fields every night after the detail is loaded.
Since you are reloaded this every night, why do you want to physically store this in the detail row, instead of just linking to it? Seems awfully costly to populate this every night, only to blow it away and start over each night.
A view linking the incoming detail to the extra columns might serve you a whole lot better......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 2:39 pm
The sql server billing report runs a couple of minutes already the way the consultant has written it, but I do not see an easier way it could have been done.
Within the reports I also link to two other databases to get the client diagnosis code which slows it down even more so I thought adding yet another linking would slow it down even more. One of the reports now takes about about 3 minutes to run and there are about 15 billing reports the user runs.
I thought if I just ran the process every night it would not slow the report runtime even more. If the eligiblity is already on the detail record the report would run faster I would think. Either way it is done I just need the correct statement to get it to work. I can load one eligibility code with no problem it is getting the other two in there correctly.
I could write this program using LINC (on a Unisys Mainframe) in less than 2 days. We have been converting to SQL Server the past year and should be completely off the mainframe by April.
If you have a better way please let me know as I welcome the help.
thanks
Shiloy
January 20, 2009 at 6:32 am
Without details, it's hard to say whether you're right or not. There are times where it does help to prepopulate, especially in a reporting scenario like you have.
That being said - for the size you're talking about - 3 minutes to generate anything seems awfully slow.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply