|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
October 29th, 2009, 12:02 PM
|
|||
|
|||
Query - General - A single query for many reports? - best practice
Hi,
I'm writing a query to hopefully return distinct results of several columns. I am needing to write 15 different reports on the same asp page, each of which based upon a different column's results of the same table. For example, report 1 will check to see if column "out1" is equal to 'n,' 'c,' or 'p'. Report 2 will check column "out2," and so on... Multiple rows can contain the same CourseID, and 'n,' 'c,' or 'p' can occur multiple times, hence the distinct. The table structure is: CourseID | Out1 | Out2 [so on...to Out15] My question is: can I write this as one query which finds distinct entries for each of the "Out..." columns and just display/filter the results through my asp coding, or will it be best to have a separate query for each report, as I've done below (for the "Out1" column)? I've tried doing a distinct with all of the Out... columns, but that doesn't work for my needs. Is there a way to somehow group each Out... column into a separate result? Many thanks for any help!  Code:
SELECT distinct
t.CourseID,
t.Out1,
t.Complete,
FROM
(
SELECT
AMS_Courses.CourseID,
AMS_ContentOverviewObjectiveOutcome.Out1,
AMS_ContentOverview.Complete,
FROM
AMS_ContentOverviewObjectiveOutcome INNER JOIN
AMS_Courses ON
AMS_ContentOverviewObjectiveOutcome.CourseID =
AMS_Courses.CourseID INNER JOIN
AMS_ContentOverview ON AMS_Courses.CourseID =
AMS_ContentOverview.CourseID
WHERE
Out1 <> 'n/a' and Out1 <> ''
)
t
;
|
|
#2
October 30th, 2009, 02:36 PM
|
|||
|
|||
|
Use variables. Use a variable for the Out columns. Build the SQL in a variable. Have a loop that increments 15 times to produce each report. Something like (is this an ASP question? - I don't know the line continuation character for ASP):
Code:
For i = 1 to 15 strSQL = "SELECT distinct t.CourseID, t.Out" & i & ", t.Complete, " & _ "FROM (SELECT " & _ "AMS_Courses.CourseID, " & _ "AMS_ContentOverviewObjectiveOutcome.Out1, " & _ "AMS_ContentOverview.Complete, " & _ "FROM " & _ "AMS_ContentOverviewObjectiveOutcome INNER JOIN " & _ "AMS_Courses ON " & _ "AMS_ContentOverviewObjectiveOutcome.CourseID = " & _ "AMS_Courses.CourseID INNER JOIN " & _ "AMS_ContentOverview ON AMS_Courses.CourseID = " & _ "AMS_ContentOverview.CourseID " & _ "WHERE " & _ "Out" & i & " <> 'n/a' AND Out" & i & "<> 't');" 'code to run report with strSQL Next Last edited by June7 : November 1st, 2009 at 11:30 PM.
|
|
#3
November 2nd, 2009, 02:17 PM
|
|||
|
|||
|
Many thanks! I'll give it a try.
Quote:
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Query - General - A single query for many reports? - best practice |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
