Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-10-25 : 18:51:37
|
I have a table looks like belowID HGB EPO Y_N ---------- ---------------------- ---------------------- --- 1001 14 5000 1001 4800 1001 4900 1001 13 4000 1001 4200 1001 14 4000 1001 4200 1001 4100 1002 14 4000 1002 4200 1002 4100 1002 14 5000 1002 4800 1002 4100 I have a complex logic that I need to use to populate the Y_N value1) I need to loop thru the records in the same ID and if HGB is greater than 13 than start the comparison using the logic. But, If the HGB is < 13 skip the lows until you see the next available and move to the next available HGB in the same PID..2) In this example, the fist HGB value of ID1001 is 14, so it’s valid for the loop comparison. As far as the comparison, you basically take the value of EPO which has the HGB and compare that number to the other EPOs which doesn’t have the HGB value…For the first comparison,I have to compare 5000 to 4800 and 4900. Because HGB value of 5000 is > 14 and it’s in the same ID.If the record is less than the number that has HGB value then put Y in Y_N column and if its greater, then Put NOAnd Move to the next until you find the next available HGB in the same ID.In this case it’s 13, 13 is less then 14 so we just skip the rows until you see the next available HGB in the same ID.. For this example, the next available HGB is 14 and it’s EPO value is 4000, so we start doing a comparison. Both 4200 and 4100 are greater than 4000, so we put N in the Y_N column.. And Go to the next... in this example go to the next ID... and apply the same logicSo the ideal result should look like:ID HGB EPO Y_N ---------- ---------------------- ---------------------- --- 1001 14 5000 Y1001 4800 Y1001 4900 Y1001 13 4000 1001 4200 1001 14 4000 N1001 4200 N1001 4100 N1002 14 4000 N 1002 4200 N1002 4100 N1002 14 5000 Y1002 4800 Y1002 4100 Y How can I do this? Can you show me some code examples? Can I do this in set based query or do I have to use a cursor?to create a test data:Here is DDL CREATE TABLE "RA"."TEST" ("ID" VARCHAR2(10 byte) NOT NULL, "HGB" NUMBER(10), "EPO" NUMBER(10), "Y_N" CHAR(1 byte)) and here is DML insert into ra.test(Id,HGB, EPO) values ( 1001, 14, 5000);insert into ra.test(Id, EPO) values ( 1001, 4800);insert into ra.test(Id, EPO) values ( 1001, 4900);insert into ra.test(Id,HGB, EPO) values ( 1001, 13, 4000);insert into ra.test(Id, EPO) values ( 1001, 4200);insert into ra.test(Id,HGB, EPO) values ( 1001, 14, 4000);insert into ra.test(Id, EPO) values ( 1001, 4200);insert into ra.test(Id, EPO) values ( 1001, 4100);insert into ra.test(Id,HGB, EPO) values ( 1002, 14, 4000);insert into ra.test(Id, EPO) values ( 1002, 4200);insert into ra.test(Id, EPO) values ( 1002, 4100);insert into ra.test(Id,HGB, EPO) values ( 1002, 14, 5000);insert into ra.test(Id, EPO) values ( 1002, 4800);insert into ra.test(Id, EPO) values ( 1002, 4100);Message was edited by: jung1975 |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-10-25 : 18:57:16
|
Actually this is the desired outout.. it doesnt have to put Y/N on the row that has HGB value,,, ID HGB EPO Y_N ---------- ---------------------- ---------------------- --- 1001 14 5000 1001 4800 Y1001 4900 Y1001 13 4000 1001 4200 1001 14 4000 1001 4200 N1001 4100 N1002 14 4000 N 1002 4200 N1002 4100 N1002 14 5000 Y1002 4800 Y1002 4100 Y |
 |
|
|
samuelclay
Yak Posting Veteran
71 Posts |
Posted - 2006-10-26 : 11:47:12
|
| Well, an initial problem I see is that you cannot guarantee the ordering of the list with the structure you have. So even with a cursor, you couldn't cycle through and expect to get the results you are looking for because you wouldn't know for certain if you were on the proper line for comparing with previous data. |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-10-26 : 15:12:24
|
| Dont you have some kind of timestamp for the results?... and are you trying to bash some naughty bicycle riders ?-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter." |
|
|
|
|
|
|
|
|
Query challenge - complex loop logic