April 19, 2009 at 5:16 pm
Gidday, Bruce!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 20, 2009 at 7:48 am
bruce.trimpop (4/17/2009)
GSquared (4/17/2009)
bruce.trimpop (4/17/2009)
Mike C (4/17/2009)
bruce.trimpop (4/17/2009)
john.arnott (4/17/2009)
Stephen Hirsch (4/17/2009)
. . .cursors tend to be easier to write . . .I think you'll get some argument on this. Cursors are easier to understand for someone coming from a procedural programming background -- like that C++ programmer Mike C tells us about in his last post. But one of the main points that I think RBarry is making in his article is that once a developer understands the mind-set of declarative programming and set-based solutions, they are often actually easier to understand.
Both arguments are relative to the developer's experience....
I think the question then becomes.....
Exactly! 😉 Assuming, of course, your organization has the time and luxury to do so!
I can't imagine a single other professional field where people would actually defend the practice of "we don't do it right, because we can't be bothered to learn how".
It truly amazes me how some peoples worlds are black and white. I'd love to live it that world. First, I was NOT defending the practice I was stating that some companies in the real world whether right or wrong do not have the luxury or time to do things "right" by your definition. They have obligations (contractual or otherwise) to get it done and have it work under given time constraints.
Imagine how you would feel if your physician told you that he uses bloodletting instead of antibiotics, because antibiotics are complex, and bloodletting is "something that my nurses are already pretty much trained to do, because they know how to take samples for the lab"
Ok how about this if you want to talk about medicine, a medic on the battle field can stop the bleeding and sow up a wound with 10 stitches but that patient will live and he can move on to the next patient, or he can take his time, "do it right!" and use 50 stitches while the next patient bled to death.
In my world I often don't have time to do a perfect solution, I have to do the best I can under the time/contractual obligations that I am presented with and get something that works to the customer. Could it be done better...given more time absolutely....does work the way it was delivered...you betcha!
Part of the problem with black and white arguments is they are rarely real world. They tend to exaggerate the opposite ends of the spectrum. The analogy is not one of blood letting vs antibiotics, it's one of antibiotics vs homeopathic solutions. Some would argue homeopathic is the better choice because of all the problem of over use of antibiotics causing drug resistance, homeopathic may be the "right" choice but it takes longer, and I need to get the infection under control ASAP!
My world exists somewhere in the middle and I've been doing this for a lot of years!!!
I would bet you any amount of money you care to wager that a properly trained SQL dev, one who actually knows his tools, can build set-based code faster and more performantly than you can build a cursor, for the same situations.
And the battlefield medical analogy is quite obviously flawed. For it to really match cursors vs actually doing the job right, you'd have to have the "10 stitches" version result in the guy being crippled for life, or at least until someone who actually knows what they're doing comes along and fixes your mess. And, since writing set-based code is actually usually faster than writing a cursor or loop, the concept of letting the next guy in line bleed to death is also backwards.
While you're declaring your cursor, opening it, and declaring the variables, I've already finished my version of the code, because it takes less typing than yours does.
So, to make the analogy correct: You have a choice between crippling 9 of your 10 injured soldiers, and maybe even making some of them worse than they were when you got them, or of getting the job done right, saving all 10, and having their families write thank you letters to you. The first is cursors, the second is doing it right in the first place.
I'm sorry that you don't know how to code well in SQL. I recommend, however, that instead of justifying it as "I just get the job done faster this way", what you do instead is either learn to do it right, or leave it to someone who does know how to do it right. That's more professional, and shows an actual concern for the well-being of your employer/customer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2009 at 7:51 am
bruce.trimpop (4/16/2009)
I guess it boils down to:"Is there a way to do a set based select statement that is based on the tables and columns used in the select being stored in a table?"
Yep - and the answer is I don't think so. It sounds to me like the solution was implemented based on a slightly incomplete understanding (I only recognise it because I've done it myself in the past).
And I liked your other post somewhere about people being tied by real-world constraints. It's a pretty common story actually, coders seem to be split into three categories IMHO:
* Perfect world blogger - this is someone who always has the time to approach each problem perfectly. They have the luxury of time to completely understand a problem domain before they perform in any implementation, and have the time to research which technologies can work for them.
* The luddite's apprentice - this is someone who learnt how to do something once (probably in about 1974), and have been using the same approach ever since. They read about new technology, but generally find an obscure reason why it's not quite as good as what they're doing already. This is the complete opposite end of the spectrum to the perfect world blogger.
* The employed programmer - this is someone who aspires to be a perfect world blogger, but is constrained by the business they work for (usually!). They read about new technology and try and implement new functionality using new technology if they can, but sometimes have to fall back to 'old methods' because they don't have the time to always implement something new. Most of the time, they will try and create something which they know can be re-used, even if it's not perfect right at the time because they need to get something finished quickly.
I've been in my current job for about 7 1/2 years. I started somewhere near to perfect world blogger - and as time progressed, slid down the scale because the workload and resource constraints made that a certainty. I'm finishing that job in about 2 1/2 weeks, and going to start a new business making SQL Server tools. I've been working on that for a while in my spare time, probably somewhere in the upper third of the scale, and when I go full time I'm aiming to be a perfect world blogger. Here's hoping! 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 20, 2009 at 7:58 am
GSquared (4/20/2009)
I'm sorry that you don't know how to code well in SQL. I recommend, however, that instead of justifying it as "I just get the job done faster this way", what you do instead is either learn to do it right, or leave it to someone who does know how to do it right. That's more professional, and shows an actual concern for the well-being of your employer/customer.
I'm sorry, but that's just plain rude.
People do live in the real world, whereby they need to get a job done quickly. See the example back in this thread where there are a set of tables which can't be changed because of consequential time constraints (to use an analogy, it's easy to build foundations correctly - more difficult when there is already a very large house on top). I personally can't think of a way to get around that problem without changing the tables, and you'll see that the author is perfectly accepting of the fact that they do need a re-design. He does not have the time.
You obviously do. And it seems to me that rather than lauding it over other people, being rude to them, you should be pleased that you have the time to be a perfect world blogger, and count your blessings.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 20, 2009 at 12:50 pm
Matt Whitfield (4/20/2009)
GSquared (4/20/2009)
I'm sorry that you don't know how to code well in SQL. I recommend, however, that instead of justifying it as "I just get the job done faster this way", what you do instead is either learn to do it right, or leave it to someone who does know how to do it right. That's more professional, and shows an actual concern for the well-being of your employer/customer.
I'm sorry, but that's just plain rude.
People do live in the real world, whereby they need to get a job done quickly. See the example back in this thread where there are a set of tables which can't be changed because of consequential time constraints (to use an analogy, it's easy to build foundations correctly - more difficult when there is already a very large house on top). I personally can't think of a way to get around that problem without changing the tables, and you'll see that the author is perfectly accepting of the fact that they do need a re-design. He does not have the time.
You obviously do. And it seems to me that rather than lauding it over other people, being rude to them, you should be pleased that you have the time to be a perfect world blogger, and count your blessings.
You're the one who's said that you can't code correctly and fast. You can either code SQL correctly, or you can code SQL fast, but you can't do both. I, and many others, do both. It takes longer to write a cursor-based solution than it does to write an optimum solution, in SQL. IF you know what you're doing.
I'm not lauding that over other people. And I don't have tons of time because I don't live in "the real world". I have a job. I get it done efficiently, correctly, and fast. I do this because I have spent decades developing the skills to be efficient and effective at what I do. You, apparently, have not done this. I don't know why that is, but the single most common cause of "I don't have time to do it right", is because of lack of the skill to do it right. The single most common cause of lack of skill is inexperience.
The difference between an inexperienced person who is going to stay that way and an inexperienced person who is going to change into an experienced person, the only difference, is attitude. The future experienced person doesn't justify poor workmanship, doesn't try to blame things on prior workers, doesn't spend his effort on figuring out ways that "it's not my fault". They focus their effort on doing something as right as they possibly can, and on constantly learning how to make it more right.
You can only take positive control of anything to the degree that you take responsibility for it. That includes learning about it. Justification, excuses, reasons for a poor job, or even just a job that's not as good as you know can be done, these are just ways to avoid responsibility.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 20, 2009 at 1:20 pm
Whether sometimes you respond to a "situation" as best you can, even while aware that someone with different skills or experience may have offered a superior solution, I don't see how anyone can justify that as an acceptible model for ongoing work.
A co-worker/team-leader of mine years ago had a small sign on her desk that asked "If you don't have time to do it right the first time, where are you going to find the time to do it over?"
You should always have an answer for that whenever you consciously choose a "quick and dirty" path. And yes, I would say that in 99.9% of all cases, iterative, procedural code in SQL is dirty. I also agree that with training, in most cases it's not even worth calling "quick".
April 20, 2009 at 1:43 pm
john.arnott (4/20/2009)
...A co-worker/team-leader of mine years ago had a small sign on her desk that asked "If you don't have time to do it right the first time, where are you going to find the time to do it over?"...
LOL I had already just changed my signature liine after catching up on this thread!!!! Seriously, I am not a copy cat!
😎 Kate The Great :w00t:
If you don't have time to do it right the first time, where will you find time to do it again?
April 20, 2009 at 2:56 pm
GSquared (4/20/2009)
You're the one who's said that you can't code correctly and fast. You can either code SQL correctly, or you can code SQL fast, but you can't do both.
Nope, I said I couldn't think of a way around the problem which bruce.t presented without changing the tables. The way you turn that into 'I can't code correctly and fast' is what I'm getting at by saying that you are rude.
GSquared (4/20/2009)
I, and many others, do both. It takes longer to write a cursor-based solution than it does to write an optimum solution, in SQL. IF you know what you're doing.
Unless, as I said in my post, writing it set based means fundamental changes to a system whereby those changes would incur a significant time penalty in implementation.
GSquared (4/20/2009)
I'm not lauding that over other people.
You should possibly look up the definition - putting other people down while praising your own skills is pretty much lauding it over people.
GSquared (4/20/2009)
And I don't have tons of time because I don't live in "the real world". I have a job. I get it done efficiently, correctly, and fast. I do this because I have spent decades developing the skills to be efficient and effective at what I do. You, apparently, have not done this.
Again, would love to know how you infer this. Just because I am willing to say when I cannot think of a solution to a particular problem given *constraints*, I don't see how that gives you rise to belittle my skills, which, frankly, you have absolutely no understanding of. I find that intensely arrogant.
GSquared (4/20/2009)
I don't know why that is, but the single most common cause of "I don't have time to do it right", is because of lack of the skill to do it right. The single most common cause of lack of skill is inexperience.
If you are building something from the ground up, it is a very different story to when you inherit something in a state where it is clearly deficient and can't be changed.
GSquared (4/20/2009)
The difference between an inexperienced person who is going to stay that way and an inexperienced person who is going to change into an experienced person, the only difference, is attitude. The future experienced person doesn't justify poor workmanship, doesn't try to blame things on prior workers, doesn't spend his effort on figuring out ways that "it's not my fault". They focus their effort on doing something as right as they possibly can, and on constantly learning how to make it more right.
I think you'll find that's what the guy did. I'm sure you're going to wow us with your solution to the posted problem, rather than just talking down to everyone with diatribe and vilification, though.
GSquared (4/20/2009)
You can only take positive control of anything to the degree that you take responsibility for it. That includes learning about it. Justification, excuses, reasons for a poor job, or even just a job that's not as good as you know can be done, these are just ways to avoid responsibility.
Thoroughly agree. It's just a shame that you haven't really read what I posted, it's a shame you haven't enlightened us all with your amazing solution to the problem, and it's a shame that the way that you talk down to people without knowing anything about them is so fantastically rude.
Personally I don't use any cursors for data manipulation on any scale at all. That's because the systems that I work with I am lucky enough to have had the chance to build from the ground up. The last time that the whole 'cursors' debate came up in Gaby Abed's thread, I think you'll find I posted the fastest set based remix (with Jeff's help 🙂 ). However, I really can't see how you would fix the problem that bruce.t posted without using some sort of cursor in T-SQL. If it was me, I would probably re-write the process into a C# app, give the data to that app in as efficient a form as possible, and put it back in the SS afterwards. But that's not really working within the problem domain, is it? That's called a constraint.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 20, 2009 at 2:59 pm
john.arnott (4/20/2009)
Whether sometimes you respond to a "situation" as best you can, even while aware that someone with different skills or experience may have offered a superior solution, I don't see how anyone can justify that as an acceptible model for ongoing work.
With the utmost respect - is there anyone here that has actually offered a superior solution to bruce.t's problem? I see a lot of putting down, but no attempt to say 'this is how I might have done it'.
Personally, I think this should be a place of learning, not a place where people come for others to put them down and belittle their skills.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 20, 2009 at 4:34 pm
Matt Whitfield (4/20/2009)
john.arnott (4/20/2009)
Whether sometimes you respond to a "situation" as best you can, even while aware that someone with different skills or experience may have offered a superior solution, I don't see how anyone can justify that as an acceptible model for ongoing work.With the utmost respect - is there anyone here that has actually offered a superior solution to bruce.t's problem? I see a lot of putting down, but no attempt to say 'this is how I might have done it'.
Personally, I think this should be a place of learning, not a place where people come for others to put them down and belittle their skills.
Matt, I detect no disrespect from you, nor have I intended any in my posts. I don't know where I may have said something that belittled anyone's skills. The post you quote may well have been written in the first person rather than the third -- note that it certainly wasn't meant in the second person. Since I wasn't directing it at any individual, but rather as a general comment on the overall direction the discussion has taken, perhaps it could have been phrased like this:
Whether sometimes I respond to a "situation" as best I can, even while aware that someone with different skills or experience may have offered a superior solution, I don't see how I could justify that as an acceptible model for ongoing work.
As for "bruce.t's problem", I'm not sure I know what you mean. I've offered sympathy on his points, saying that it's sometimes all we can do to get things back on track again. His posts seem to fit that take on things, and have supported the idea of learning to use superior tools when they become available.
bruce.trimpop (4/17/2009)
. . .. . .
What I'm saying is that while the training is going on I still have product to deliver. So I deliver the best solution I can today which is when it's due. Tomorrow I find out about some new whiz bang feature in training...great I'll use that on the next project, but yesterday's project with the old "not right" solution, that works fine by the way, is out the door. If I have the luxury of some fee time maybe I'll be able to refactor it!
I certainly didn't mean to be heard as putting anyone down. I am having a hard time understanding why anyone feels the need to defend stop-gap measures as though the hurry-hurry, get-it-done-NOW methods are acceptable for on-going work. You seem to understand that, so I think we can agree that even if someone has managed to make something work with a cursor-based solution, for instance, the concept of continual improvement would lead them to learn new techniques that take advantage of new technology, and to periodically review existing applications for possible enhancements that would make them more stable and/or more efficient. That's not putting anyone down, I hope.
Maybe it's just evidence that I don't understand the point of so many posts that seem to argue against learning the techniques RBarry is writing about, and if that's the case, it's my failing.
April 20, 2009 at 5:34 pm
john.arnott (4/20/2009)
I certainly didn't mean to be heard as putting anyone down. I am having a hard time understanding why anyone feels the need to defend stop-gap measures as though the hurry-hurry, get-it-done-NOW methods are acceptable for on-going work. You seem to understand that, so I think we can agree that even if someone has managed to make something work with a cursor-based solution, for instance, the concept of continual improvement would lead them to learn new techniques that take advantage of new technology, and to periodically review existing applications for possible enhancements that would make them more stable and/or more efficient. That's not putting anyone down, I hope.Maybe it's just evidence that I don't understand the point of so many posts that seem to argue against learning the techniques RBarry is writing about, and if that's the case, it's my failing.
I absolutely 100% agree. Reading my post back it seems like i'm offended by what you wrote, and i'm not, so I whole heartedly apologise if that came across because it's not what I meant!
And I don't really get it either - I think that we showed that the last time we had the 'massive thread about cursors' 🙂 But - I have to say it - I can't think of a way of getting round bruce.t's problem - which I personally found really disappointing! I was hoping to be able to get a cursor solution killed, make someone happy and get some personal satisfaction too! I'm sure it would be possible to copy the data to temp tables and work with a more set based approach from there, but that wouldn't be as good because it wouldn't be usable as a long term solution in that environment. Compare that to the last cursor thread and I think that was a different story. We managed (I say we because Jeff wrote a stunning bit of SQL which I then cheekily nicked and slightly improved upon) to improve the speed, get rid of the cursor, and find & fix a bug at the same time... Oh well - onwards and upwards! 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
April 20, 2009 at 8:22 pm
Instead of searching through 300+ posts, could we get bruce t's problem posted to a new forum thread and a link added here for anyone interested to follow to that thread? I think it would help, and I would at least look at it again, and possibly see what I could do with it.
Thanks.
April 20, 2009 at 9:08 pm
Matt Whitfield (4/20/2009)
But - I have to say it - I can't think of a way of getting round bruce.t's problem - which I personally found really disappointing!
I may yet give 'er a "yank", but I've got a lot of other things going. Haven't really looked at the problem deeply, yet.
For those setup with 50 posts per page, Bruce.T's problem is on page 3 about half way down.... somewhere around post 125 or so.
--Jeff Moden
April 20, 2009 at 9:14 pm
bruce.trimpop (4/16/2009)
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
Bruce
Here is the SQL i used on 2000 to re-create the data and tables - some mods were required, so hope you can check.
Is it possible that you could post the cursor you use? I am having trouble understanding how some of the data interacts, and seeing the source would really help.
My initial reaction would be that the table layout would benefit from a bit of a redesign in terms of being somewhere close to 3NF, which would probably have made the set based approach a lot easier in the first place. I presume that you cannot change table structures however, and will respect that in my solution!
It does (need redesign) and I can't. :crying:
Posting the cursor code probably won't help much since I stripped down the functionality to a bare minimum.
The code does a great deal more than what I've posted and would probably confuse the matter even more. I'll try to describe critical part of the process, maybe that will help. :crazy:
A client (dbo.client) receives services (dbo.service) for which a charge is calculated and stored in service.charge.
The client is assessed a fee based on ability to pay (dbo.clientfee) that can be a flat dollar amount or a percentage of the charge. The fee can be determined in 1 of 2 ways. Either a flat amount which is stored in dbo.clientfee or a table (dbo.psfeetablemstr) driven amount based on some client data falling into a set of ranges assigned to that table (dbo.psfeetable). All of this is conditional based on the service date falling within effective date ranges for the client fee (dbo.clientfee) or the fee table (dbo.psfeetablemstr). Also conditional on whether the service is covered by the particular fee (dbo.clientfee or dbo.psfeetablemstr) as determined by service components and their possible values as set up in (dbo.limitcodes) Also conditional on whether the particular client fee is capped by number of services and/or dollar totals and whether the cap has been met. The crux of the problem is the fee table (dbo.psfeetablemstr) is user configurable to use any client related table and any column in that table as the value used to compare against the fee table ranges (dbo.psfeetable) to find what slot the client falls in and what fee to use. I could figure out no way of getting those table/column names out of the fee tables and used in a query without building a dynamic query to return the psfeetable
row that contained the range that that client's data fell into. And given the unlimited number and in any combination of client fees I could figure no way of doing this without using a cursor.
I guess it boils down to:
"Is there a way to do a set based select statement that is based on the tables and columns used in the select being stored in a table?"
example:
If a psfeetablemstr row contains table1_vc = 'dbo.client', column1_vc = 'Income', table2_vc = 'dbo.sometable', column2_vc = 'somecolumn'
from that I need a select that is something like:
SELECT ft.value3
FROM dbo.client t1
JOIN dbo.sometable t2 ON t1.clientid = t2.clientid
JOIN dbo.psfeetable ft ON t1.income >= ft.valuemin1 and t1.income = ft.valuemin2 and t2.somecolumn <= ft.valuemax2
JOIN dbo.psfeetablemstr ftm ON ft.psfeetablemstrid = ftm.uniqueid
JOIN dbo.clientfee cf ON t1.clientid = cf.clientid
JOIN dbo.service s ON s.clientid = t1.clientid
Keeping in mind that a psfeetablemstr row might contain 1 or 2 table/column values and might be different table/column values from row to row.
I can't figure out how to do this other than with a cursor and dynamically building the select based on the table/column names stored in the
psfeetablemstr table for that particular fee table.
Actually I think I can pare down the example even more to get to the crux of my problem.
CREATE TABLE [dbo].[client](
[clientid] [int] not null,
[income] [numeric] (12,2)
)
CREATE TABLE [dbo].[sometable](
[clientid] [int] not null,
[dependents] [int]
)
CREATE TABLE [dbo].[clientfee](
[uniqueid] [int] NOT NULL,
[clientid] [int] not null,
[psfeetablemstrid] int,
CONSTRAINT [pk_clientfee] PRIMARY KEY NONCLUSTERED
( [uniqueid] ASC )
)
CREATE TABLE [dbo].[psfeetablemstr](
[uniqueid] [int] NOT NULL,
[table1_vc] [varchar](20) NULL,
[column1_vc] [varchar](20) NULL,
[table2_vc] [varchar](20) NULL,
[column2_vc] [varchar](20) NULL,
CONSTRAINT [pk_psfeetablemstr] PRIMARY KEY NONCLUSTERED
( [uniqueid] ASC )
)
CREATE TABLE [dbo].[psfeetable](
[uniqueid] [int] NOT NULL,
[value1min_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable1] DEFAULT (0),
[value1max_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable2] DEFAULT (0),
[value2min_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable3] DEFAULT (0),
[value2max_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable4] DEFAULT (0),
[value3_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_psfeetable5] DEFAULT (0),
[psfeetablemstrid] [int] NOT NULL,
CONSTRAINT [pk_psfeetable] PRIMARY KEY NONCLUSTERED
( [uniqueid] ASC )
)
---Sample data
---dbo.client
INSERT INTO [dbo].[client] ([clientid], [income])
VALUES (1,25000)
INSERT INTO [dbo].[client] ([clientid], [income])
VALUES (2,10000)
---dbo.sometable
INSERT INTO [dbo].[sometable] ([clientid], [dependents])
VALUES (1,2)
INSERT INTO [dbo].[sometable] ([clientid], [dependents])
VALUES (2,1)
--dbo.clientfee
INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])
VALUES (1,1,1)
INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])
VALUES (2,1,2)
INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])
VALUES (3,2,1)
INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid])
VALUES (4,2,2)
---dbo.psfeetablemstr
INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc])
VALUES (1,'dbo.client','income','dbo.sometable','dependents')
INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc])
VALUES (2,'dbo.sometable','dependents',NULL,NULL)
---dbo.psfeetable
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (1,0,5000,1,1,10,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (2,5001,10000,1,1,20,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (3,10001,20000,1,1,30,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (4,20001,50000,1,1,40,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (5,50001,9999999,1,1,50,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (6,0,5000,2,3,7,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (7,5001,10000,2,3,17,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (8,10001,20000,2,3,27,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (9,20001,50000,2,3,37,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (10,50001,9999999,2,3,47,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (11,0,5000,4,999,5,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (12,5001,10000,4,999,15,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (13,10001,20000,4,999,25,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (14,20001,50000,4,999,35,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (15,50001,9999999,4,999,45,1)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (16,1,1,0,0,10,2)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (17,2,3,0,0,20,2)
INSERT INTO [dbo].[psfeetable] ([uniqueid], [value1min_n], [value1max_n], [value2min_n], [value2max_n], [value3_n], [psfeetablemstrid])
VALUES (18,4,999,0,0,30,2)
From this for clientfee with uniqueid 1 I need psfeetable row with uniqueid 9 returned
Reason: clientfee row with uniqueid 1 for client 1 uses psfeetablemstr uniqueid 1. psfeetablemstr uniqueid 1 row is based on income and dependents. client 1 income and dependents fall into the min/max ranges of psfeetable row 9 which is linked to psfeetablemstr uniqueid 1
From this for clientfee with uniqueid 2 I need psfeetable row with uniqueid 17 returned
Reason: clientfee row with uniqueid 2 for client 1 uses psfeetablemstr uniqueid 2. psfeetablemstr uniqueid 2 row is based on dependents, client 1 dependents fall into the min/max ranges of psfeetable row 17 which is linked to psfeetablemstr uniqueid 2
From this for clientfee with uniqueid 3 I need psfeetable row with uniqueid 2 returned
Reason: clientfee row with uniqueid 3 for client 2 uses psfeetablemstr uniqueid 1. psfeetablemstr uniqueid 1 row is based on income and dependents. client 2 income and dependents fall into the min/max ranges of psfeetable row 2 which is linked to psfeetablemstr uniqueid 1
From this for clientfee with uniqueid 4 I need psfeetable row with uniqueid 16 returned
Reason: clientfee row with uniqueid 4 for client 2 uses psfeetablemstr uniqueid 2. psfeetablemstr uniqueid 2 row is based on dependents, client 2 dependents fall into the min/max ranges of psfeetable row 16 which is linked to psfeetablemstr uniqueid 2
All of this given that you only know dbo.client and dbo.sometable exist and the columns income and dependents exist by what is stored in psfeetablemstr
Here's Bruce.T's problem with sample data.
I've been working on it, but haven't really gotten anywhere.
April 21, 2009 at 1:47 am
Lynn Pettis (4/20/2009)
Instead of searching through 300+ posts, could we get bruce t's problem posted to a new forum thread and a link added here for anyone interested to follow to that thread? I think it would help, and I would at least look at it again, and possibly see what I could do with it.Thanks.
Jeff Moden (4/20/2009)
I may yet give 'er a "yank", but I've got a lot of other things going. Haven't really looked at the problem deeply, yet.For those setup with 50 posts per page, Bruce.T's problem is on page 3 about half way down.... somewhere around post 125 or so.
I'd be quite up for doing a team effort again. If, between us, we can actually get around this without using a CURSOR, then I think that would be fantastic example to link to from Part 2 of the article...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 15 posts - 286 through 300 (of 380 total)
You must be logged in to reply to this topic. Login to reply