Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > BOOK: Beginning SQL
| Search | Today's Posts | Mark Forums Read
BOOK: Beginning SQL
This is the forum to discuss the Wrox book Beginning SQL by Paul Wilton, John Colby; ISBN: 9780764577321
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning SQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 1st, 2009, 11:48 AM
Authorized User
 
Join Date: Jun 2009
Posts: 66
Thanks: 22
Thanked 0 Times in 0 Posts
Default Need Help in SQL Query

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.

Last edited by skhan; September 1st, 2009 at 12:49 PM..
 
Old September 1st, 2009, 12:05 PM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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.
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
 
Old September 1st, 2009, 12:19 PM
Authorized User
 
Join Date: Jun 2009
Posts: 66
Thanks: 22
Thanked 0 Times in 0 Posts
Default

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.
 
Old September 1st, 2009, 12:39 PM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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'
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
The Following User Says Thank You to om_prakash For This Useful Post:
skhan (September 1st, 2009)
 
Old September 1st, 2009, 12:54 PM
Authorized User
 
Join Date: Jun 2009
Posts: 66
Thanks: 22
Thanked 0 Times in 0 Posts
Default

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.
 
Old September 1st, 2009, 01:59 PM
Authorized User
 
Join Date: Jun 2009
Posts: 66
Thanks: 22
Thanked 0 Times in 0 Posts
Default

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 September 1st, 2009, 07:33 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old September 1st, 2009, 07:44 PM
Authorized User
 
Join Date: Jun 2009
Posts: 66
Thanks: 22
Thanked 0 Times in 0 Posts
Default

Unfortunately I am using Access DB :(
But thank you for your helpful suggestion:) Hate the person who started the application with Access DB........ :(
 
Old September 1st, 2009, 08:52 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
The Following User Says Thank You to Old Pedant For This Useful Post:
skhan (September 1st, 2009)
 
Old September 1st, 2009, 10:04 PM
Authorized User
 
Join Date: Jun 2009
Posts: 66
Thanks: 22
Thanked 0 Times in 0 Posts
Default

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..... :)




Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query i need seearam MySQL 7 November 30th, 2008 03:14 AM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
Help with SQL query sattaluri Access 2 August 11th, 2006 09:26 AM
SQL query PinkyCat Classic ASP Databases 3 March 11th, 2005 01:41 PM
Help with SQL Query jammykam SQL Language 4 October 19th, 2003 07:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.