Complicated query

From: Just D (no_at_spam.please)
Date: 07/21/04 

Date: Tue, 20 Jul 2004 19:52:56 -0700

Hi,

The task is so hard that I even don't know how to start explaining it...(
Let try to write a schema step by step.

We have two basic tables (and a few related subtables) - with questions and
answers. The tblQuestions database table is having the following fields
(simplified):

QID [int, PK]
QTopic [varchar 50]
QText [varchar 50]
Enabled [bit, NOT NULL]
Description [varchar 250]
Term [int] - Validity in months from the current date.
...

The table tblAnswers has the following structure (simplified):

AnswerID [int, PK, NOT NULL]
PtID [int, NOT NULL] - PatientID
RespDate [smalldatetime, NOT NULL, GetDate()] - the Response date
TextAnswer [varchar, not so important now]
BitAnswer [bigint, bitmask, not so important now]
...

The answer supposed to be saved correctly if at least one of these last
fields has a value.

The problems are to write a query to get the question list for a patient
with a some defined PtID. The query should evaluate the patient's answers so
that if the question is already having the answer saved inside the Validity
period:
(GETDATE() - Answer.RespDate < Question.Validity)
should be ignored. Another one problem - we're having many generations of
answers and we need to evaluate the answers inside some defined time period:
(WHERE RespDate<SomeDate)
keeping in mind the Validity interval.

The main rule - if the answer is already saved inside the Validity interval
the question should be ignored, if not - we need to ask this question and
save a new answer with the current DateTime. The previous answer should not
be updated or replaced, we just need to save a new one.

Second problem - we need to show the answers for defined DateTime receiving
all valid answers for current date.

The tasks are even more complicated because we don't need always to ask all
questions and the question list should be customized for a current patient
condition. It's like a bitmask and this WHERE can be gotten using a simple
bit-related query. It's easy.

Maybe anybody knows how to create such types of queries, if so, we'd
probably can discuss more details of this terrible task if anything's not
clear.

Thanks,
Dmitri

Relevant Pages

  • Re: HowTo: Append Data to a table stored in another database?
    ... ID int ... Name varchar ... What I need is a query that checks if the ID is in table b or not - if not, ... >> I have database A with table a and I have database b. ...
    (microsoft.public.sqlserver.server)
  • How to compare two Text fields using a query?
    ... an Id field as int and a Description field as ... I want to compare the Description fields with a query as ... to downcast a text field to a varchar since my data is ... larger than 8000 characters. ...
    (microsoft.public.sqlserver.programming)
  • RE: Query help (JOIN on own table)
    ... > id int not null, ... > source varchar not null ... > 1 testOne One ... > I've managed to it with the query at the bottom, but I'm not too happy about ...
    (microsoft.public.sqlserver.programming)
  • Return Hierarchical Data From One Table (with a JOIN to a second table)
    ... I would appreciate help in designing an efficient query that will retrieve ... [SequenceInRank] ... - specifies which row is the logical parent of the ...
    (microsoft.public.sqlserver.programming)
  • [GIT PULL] post-2.6.17-rc1 fixes
    ... Wait for join to finish before freeing mcast struct ... int ret; ... Otherwise it is a query ID that can be used to cancel ...
    (Linux-Kernel)