I have an issue with manipulating a data in ASP. I have to give the
final output as a %age of the records. My SQL query is something like
this:
1)
Select T.CustDealerID, count(*) As RecordCount,
D.DealerCompanyDesignator, D.DealerRegionID FROM
NACCO_WEB.dbo._tmpSampleCustomerDealerReviewFromWe b_Q1 T,
NACCO_WEB.dbo.lkuDealer D Where T.CustDealerID = D.DealerID AND
(T.IPContactFullNm is not NULL or T.PSContactFullNm is not NULL)
Group by T.CustDealerID, D.DealerCompanyDesignator, D.DealerRegionID
ORDER BY T.CustDealerID, D.DealerCompanyDesignator, D.DealerRegionID
When I display my records I have to display the record count as the
percentile of this query:
2)
Select count(T.CustDealerID) As RecordCount,
D.DealerCompanyDesignator, D.DealerRegionID FROM
NACCO_WEB.dbo._tmpSampleCustomerDealerReviewFromWe b_Q1 T,
NACCO_WEB.dbo.lkuDealer D WHERE T.CustDealerID = D.DealerID Group by
D.DealerCompanyDesignator, D.DealerRegionID ORDER by
D.DealerCompanyDesignator, D.DealerRegionID
Essentially I am trying to get the record count as
(1 query's count(*) As RecordCount / 2 query's count(*) As
RecordCount) * 100
My ASP code is as follows:
<% Option Explicit%>
<% Response.ContentType = "application/vnd.ms-excel" %>
<HTML>
<HEAD>
<TITLE>Percent Of Records with Contact Name by Region/Brand</TITLE>
</HEAD>
<BODY>
<BODY>
<TABLE BORDER=0>
<TR>
<TD align=center colspan=8><H2>Percentile of Records With Contact
Name by Region/Dealer Access Tracking Report</H2></TD>
</TR>
<TR>
<TD align=left colspan=8><% Response.Write "Created on " &
FormatDateTime(Date, 1) %></TD>
</TR>
</TABLE>
<TABLE BORDER=0>
<TR><TD align=left colspan=8> </TD></TR>
</TABLE>
<TABLE BORDER="1">
<TR>
<TD align=center><STRONG>Customer DealerID</STRONG></A></TD>
<TD align=center><STRONG>Dealer Region ID</STRONG></A></TD>
<TD align=center><STRONG>Dealer Company</STRONG></A></TD>
<TD align=center><STRONG>Counts</STRONG></A></TD>
<TD align=center><STRONG>Percentile(%)</STRONG></A></TD>
</TR>
<%
Dim objRs: Set objRs = Server.CreateObject("ADODB.Recordset")
Dim objConn: Set objConn = Server.CreateObject("ADODB.Connection")
Dim intCheckCounter
Dim intLnCnt
objConn.Open "Provider=SQLOLEDB.1;Initial Catalog=Web;Data Source=" &
SERVER_DB & ";UID=777;PWD=222"
With objRs
.Open "Select T.CustDealerID, count(*) As RecordCount,
D.DealerCompanyDesignator, D.DealerRegionID FROM
_tmpSampleCustomerDealerReviewFromWeb_Q1 T, dbo.lkuDealer D Where
T.CustDealerID = D.DealerID AND (T.IPContactFullNm is not NULL or
T.PSContactFullNm is not NULL) Group by T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID ORDER BY T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID", objConn
'' This is the total number of records that the dealer has.
'.Open "Select T.CustDealerID, count(T.CustDealerID) As RecordCount1,
D.DealerCompanyDesignator, D.DealerRegionID FROM
dbo._tmpSampleCustomerDealerReviewFromWeb_Q1 T, dbo.lkuDealer D WHERE
T.CustDealerID = D.DealerID Group by T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID ORDER by T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID", objConn
If Not .EOF Then .MoveFirst
Do While Not .EOF
Response.Write "<TD align=left>" & objRs.Fields("CustDealerID")
& "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("DealerRegionID")
& "</TD>"
Response.Write "<TD align=left>" & objRs.Fields
("DealerCompanyDesignator") & "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("RecordCount")
& "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("Percentile")
& "</TD>"
Response.Write "</TR>"
SET intlnCnt = RecordCount
.MoveNext
Loop
.Close
End With
Set objRs = Nothing
Set objConn = Nothing
%>
</TABLE>
</BODY>
</HTML>
I tried using the logic of having both the SQL query executed from
the same place but I got an error as ADODB.Recordset EOT and BOF----.
I am not very sure where I am missing:
I know the logic has to be somewhere here:
With objRs
.Open "Select T.CustDealerID, count(*) As RecordCount,
D.DealerCompanyDesignator, D.DealerRegionID FROM
_tmpSampleCustomerDealerReviewFromWeb_Q1 T, dbo.lkuDealer D Where
T.CustDealerID = D.DealerID AND (T.IPContactFullNm is not NULL or
T.PSContactFullNm is not NULL) Group by T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID ORDER BY T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID", objConn
'' This is the total number of records that the dealer has.
'.Open "Select T.CustDealerID, count(T.CustDealerID) As RecordCount1,
D.DealerCompanyDesignator, D.DealerRegionID FROM
dbo._tmpSampleCustomerDealerReviewFromWeb_Q1 T, dbo.lkuDealer D WHERE
T.CustDealerID = D.DealerID Group by T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID ORDER by T.CustDealerID,
D.DealerCompanyDesignator, D.DealerRegionID", objConn
If Not .EOF Then .MoveFirst
Do While Not .EOF
Response.Write "<TD align=left>" & objRs.Fields("CustDealerID")
& "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("DealerRegionID")
& "</TD>"
Response.Write "<TD align=left>" & objRs.Fields
("DealerCompanyDesignator") & "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("RecordCount")
& "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("Percentile")
& "</TD>"
Response.Write "</TR>"
SET intlnCnt = RecordCount
.MoveNext
Loop
.Close
End With
Any thoughts - pointers?
Thanks
Sri
Manipulating data in ASP
