Up for a challenge? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Up for a challenge?

Author

Topic

jglover
Starting Member

4 Posts

Posted - 2004-09-28 : 15:50:37

Ok, In the code below it is pulling data from 3 different tables, I'm getting duplicate records returned. I also need after every recordset to create another table. And go on. I have screenshots of what it is returning if you would like to see. Email me at jglover72@hotmail.com and I will send you the pic. Here is the code:

<%@ LANGUAGE="VBSCRIPT" %>

<% Option Explicit%>
<%
If Session("loggedin") = "" Then
Response.redirect ("../../../login.asp")
End If
%>

<%

Dim strData, datebox, datebox2
datebox=request.form("datebox")
datebox2=request.form("datebox2")

'Create a recordset
Dim rs,sheet
Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT DISTINCT

SQL1.Col001,(producer.Name),Sheet1$.Class,(Sheet1$.Count),(Sheet1$.Weight),Sheet1$.Price,Sheet1$.Ext,

COUNT(SQL1.Col013), SUM(SQL1.Col009), SUM(SQL1.Col009) / SUM(Sheet1$.Weight) from SQL1 JOIN producer

ON SQL1.Col001=producer.Producer# AND Col014 BETWEEN '" & datebox & "' AND '" & datebox2 & "' JOIN

Sheet1$ ON SQL1.Col001=Sheet1$.VendorNum GROUP BY producer.Name,Sheet1$.Class,

SQL1.Col001,Sheet1$.Count,Sheet1$.Weight, Sheet1$.VendorNum,Sheet1$.Price,Sheet1$.Ext,VendorNum

ORDER BY SQL1.Col001,Sheet1$.Class", objConn
Dim strTable
strTable = rs.GetString(,,"</td><td>","</td></tr><tr><td>"," ") %>

<HTML>
<BODY>
<img src="pinesmall1.jpg">
<%Response.Write("<H1 align=center>Hot Scale Summary</H1>")%>
<%Response.Write("<p align=center> From: " & datebox & "

To: " & datebox2 & "")%>
<%
Response.Write("<table border=1 cellspacing=1 bgcolor=LightSteelBlue>")
Response.Write("<td><B>Vendor#</td><td><B>Vendor Name</td><td><B>Class</td><td><B>Count

</td><td><B>Weight</td><td><B>Price</td><td><B>Extension</td><td><B>Carcass Count</td><td><B>Carcus

Weight</td><td><B>Yeild %</tr><tr><td>")

Response.Write(strTable)
Response.Write("</tr>")
%>

</BODY>
</HTML>
<%

'Cleanup!
rs.Close
Set rs = Nothing
objConn.Close
Set objConn = Nothing
%>

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-09-28 : 16:46:53

maybe you should try debuging the SQL statement in the QueryAnalyzer altering the query until you get the expected results

Declare @date1 datetime, @date2 datetime
--change the dates as needed
Set @date1 = '9/1/2004'
Set @date2 = '9/30/2004'

SELECT  
    SQL1.Col001, producer.Name, Sheet1$.Class, Sheet1$.COUNT, Sheet1$.Weight, 
    Sheet1$.Price, Sheet1$.Ext, COUNT(SQL1.Col013), SUM(SQL1.Col009), 
    SUM(SQL1.Col009) / SUM(Sheet1$.Weight)
FROM SQL1 JOIN producer ON 
    SQL1.Col001 = producer.Producer#   
    JOIN Sheet1$ ON 
    SQL1.Col001 = Sheet1$.VendorNum
Where Col014 BETWEEN @date1 AND @date2
GROUP BY SQL1.Col001, producer.Name, Sheet1$.Class, Sheet1$.COUNT, Sheet1$.Weight,
    Sheet1$.Price, Sheet1$.Ext
ORDER BY SQL1.Col001, Sheet1$.Class

Note: storing this kind of query in a stored procedure or view should be more efficient than building the query each time from your application

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources