Report from a Database

[st3ady]

Hey there, I am really new to ASP and SQL

My boss gave me the task to do the following:

We have a huge database with a ton of records that specify:

Location, Specialty, Number, and other things. He wants me to create a report that displays

Location1 SpecialtyA Average Number for this specialty
----------SpecialtyB Average Number for this specialty
----------SpecialtyC Average Number for this specialty
Location2 SpecialtyA Average Number for this specialty
----------SpecialtyB Average Number for this specialty
----------SpecialtyC Average Number for this specialty
and so on...

So far I created a page in dreamweaver filled with a huge table with a ton of cells. After connecting the database, what do I type to have it spit out the average number? Something like Response.Write ? Thanks!

[russell]

<%
main

Sub main()
Dim ar

ar = getData()

If isArray(ar) Then
showReport ar
Else
Response.Write "No Data"
End If
End Sub

Sub showReport(ar)
Dim i

With Response
.Write "<table>" & vbCrLf
.Write "<tr>" & vbCrLf
.Write "<td>Location</td>" & vbCrLf
.Write "<td>Specialty</td>" & vbCrLf
.Write "<td>Avg</td>" & vbCrLf
.Write "</tr>" & vbCrLf

For i = 0 To Ubound(ar, 2)
.Write "<tr>" & vbCrLf
.Write "<td>" & ar(0, i) & "</td>" '' location
.Write "<td>" & ar(1, i) & "</td>" '' specialty
.Write "<td>" & ar(2, i) & "</td>" '' avg
.Write "</tr>" & vbCrLf
Next
.Write "<table>" & vbCrLf
End With
End Sub

Function getData()
Dim cmd
Dim sql
Dim rs

sql = "SELECT location, specialty, AVG(number_for_this_specialty) " &_
"FROM myTable " &_
"GROUP BY location, specialty " &_
"ORDER BY location, specialty"

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = YOUR_CONNECTION_STRING_HERE
.CommandType = 1 '' adCmdText
.CommandText = sql

rs.Open .Execute
End With

If Not rs.EOF Then
getData = rs.GetRows
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
End Function
%>

[st3ady]

You sir, are amazing. Wow. You are strong with the ways of ASP.
worked like a charm. I have some modifications I need to make, you know how those bosses always are... I think I will be able to figure them out on my own, but I may be back for more of your sweet sweet wisdom. Thank you very much Russell! :)

[russell]

I appreciate the kind words! Glad to help :)

[st3ady]

Ok so here is my code now:

<%
main

Sub main()
Dim ar

ar = getData()

If isArray(ar) Then
showReport ar
Else
Response.Write "No Data"
End If
End Sub

Sub showReport(ar)
Dim i

With Response
.Write "<table>" & vbCrLf
.Write "<tr>" & vbCrLf
.Write "<td><b>District</b></td>" & vbCrLf
.Write "<td><b>Call Load</b></td>" & vbCrLf
.Write "<td><b>Specialty</b></td>" & vbCrLf
.Write "<td><b>Avg of ActualSOS of May06</b></td>" & vbCrLf
.Write "<td><b>Avg of ActualSOS from Feb06 to May06</b></td>" & vbCrLf
.Write "</tr>" & vbCrLf

For i = 0 To Ubound(ar, 2)
.Write "<tr>" & vbCrLf
.Write "<td>" & ar(0, i) & "</td>" '' SVC_UN_NO aka District
.Write "<td>" & ar(1, i) & "</td>" '' BR_SVC_UN_NO aka Call Load
.Write "<td>" & ar(2, i) & "</td>" '' MDS_SP_CD aka Specialty
.Write "<td>" & ar(3, i) & "</td>" '' AVG(ActualSOS) aka avg of may
.Write "<td>" & ar(4, i) & "</td>" '' AVG(ActualSOS) aka avg of feb-may ** NOT CORRECT YET **
.Write "</tr>" & vbCrLf
Next
.Write "<table>" & vbCrLf
End With
End Sub

Function getData()
Dim cmd
Dim sql
Dim rs

sql = "SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, AVG(ActualSOS), AVG(ActualSOS) " &_
"FROM 2006_sos " &_
"WHERE ACCTG_MTH = 5 " &_
"GROUP BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD " &_
"ORDER BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD"

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
SName = Server.MapPath("SOSwCL_811.mdb")
With cmd
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+SName+";"
.CommandType = 1 '' adCmdText
.CommandText = sql

rs.Open .Execute
End With

If Not rs.EOF Then
getData = rs.GetRows
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
End Function
%>
The issue I am having is figuring out how to get the 5th column to work "Avg of ActualSOS from Feb06 to May06", which should be an average of the ActualSOS from February (2) to May (5).
Would I have to create a new SQL statement? Thanks very much.

[russell]

yes, your SQL will look like this (not sure if I should be joining on MDS_SP_CD, but i'm sure about the first two -- only u know for sure.


SELECT a.SVC_UN_NO, a.BR_SVC_UN_NO, a.MDS_SP_CD, a.ActualSOS, isnull(b.ActualSOS, 0) ActualSOsFebThruJune
FROM (
SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, AVG(ActualSOS) ActualSOS
FROM 2006_sos
WHERE ACCTG_MTH = 5
GROUP BY
SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD
) a
LEFT JOIN (
SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, AVG(ActualSOS) ActualSOS
FROM 2006_sos
WHERE ACCTG_MTH Between 2 And 5
GROUP BY
SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD
) b
On a.SVC_UN_NO = b.SVC_UN_NO
And a.BR_SVC_UN_NO = b.BR_SVC_UN_NO
And a.MDS_SP_CD = b.MDS_SP_CD
ORDER BY
a.SVC_UN_NO, a.BR_SVC_UN_NO, a.MDS_SP_CD
also, if you KNOW that there is a match for every record, you can turn the left join into an inner join and remove the isnull function in the select clause.

Finally, this code assumes SQL Server. Requires a little tweaking for other rdbms.

There are other ways to accomplish this as well, but this ought to do the trick with reasonable efficiency

[st3ady]

Hey Russell, back to working on this problem again. I tried using what you gave me but I could not get it working, I guess it is because I am using Access. Here is the SQL code snippet I'm currently using, the MonthBy is selected on the page using a dropdown box.

IF request("B1") = "" THEN 'form submitted if not empty string. build sql string
StatusMessage = "Select a Region, District, and Month to View Report."
ELSE
Sql = "SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, AVG(ActualSOS) AS AVERAGESOS FROM 2006_sos_exp WHERE ACCTG_MTH = " & MonthBy

IF NOT District = "" THEN 'if a district was passed in add criteria to only show that district
Sql = Sql & " and SVC_UN_NO = " & District

END IF

Sql = Sql & " GROUP BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD ORDER BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD"

Set SOSreport_rs = cnn.Execute(Sql)

IF SOSreport_rs.eof then StatusMessage = "No data found for selected criteria."
END IF

Is there a way to get two different averages with different where statements in Access? Is a join required for this? This would show the chosen month average and then the year-to-date average side by side.
Thanks!

[russell]

ok, for Access, change the query to this (actually, I'll only post the first line from my example above, because all of the rest stays the same

SELECT a.SVC_UN_NO, a.BR_SVC_UN_NO, a.MDS_SP_CD, a.ActualSOS, iif(isnull(b.ActualSOS), 0, b.ActualSOS) As ActualSOsFebThruJune

[russell]

my bad. a couple of other changes...all changes are in red below

SELECT a.SVC_UN_NO, a.BR_SVC_UN_NO, a.MDS_SP_CD, a.ActualSOS, iif(isnull(b.ActualSOS), 0, b.ActualSOS) As ActualSOsFebThruJune
FROM (
SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, AVG(ActualSOS) As ActualSOS
FROM 2006_sos
WHERE ACCTG_MTH = 5
GROUP BY
SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD
) a
LEFT JOIN (
SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, AVG(ActualSOS) As ActualSOS
FROM 2006_sos
WHERE ACCTG_MTH Between 2 And 5
GROUP BY
SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD
) b
On a.SVC_UN_NO = b.SVC_UN_NO
And a.BR_SVC_UN_NO = b.BR_SVC_UN_NO
And a.MDS_SP_CD = b.MDS_SP_CD
ORDER BY
a.SVC_UN_NO, a.BR_SVC_UN_NO, a.MDS_SP_CD
one more thing that may need to change is flipping the queries around in the section bracketed off as "a" and "b". Or change Left Join to Right Join... depends on the data...

edit: I just noticed a minor error... the closing table tag in the script is an opening table tag. Might want to fix that... :)

[st3ady]

Thanks Russell. I tried it but I got an error saying "Circular reference caused by alias 'ActualSOS' in query definition's SELECT list." So I am assuming this is because in the table there is a column titled 'ActualSOS' and this get the query all mixed up. I tried renaming the 'ActualSOS's in the 3rd and 10th lines but I wasnt sure if I had to change them up on the first line as well, and which one corresponds to which. I tried that and when I tried to run it, Access asked me to input a parameter for a.ActualSOS but I just clicked cancel. I am trying to read through it to try and understand what is going on, let me know if I got this straight:
It selects the first few things from the 'a' group which is defined in the a parenthesis, and then it has an if statement to check and see if the 'b' ActualSOS is empty or not, and then it renames it as ActualSOsFebThruJune...
:confused:

[russell]

yup, u got it. and you're right. can't alias a field in access with an actual field name. change 'em all to something else.