February 13, 2002 at 1:02 pm
Say i have this table with 3 columns
F1, F2 , Observation#
Possible values are
XYZ 100 1
XYZ 100 2
XYZ 100 3
XYZ 101 1
XXA 100 1
XXA 100 2
XXA 101 1
XXA 102 1
XXA 102 2
.......
i want this observation# to be 1 if this is the fisrt instance of F1 and F2,, if not then increment the last observevation# for this F1,F2 pair and so on.
Any ideas on how this could be done ,, part of table design, trigger or ??
Thanks for your input.
February 13, 2002 at 1:16 pm
Not sure what you mean. Can you clarify a little more with some data examples.
Steve Jones
February 13, 2002 at 1:57 pm
I am collecting some patient data based on some unique survey,, a patient might report many side effects ,, i am calling these side effects observations ,, i want to have a count of these observations for reference ,, so looking back at the data i have patient XYZ who filled out survey 100 and had 3 events to report,, patient XXA for survey 100 had one observation while for survey 102 he/she had 2 events to report.
i hope it clears it somewhat.
Thanks
February 13, 2002 at 2:03 pm
You could accomplish your objective in an insert/update trigger, but you are setting yourself up for some maintenance headaches. Perhaps an alternative solution would be to just compute the count (count(*))of observations per survey for each patient when you run the report. Unless you have additional requirements for the individual numbering or "count incrementing" of the records, it should not be necessary to maintain this data.
February 13, 2002 at 3:06 pm
I agree with Sean, one of the biggest problems you will have is database performance has ths gets larger because to ensure data order and no reoccurrance of an Observation# for a given patient, you will have to lock the entire table while the insert occurres. There are other ways to view the data like you want without trying to manipulate the numbers like this. I would suggest a datetime field with a default value of GETDATE() or otherwise entered to keep them ordered and then run a query like this.
SELECT F1, F2, (SELECT COUNT(*) + 1 FROM myTbl iT WHERE iT.Observation# < oT.Observation# AND iT.F1 = oT.F1 AND iT.F2 = oT.F2) AS ObservOrdNum FROM myTbl oT
Which should if I did it right return an output exactly like the list you gave in your original post but that would be using DateTime field instead of numbers.
If you really have to do it the other way I would suggest then following. You will need to test as I don't have a server here to check:
INSERT INTO myTbl (F1, F2, Observation#) SELECT MyValForF1, MyValForF2, (COUNT(*) +1) AS ObserNum FROM myTbl (TABLOCK) where F1 = MyValForF1 AND F2 = MyValForF2
Note I insert a specific val for F1 and F2 and counted from the table the number of occurrances already + 1 (so if there are non you get 0 + 1 so 1) from the table where F1 and F2 already equal my values and I place a table lock (this may need to be checked as again I don't have a server right here) to prevent a person inserting a record for the same patient. Now if you are 100 % sure this will never happen then don't worry about the table lock as this will impact performance overall.
Hope this helps.
February 14, 2002 at 2:51 pm
To add more fuel to the fire, we do have an application group who did a similar method to what you are proposing. It did incur a performance issue, with relatively few users. That is one area where they've had to rearchitect their table design.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply