SQL Statement Help

[cwwilly]

Hello,

Thank you in advance for taking a look at this.

Here is my SQL statement:

Set rsAdverseEventsMax = objConn.Execute("SELECT tblAdverseEventsID,
PatientID, SubmittedBy, SubmittedDTS, ModBy, ModDTS FROM
tblAdverseEvents WHERE PatientID='" & Request("PatientID") & "' AND
ModDTS=(SELECT MAX(ModDTS) FROM tblAdverseEvents WHERE PatientID='" &
Request("PatientID") & "') GROUP BY SubmittedDTS")

I want to run a loop which will display distinct SubmittedDTS values
while still displaying the MAX(ModDTS). I can't include DISTINCT in
my statement, since it will apply to all fields. How can I get it to
look at only one field ?Any help would be much appreciated.

Chad
[email]cwilliamson@spirationinc.com[/email]

[Bob Barrows]

cwwilly wrote:

> Hello,
>
> Thank you in advance for taking a look at this.
>
> Here is my SQL statement:
>
> Set rsAdverseEventsMax = objConn.Execute("SELECT tblAdverseEventsID,
> PatientID, SubmittedBy, SubmittedDTS, ModBy, ModDTS FROM
> tblAdverseEvents WHERE PatientID='" & Request("PatientID") & "' AND
> ModDTS=(SELECT MAX(ModDTS) FROM tblAdverseEvents WHERE PatientID='" &
> Request("PatientID") & "') GROUP BY SubmittedDTS")
>
> I want to run a loop which will display distinct SubmittedDTS values
> while still displaying the MAX(ModDTS). I can't include DISTINCT in
> my statement, since it will apply to all fields. How can I get it to
> look at only one field ?Any help would be much appreciated.
>
> Chad
> [email]cwilliamson@spirationinc.com[/email]

Please assign your sql statement to a variable, response.write it and copy
and paste the result into your reply to this post so we can see your actual
sql statement instead of the vbscript code that is supposed to result in a
sql statement.

Could you show us a few rows of data from the table (in tabular format),
followed by the results you wish to obtain from that sample data (again, in
tabular format). That will greatly help us understand your problem.

Also, let us know the type and version of the database you are using. It's
relevant.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Chad W]

Thanks for such a quick response. I really appreciate it ! This is
what you asked for...

Response.Write Results:

SELECT tblAdverseEventsID, PatientID, SubmittedBy, SubmittedDTS, ModBy,
ModDTS FROM tblAdverseEvents WHERE PatientID='' AND ModDTS=(SELECT
MAX(ModDTS) FROM tblAdverseEvents WHERE PatientID='')

Database:

SQL Server 2000

Data in Table:

SubmittedBy SubmittedDTS ModBy ModDTS
cwilliamson 2/13/2004 11:20:08 AM cwilliamson 2/13/2004 11:20:08 AM
cwilliamson 2/13/2004 11:20:08 AM cwilliamson 2/13/2004 11:36:18 AM
cwilliamson 2/13/2004 3:14:19 PM cwilliamson 2/13/2004 3:14:19 PM

Final Results:

cwilliamson 2/13/2004 11:20:08 AM cwilliamson 2/13/2004 11:36:18 AM
cwilliamson 2/13/2004 3:14:19 PM cwilliamson 2/13/2004 3:14:19 PM

To display the distinct SubmittedDTS and also display which has the most
rescent ModDTS.

Thank You !

Chad





*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Chad W wrote:

> Thanks for such a quick response. I really appreciate it ! This is
> what you asked for...
>
> Response.Write Results:
>
> SELECT tblAdverseEventsID, PatientID, SubmittedBy, SubmittedDTS,
> ModBy, ModDTS FROM tblAdverseEvents WHERE PatientID='' AND
> ModDTS=(SELECT MAX(ModDTS) FROM tblAdverseEvents WHERE PatientID='')
>
> Database:
>
> SQL Server 2000
>
> Data in Table:
>
> SubmittedBy SubmittedDTS ModBy ModDTS
> cwilliamson 2/13/2004 11:20:08 AM cwilliamson 2/13/2004 11:20:08 AM
> cwilliamson 2/13/2004 11:20:08 AM cwilliamson 2/13/2004 11:36:18 AM
> cwilliamson 2/13/2004 3:14:19 PM cwilliamson 2/13/2004 3:14:19 PM
>
> Final Results:
>
> cwilliamson 2/13/2004 11:20:08 AM cwilliamson 2/13/2004 11:36:18 AM
> cwilliamson 2/13/2004 3:14:19 PM cwilliamson 2/13/2004 3:14:19 PM
>
> To display the distinct SubmittedDTS and also display which has the
> most rescent ModDTS.
>
> Thank You !

Will this do it (I have not tested this)?

SELECT SubmittedBy, SubmittedDTS, ModBy,LatestMod
FROM tblAdverseEvents t1 Inner Join
(SELECT SubmittedBy, SubmittedDTS,
Max(ModDTS) As LatestMod
FROM tblAdverseEvents t2
GROUP BY SubmittedBy, SubmittedDTS) qLatest
ON t1.SubmittedBy = qLatest.SubmittedBy AND
t1.SubmittedDTS = qLatest.SubmittedDTS AND
t1.ModDTS = qLatest.LatestMod
WHERE PatientID=''

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Chad W]

Thank You so much for your help !

I'm receiving the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
'SubmittedBy'.

Chad

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Chad W wrote:

> Thank You so much for your help !
>
> I'm receiving the following error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
> 'SubmittedBy'.
>
> Chad
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

Yes, both data sources have that field. You need to explicitly qualify the
field names in the SELECT clause. Change it to:
SELECT t1.SubmittedBy, t1.SubmittedDTS,
ModBy,LatestMod
FROM tblAdverseEvents t1 Inner Join
(SELECT SubmittedBy, SubmittedDTS,
Max(ModDTS) As LatestMod
FROM tblAdverseEvents t2
GROUP BY SubmittedBy, SubmittedDTS) qLatest
ON t1.SubmittedBy = qLatest.SubmittedBy AND
t1.SubmittedDTS = qLatest.SubmittedDTS AND
t1.ModDTS = qLatest.LatestMod
WHERE PatientID=''


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Chad S]

Hello,

Thank you for taking the time to help me out with this.

I have two questions for you:

1. I have a drop-down in a form which I want to display a set of
locations. But I only want to show the locations which are
implanted='yes' AND PatientID=Request("PatientID") AND
MAX(SubmittedDTS). Each location can show up multiple times in the
database, but I only want it with the criteria above. When I throw in
the MAX(SubmittedDTS) it only evaluates, of course, the overall MAX but
I want it grouped by location.

Sample Data:
Line PatientID Location Implanted SubmittedDTS
1 1 1 Yes 04/08/2004 5:13:00PM
2 1 1 No 04/08/2004 6:13:00PM
3 1 1 Yes 04/08/2004 6:15:00PM
4 1 2 Yes 04/08/2004 6:14:00PM
5 2 1 Yes 05/08/2004 5:07:00AM

Output:
In the drop down I want the following from the data above with the
PatientID=1:
1 <= From Line 3
2 <= From Line 4

Current SQL Statement:
SELECT DISTINCT tblDeviceImplantID, PatientID, FK_ProceduralInfoID,
Location, Implanted FROM tblDeviceImplant WHERE Implanted='Yes' AND
PatientID='" & Request("PatientID") & "'")

2. My second question is once a location from the dropdown is used can
I have it automatically removed from the dropdown for later submissions?

I have two tables 1. tblDeviceImplant, where I pull the locations for
the dropdown and 2. tblDeviceObservations, where if a location is used
in this table remove it from the dropdown. I don't want to remove it
from the database. I only want to remove it from the dropdown.

Thanks so much ! Any thoughts would be much appreciated.

Chad


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Maarten]

See DISTINCT or GROUP BY in your Docs

[Aaron Bertrand [MVP]]

> 1. I have a drop-down in a form which I want to display a set of

> locations. But I only want to show the locations which are
> implanted='yes' AND PatientID=Request("PatientID") AND
> MAX(SubmittedDTS). Each location can show up multiple times in the
> database, but I only want it with the criteria above. When I throw in
> the MAX(SubmittedDTS) it only evaluates, of course, the overall MAX but
> I want it grouped by location.

sql = "SELECT Location, MAX(SubmittedDTS) " & _
" FROM tblDeviceImplant " & _
" WHERE Implanted='Yes' " & _
" AND PatientID = " & Request("PatientID") & _
" GROUP BY Location"

A couple of suggestions:

(1) PatientID should not be a character data type. If it is, rename it,
because IDs almost universally refer to numbers. If it is numeric, then
remove the single quotes from your SQL statement, this will cause a data
type mismatch error.

(2) Use Request.Form("") or Request.QueryString(""). Do not be lazy and use
just Request(""). See [url]http://www.aspfaq.com/2111[/url]

(3) You should validate your input. Make sure that PatientID is numeric,
for example. If it can be character-based, then make sure you replace all '
with '' to prevent string truncation problems, and more importantly, SQL
injection.

> 2. My second question is once a location from the dropdown is used can
> I have it automatically removed from the dropdown for later submissions?

You will need to keep track of this via a session (if it is only for that
specific user) or in the database (if it is for all users). Maybe you could
add a column to the table to track that...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Aaron Bertrand [MVP]]

In addition, PLEASE always tell us what database product and version you are
using, so we don't have to guess...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]