Need Help in SQL Query

skhan · September 1st, 2009, 11:48 AM

Hello,
I am fairly new in writing SQL query.....
I have a database table as the following.

re_MLNO re_Date HIV1Status HIV2Status DeadStatus
ML0006 18-Feb-85 0 0
ML0006 4-Apr-85 0 0
ML0006 4-Apr-86 0 0
ML0006 11-Nov-86 1 1 0
ML0006 12-Jan-87 1 1 0
ML0007 28-Mar-85 1 1 0
ML0007 12-Jan-87 1 1 0
ML0007 3-Jul-87 1 1 0
ML0007 16-Nov-89 1 1 0
ML0008 28-Mar-85 1 1 9
ML0008 19-Feb-87 1 1 9
ML0008 30-Jul-87 1 1 9
ML0008 3-Sep-87 1 1 9
ML0009 18-Feb-85 0 9
ML0009 12-Jan-87 0 9
ML0010 18-Feb-85 0 0
ML0010 12-Jan-87 1 1 0
ML0010 12-Feb-87 1 1 0
ML0010 27-Mar-87 1 1 0
ML0010 17-Aug-87 1 1 0
ML0010 17-Sep-87 1 1 0
ML0010 6-Mar-91 1 1 0

Now I need the last Negative Date(highlighted in red) and the 1st positive date(highlighted in blue) for a single MLNO(which is the patient number). In the same query I also need the first experiment date(highlighted in Maroon) and the last experiment date(highlighted in Maroon) for a single MLNO.

Please help. I am new to this kind of things so I am sorry if this is a wrong forum and I am sorry if I have asked too much....and thanks in advance for your time.

om_prakash · September 1st, 2009, 12:05 PM

How do you identify the following:
last Negative Date(highlighted in red)
1st positive date(highlighted in blue)
the first experiment date(highlighted in Maroon)
last experiment date(highlighted in Maroon)
for a single MLNO.

skhan · September 1st, 2009, 12:19 PM

Thanks for your attention.

last Negative Date(highlighted in red) - Is the last date where HIV_Status1 was ="0" for a single MLNO (for example for ML0006 it is 4-Apr-86)

1st positive date(highlighted in blue) - Is the first date where HIV_Status1 was ="1" for a single MLNO (for example for ML0006 it is 11-Nov-86 )

the first experiment date(highlighted in Maroon) is the first date when patient ML0006 was tested (in this case 18-Feb-85,It is actually the first time the patient was entered into the database, ie. the first row containing ML0006)

last experiment date(highlighted in Maroon) is the last date when patient ML0006 was tested (in this case 12-Jan-87,It is actually the last time the patient was entered into the database, ie. the last row containing ML0006)

HivStatus =0 means the patient is negative, and HivStatus=1 means the patient is positive.

I hope this clarifies things a little bit.

om_prakash · September 1st, 2009, 12:39 PM

You can do something like (although this is not the best way to do it):

Code:

SELECT 
(SELECT MAX(re_DATE) FROM TABLE_NAME t1 WHERE HIV_Status1 = 0 t1.re_MLNO = T.re_MLNO) [last Negative Date],
(SELECT MIN(re_DATE) FROM TABLE_NAME t2 WHERE HIV_Status1 = 1 AND t2.re_MLNO = T.re_MLNO) [column name2],
(SELECT MIN(re_DATE) FROM TABLE_NAME t3 WHERE t3.re_MLNO = T.re_MLNO) [column name3],
(SELECT MAX(re_DATE) FROM TABLE_NAME t4 WHERE t4.re_MLNO = T.re_MLNO) [column name4]
FROM 
TABLE_NAME T WHERE re_MLNO = 'ML0006'

skhan · September 1st, 2009, 12:54 PM

My table name is ml_hiv_status, and I am currently running the following query to extract the first experiment date and the last experiment date:

Code:

SELECT DISTINCT re_MLNO, 
MIN(re_Date) AS First_Resurvey_Date, 
MAX(re_SpecimenDate) AS Last_Resurvey_Date 
FROM ml_hiv_status 
WHERE (re_MLNO = 'MLL0006' AND HIV1_Status=1 AND re_HIV2_Status=1) GROUP BY re_MLNO";

which give me the first experiment date and the last experiment date for Patient no ML0006.

Now I will have to incorporate the one that you have suggested into this query.

Thanks much.

skhan · September 1st, 2009, 01:59 PM

Code:

SELECT DISTINCT re_MLNO, MAX(re_SpecimenDate) AS Last_Negative_Date, 
MIN(re_SpecimenDate) AS First_Resurvey_Date 
FROM ml_hiv_status 
WHERE (re_MLNO = '" + selected_MLNO + "' AND re_HIV1_Status=0) 
GROUP BY re_MLNO 

UNION ALL 

SELECT DISTINCT re_MLNO, MIN(re_SpecimenDate) AS First_Positive_Date, 
MAX(re_SpecimenDate) AS Last_Resurvey_Date 
FROM ml_hiv_status 
WHERE (re_MLNO = '" + selected_MLNO + "' AND re_HIV1_Status=1) 
GROUP BY re_MLNO";

The above code gives me the desired rows and columns but the UNION ALL makes them as follows:

--------------------------------------------------------------------------------
ML0006 | Last_Negative_Date | First_Resurvey_Date |
--------------------------------------------------------------------------------
ML0006 | First_Positive_Date | Last_Resurvey_Date |
--------------------------------------------------------------------------------

But I want all the dates to be in one row, not grouped under 2 columns....

Any suggestion?

Old Pedant · September 1st, 2009, 07:33 PM

Completely UNTESTED, of course.

But try this:

Code:

SELECT Q1.re_LMNO, Q1.Last_Negative_Date, Q1,First_Resurvey_Date,
       Q2.First_Positive_Date, Q2.Last_Resurvey_Date
FROM 
  ( SELECT re_MLNO, MAX(re_SpecimenDate) AS Last_Negative_Date, 
           MIN(re_SpecimenDate) AS First_Resurvey_Date 
    FROM ml_hiv_status 
    WHERE re_MLNO = @MLNO AND re_HIV1_Status=0
    GROUP BY re_MLNO 
  ) AS Q1,
  ( SELECT re_MLNO, MIN(re_SpecimenDate) AS First_Positive_Date, 
          MAX(re_SpecimenDate) AS Last_Resurvey_Date 
    FROM ml_hiv_status 
    WHERE re_MLNO = @MLNO AND re_HIV1_Status=1
    GROUP BY re_MLNO
  ) AS Q2
WHERE Q1.re_MLNO = Q2.re_MLNO
ORDER BY Q1.re_MLNO

Unless you are using an Access DB, that should work. If you are using Access, we can still make it work; just have to create VIEWs first.

NOTE: No reason for DISTINCT in those subqueries. The GROUP BY should take care of that.

skhan · September 1st, 2009, 07:44 PM

Unfortunately I am using Access DB :(
But thank you for your helpful suggestion:) Hate the person who started the application with Access DB........ :(

Old Pedant · September 1st, 2009, 08:52 PM

No problem!

It's easy to fix. Honest.

Do it in steps. FIrst, bring up Access (the program), open up this database file, and create two STORED QUERIES.

That is, create a query in design view, switch to SQL View, and then type in (or copy/paste) this:

Code:

SELECT re_MLNO, MAX(re_SpecimenDate) AS Last_Negative_Date, 
       MIN(re_SpecimenDate) AS First_Resurvey_Date 
FROM ml_hiv_status 
WHERE re_HIV1_Status=0
GROUP BY re_MLNO

Save that query. Give it a memorable name. Such as "QueryMinMaxHiv0"

Then do the same for this query:

Code:

SELECT re_MLNO, MIN(re_SpecimenDate) AS First_Positive_Date, 
       MAX(re_SpecimenDate) AS Last_Resurvey_Date 
FROM ml_hiv_status 
WHERE re_HIV1_Status=1
GROUP BY re_MLNO

Save that query using a similar name. Let's say ""QueryMinMaxHiv1"

Now write this in your code (I assume ASP code?):

Code:

SQL = "SELECT Q1.re_LMNO, Q1.Last_Negative_Date, Q1,First_Resurvey_Date, " _
            " Q2.First_Positive_Date, Q2.Last_Resurvey_Date "
    & " FROM QueryMinMaxHiv0 AS Q1, QueryMinMaxHiv1 AS Q2 "
    & " WHERE Q1.re_LMNO = Q2.re_LMNO "
    & " AND Q1.re_LMNO = '" & lmno & "' "
 ...

I dunno why Access has trouble with inner SELECTs like that when it has no trouble at all if you convert them into stored queries ahead of time, but it does. But fortunately using stored queries virtually always works. Should work fine for this.

You can of course test it out ahead of time by using that final query within Access.

skhan · September 1st, 2009, 10:04 PM

Thank you Old Pedant. It worked like a charm in the MS Access Database. I am working with it in the application now. Thank you a bunch..... :)