Creating a joined view within a loop

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

General SQL Server Forums

New to SQL Server Programming

Creating a joined view within a loop

Author

Topic

crtdude
Starting Member

13 Posts

Posted - 2010-02-06 : 08:37:45

I have a form which makes a call to a table based on two variables then displays column data in a table. However, while this occurs I need to pull two columns of data for records in the original recordset which corresponds to the name, month and year.

The second table I am referring to has corresponding column names to the first (name_brand=name_brand,bf_month=month,bf_year=year) and the columns I need to pull data from are budg_rms and budg_rev and need to be shown in the columns mixed with the other data. I already have those fields entered below.

My code is below and I would appreciate any help from the experts within this forum. Thanks in advance.

=========


<%
dim dsn,conn,rs,sql,x
dim flag,color,useraction,element
dim MonthNow
MonthNow = Month(Date())
useraction=request.querystring("action")
select case useraction
case "search"
x = 0
sql = "select * from OMNI_FCSTBYDAY_ADOSM"
dsn="DRIVER={SQL Server};SERVER=XXX;DATABASE=XXX;UID=XXX;PWD=XXX;"
set conn = server.createObject("adodb.connection")
set rs = server.createObject("adodb.recordset")
conn.open dsn

for each element in request.form

if request.form(element)<>"" then

select case element
case "year"
sql=sql & " and year = '" & Request.form(element) & "'"
case "name_brand"
sql=sql & " where name_brand = '" & Request.form(element) & "'"
end select
end if
next
sql=sql & " order by month ASC"
'response.write sql'***debug
rs.open sql,conn
if rs.eof and rs.bof then
response.write "<center><table width=540 border=0><tr><td><font color=red size=2 face=Arial, Helvetica, sans-serif><BR>"
response.write "Your Search Retrieved No Records."
response.write "<BR><BR><font color=black size=2 face=Arial, Helvetica, sans-serif>"
response.write "Please check your selections and try again. If you believe you have received this message in error, please contact Manager of Sales Systems (Angel Johnson)."
response.write "</font></td></tr></table><BR>"
else
response.write "<center><table width=540 border=0><tr><td align=left><font color=#000000 size=2 face=Arial, Helvetica, sans-serif><b>Property Name: "
response.write RS("name_brand") & "</td></tr>"
response.write "<BR><center><tr><td align=left><font color=#000000 size=2 face=Arial, Helvetica, sans-serif><b>ADOSM: "
response.write RS("adosm") & "</td></tr>"
response.write "<BR><center><tr><td align=left><font color=#000000 size=2 face=Arial, Helvetica, sans-serif><b>Type: "
response.write RS("type") & "<BR><BR></td></tr>"
response.write "<center><table width=540 border=0><th align=center><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>Year</th>"
response.write "<th align=center><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>Month</TH><th align=center><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>Budget RMS</TH><th align=center><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>Budget REV</TH><th align=center><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>Forecast RMS</TH><th align=center><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>Forecast REV</TH>"
do while not rs.eof
if flag=0 then
flag=1
color="#D7D7D7"
else
flag=0
color="#B0C4EC"
end if

response.write "<TR bgcolor="& color & "><TD><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>" & RS("year") & "</td><td><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>" & RS("month") & "</td><td><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>"
response.write RS("budg_rms") & "</td><td><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>" & RS("budg_rev") & "</td><td><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>"
response.write RS("forecast_rms") & "</td><td><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>" & RS("forecast") & "</td><td><font color=#000000 size=2 face=Arial, Helvetica, sans-serif>"
if Year(Date) = RS("year") then
if MonthNow = RS("month") then
response.write "<A href='DOSM_GPF_edit.asp?action=edit&id="&rs("ID")&"'><b>Edit</b></td></TR></TD>"
else
response.write "</td></TR></TD>"
end if
else
response.write "<b>Locked</b></td></TR></TD>"
end if
rs.movenext
x=x+1
loop

response.write "</table></center><BR>"
end if
end select
%>

<table border=0 width=700>
<tr><td colspan=2 align=center bgcolor="#F7E6CE"><font color="#000000" size="2" face="Arial, Helvetica, sans-serif"><b>Please select Property and Year</b><br><br></td></tr>
<form action="DOSM_GPF_search.asp?action=search" method=post>
<tr><td align="center" bgcolor="#F7E6CE"><font color="#000000" size="2" face="Arial, Helvetica, sans-serif">
Select Property: <SELECT NAME="name_brand" SIZE="1">
<%
dim dsn2,conn2,rs2,sql2
sql2 = "select distinct name_brand from OMNI_FCSTBYDAY_ADOSM order by name_brand ASC"
dsn2="DRIVER={SQL Server};SERVER=XXX;DATABASE=XXX;UID=XXX;PWD=XXX;"
set conn2 = server.createObject("adodb.connection")
set rs2 = server.createObject("adodb.recordset")
conn2.open dsn2
rs2.open sql2,conn2
do while not rs2.eof %>
<option value="<%= rs2("name_brand") %>" ><%= rs2("name_brand") %></option>
<% rs2.MoveNext
Loop
conn2.close
set conn2 = nothing %>
</SELECT></td></tr>
<tr><td align="center" bgcolor="#F7E6CE"><font color="#000000" size="2" face="Arial, Helvetica, sans-serif">
Select Year: <SELECT NAME="year" SIZE="1">
<%
dim dsn3,conn3,rs3,sql3
sql3 = "select distinct year from OMNI_FCSTBYDAY_ADOSM order by year DESC"
dsn3="DRIVER={SQL Server};SERVER=XXX;DATABASE=XXX;UID=XXX;PWD=XXX;"
set conn3 = server.createObject("adodb.connection")
set rs3 = server.createObject("adodb.recordset")
conn3.open dsn3
rs3.open sql3,conn3
do while not rs3.eof %>
<option value="<%= rs3("year") %>" ><%= rs3("year") %></option>
<% rs3.MoveNext
Loop
conn3.close
set conn3 = nothing %>
</SELECT></td></tr>
<tr><td align="center" bgcolor="#F7E6CE"><BR><input type="submit" value="search"></td></tr>
</table>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 10:32:38

can you please elaborate on what you want with some sample data. your current explanation doesnot make much sense (at least for me)

crtdude
Starting Member

13 Posts

Posted - 2010-02-06 : 10:58:17

I can only supply a text example as shown below:

================================ HTML TABLE ==================================
Month | Year | Budg_rms | Budg_rev | Forecast_rms | Forecast_rev
1 | 2010 | 456 | 325000 | 635 | 350000
2 | and so on...

budg_rms and budg_rev are being pulled from table OMNI_PROPBGTFCST using a callout to bf_month and bf_year and name_brand. bf_month, bf_year and name_brand should match the form elements name_brand, month and year as shown on the HTML form.

Month, Year, Forecast_rms and Forecast_rev are being pulled from OMNI_FCSTBYDAY_ADOSM based on the HTML form selection for name_brand and year. But I also need to pull Budg_rms and Budg_rev from OMNI_PROPBGTFCST and display it in the columns as well.

The looping for all 12 months in the year is throwing me off. I need some sort of subroutine to run based on the original recordset pulling the data for the corresponding year and months for the same hotel and displaying that data along with my forecast data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 11:09:47

seems like this


sql = "SELECT f.Month , f.Year , p.Budg_rms , p.Budg_rev , f.Forecast_rms , f.Forecast_rev
FROM OMNI_PROPBGTFCST p
INNER JOIN OMNI_FCSTBYDAY_ADOSM f
ON f.name_brand = p.name_brand 
and f.year = p.year
WHERE p.bf_month='" & Request.form(element) & "' AND p.bf_year= '" & Request.form(element) & "' AND p.name_brand = '" & Request.form(element) & "'"

crtdude
Starting Member

13 Posts

Posted - 2010-02-06 : 11:23:45

Wow, simply amazing. A couple of questions though.

1. Where exactly do I place the query? I am totally lost now.
2. What are the f and p prefixes you use? Yes, I know, I am quite a rookie as serious sql.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 11:29:39

quote:
Originally posted by crtdude

Wow, simply amazing. A couple of questions though.

1. Where exactly do I place the query? I am totally lost now.
2. What are the f and p prefixes you use? Yes, I know, I am quite a rookie as serious sql.

Thanks.

1. what?? I've given you the query itself. what do you mean by where to place?
2. they're table aliases i.e short names for tables

crtdude
Starting Member

13 Posts

Posted - 2010-02-06 : 11:32:18

Gotcha. Thanks a million, will try it in a short while.

Have a good weekend.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 11:41:53

welcome

crtdude
Starting Member

13 Posts

Posted - 2010-02-06 : 13:09:21

Okay, I left all my code posted above exactly the same, named your sql string SQL4 along with all the connection info related to it.

I then placed SQL4 right after:

do while not rs.eof
if flag=0 then
flag=1
color="#D7D7D7"
else
flag=0
color="#B0C4EC"
end if

and while I am getting data in the table for the correct budg columns it is not the correct data.

First, it is not looping through the 12 months of the year and is showing all the same numbers. Those numbers also do not correspond with any of the data for any of the months actually in the joined second table items.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 13:11:59

quote:
Originally posted by crtdude

Okay, I left all my code posted above exactly the same, named your sql string SQL4 along with all the connection info related to it.

I then placed SQL4 right after:

do while not rs.eof
if flag=0 then
flag=1
color="#D7D7D7"
else
flag=0
color="#B0C4EC"
end if

and while I am getting data in the table for the correct budg columns it is not the correct data.

First, it is not looping through the 12 months of the year and is showing all the same numbers. Those numbers also do not correspond with any of the data for any of the months actually in the joined second table items.

why do you need to loop? dont table by itself have data for 12 months?

Those numbers also do not correspond with any of the data for any of the months actually in the joined second table items.

do you mean you need data group by months?

crtdude
Starting Member

13 Posts

Posted - 2010-02-07 : 06:23:09

I have no idea how to get your sql statement to print the table in place of where I have the existing loop setup which populates the table.

Can you cut and paste into my code from my initial post so I can see how you would get your sql to print in a table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources