Datetime and validity
-
Hello all,
I have the following situation: in a nursing home meals are served to patients (guests). An allergy (table "Allergy" with allergy_ID) can be assigned to a certain guest for a specific day (table "Assignment" with allergy_ID, patient_ID and valid_from) and is valid until the next assignment. Let's say John Smith is allergic to peanuts from 4/18/2007 on. On 4/25/2007 he is assigned an additional allergy e.g. dairy products. His allergy to peanuts is valid from 4/18/2007 until 4/25/2007 when the new composite allergy is valid from. There are 3 tables involved: Allergy, Patient and Assignments. Allergy has only allergy names and IDs, Patient has patient names and IDs and all assignments are recorded in the Assignments table in following fashion:
REC_ID----PAT_ID---- ALERG_ID----VALID_FROM
1 101 1 4/18/2007----->(John Smith allergic to peanuts from 4/18)
2 101 1 4/25/2007----\
>(John Smith allergic to peanuts & dairy from 4/25)
3 101 2 4/25/2007----/
How can I make a SQL query that tells me that on 4/20 he was allergic only to peanuts (allergy ID 1)?
Many thanks for any hints...
-
This is probably not the best way to do this, but you could do something similar to the following:
select top 1 alerg_id from assignments
where valid_from < '4/20/2007' -- or your date variable here
order by valid_from desc
The result set (without the top 1) should return all assignments that were previously in effect by the date you specify, but the order descending (in conjuction w/the top 1) will give you the latest one (and the one that should be currently in effect)...
-
Select * from dbo.TableName where valid_from <= @Date
The top 1 would limit the results to one row, which is most likely not what is wanted in this case.
-
Please post the link here for further referrences.
-
Only one allergy can be applicable at any time? Do you stop being allergic to peanuts once you become allergic to milk?
-
Hello Allyn and Ninja,
thanks for your replies. An allergy is valid only until the next assignment:
1. Allergy to peanuts assigned on 4/18 (valid from 4/18)
2. Additional allergy to dairy products assigned on 4/25 (allergy to peanuts & dairy as a composite allergy valid from 4/25)
You could say that allergy "packages" are assigned (consisting of one or more allergies). So in my case, if I wanted to know to what the allergy was on 4/20, query should return just one record (peanuts) whereas on 4/25 (or later) it should return peanuts & dairy (two records).
Regards,
marin
-
Select * From dbo.tbl tbl inner join (
Select Patient_id, Max(Valid_from) from dbo.tbl where Patient_id = @SomeID group by Patient_id) dtDate
on tbl.Patien_id = dtDate.Patient_id and tbl.Valid_from = dtDate.Valid_from
-
see if this doesn't help
--------------------start code---------------------------
set nocount on
DECLARE @x table (REC_ID int, PAT_ID int, ALERG_ID int, VALID_FROM datetime)
INSERT @x Values(1,101,1,'20070318')
INSERT @x Values(2,101,1,'20070418')
INSERT @x Values(3,101,1,'20070425')
INSERT @x Values(4,101,2,'20070425')
INSERT @x Values(5,102,2,'20070726')
INSERT @x Values(6,102,2,'20071221')
INSERT @x Values(7,102,2,'20070326')
INSERT @x Values(8,102,3,'20070326')
/*
REC_ID----PAT_ID---- ALERG_ID----VALID_FROM
1 101 1 4/18/2007----->(John Smith allergic to peanuts from 4/18)
2 101 1 4/25/2007----
>(John Smith allergic to peanuts & dairy from 4/25)
3 101 2 4/25/2007----/
*/
select PAT_ID, ALERG_ID, MAX(VALID_FROM) VALID_FROM FROM @x WHERE PAT_ID = 101 AND VALID_FROM <= '20070420' GROUP BY PAT_ID, ALERG_ID
select PAT_ID, ALERG_ID, MAX(VALID_FROM) VALID_FROM FROM @x WHERE PAT_ID = 101 AND VALID_FROM <= '20070520' GROUP BY PAT_ID, ALERG_ID
---------------------end code----------------------------
Note this assumes you make a duplicate entry every time for existing alergies when you add a new one.
Also just in case you want to see first occurrance you could (this assumes an alergy is considered permanent.)
--------------------start code---------------------------
select PAT_ID, ALERG_ID, MIN(VALID_FROM) VALID_SINCE FROM @x WHERE PAT_ID = 101 AND VALID_FROM <= '20070420' GROUP BY PAT_ID, ALERG_ID
select PAT_ID, ALERG_ID, MIN(VALID_FROM) VALID_SINCE FROM @x WHERE PAT_ID = 101 AND VALID_FROM <= '20070520' GROUP BY PAT_ID, ALERG_ID
---------------------end code----------------------------
and of course you could combine them.
--------------------start code---------------------------
select PAT_ID, ALERG_ID, MAX(VALID_FROM) VALID_FROM, MIN(VALID_FROM) VALID_SINCE FROM @x WHERE PAT_ID = 101 AND VALID_FROM <= '20070420' GROUP BY PAT_ID, ALERG_ID
select PAT_ID, ALERG_ID, MAX(VALID_FROM) VALID_FROM, MIN(VALID_FROM) VALID_SINCE FROM @x WHERE PAT_ID = 101 AND VALID_FROM <= '20070520' GROUP BY PAT_ID, ALERG_ID
---------------------end code----------------------------
-
Hello Antares, Ninja and Allyn,
thank you very much for your assistance. I will surely give it a try with your suggestions.
Many greetings,
Marin
-
The fundamental problem here is a design problem. If you combine each combination of allergies into a new row, it will result in an excessive number of combinations. For example, with only 3 allergies in your allergy table, you have the following combinations:
1
2
3
1,2
1,3
2,3
1,2,3
The exponential growth will cause performance problems due to the redundant storage of data. If instead you added a column for "EndDate" (Normalize the data), you could then query for any medicines that were valid between a given "StartDate" and "EndDate".
-
Jeff, can you appoint "End Date" for your medical condition?
You must have very good connections up there to do it.
Most people don't.
That's why they take "End Date" and a date of next assessment when that condition is not discovered anymore.
This design makes perfect sense for health related database.
_____________
Code for TallyGenerator -
I was thinking to store the end date for historical purposes. In case you found that one of your previous "allergies" was misdiagnosed and no longer relevant, you have access to that information - which could help explain why you were given a certain prescription that otherwise wouldn't make sense. Perhaps I am too far removed from your industry.
I guess the best decision about whether to store a table of combinations of allergies versus storing one row for each patient allergy combination probably depends upon the number of potential combinations. As few as 10 allergies can have millions of combinations which would seemingly be better stored in a PatientAllergy table - as in a traditional many:many relationship. If you are dealing with millions of patients this may be an acceptable trade-off. It seems to introduct complex logic to dynamically pair up different allergies, whereas the other approach does this for you automatically:
'Patient1', 'Allegic to Dogs, Cats, Shellfish'
'Patient2,'Allergic to Cats,Shellfish'
'Patient3','Allergic to Shellfish'
Versus
Patient1,shellfish
Patient1,Dogs
Patient1,Cats
Patient2,Cats
Patient2,Shellfish
Patient3,Shellfish
How do you guarantee the difference between 'Allegic to Dogs, Cats, Shellfish' and 'Allegic to Dogs, Shellfish, Cats'?
Sorry, just trying to help.
-
I don't see any difference between 'Allegic to Dogs, Cats, Shellfish' and 'Allegic to Dogs, Shellfish, Cats'?
So, there not gonna be any cobinations.
Every assessment sets some current conditions.
After every assessment old conditions become irrelevant.
So, the second option from your post (which is the original design) is absolutely right. Because it reflects the real world situation.
EndDate for historical purposes could be set up, but it could be nothing else but result of aggregation query to actual data tables.
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply