I couldn't find Appropriate SQL Queries

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

I couldn't find Appropriate SQL Queries

Author

Topic

Hamsicik
Starting Member

2 Posts

Posted - 2007-02-06 : 07:07:24

Because forum HTML codes are OFF I had to put my question between code tags. Because to tell my question I had to show the database tables and wanted output. I succeeded retrieving the datas but combining these three tables together with SQL or ASP codes is too complex. Please help me determining the table. As I am subscribed to this forum you can ask me anything about my question. I can clarify my question if you need. Your answers will be replied immediately.
(To see my question pls save the codes below as htm file or see it with a wysiwyg editor.)

<p><b><font size="2">tbl_PRODUCTIONS</font></b></p>
<table border="1" width="971" id="table1">
	<tr>
		<td height="19" width="54"><b><font size="2">fld_DATE</font></b></td>
		<td width="161" height="19"><b><font size="2">fld_PRODUCER_UNITCODE</font></b></td>
		<td height="19" width="124"><b><font size="2">fld_PRODUCT_CODE</font></b></td>
		<td height="19" width="222"><b><font size="2">fld_DAILY_PRODUCTION_PROGRAM</font></b></td>
		<td height="19" width="176"><b><font size="2">fld_PRODUCTION_QUANTITY</font></b></td>
		<td height="19" width="136"><b><font size="2">fld_STOCK_QUANTITY</font></b></td>
		<td height="19" width="52"><b><font size="2">fld_SELL</font></b></td>
	</tr>
	<tr>
		<td width="54" >......</td>
		<td width="161" >....</td>
		<td width="124">....</td>
		<td width="222">....</td>
		<td width="176">....</td>
		<td width="136">.....</td>
		<td width="52">....</td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.10</font></td>
		<td width="222"><font size="2">100</font></td>
		<td width="176"><font size="2">115</font></td>
		<td width="136"><font size="2">100</font></td>
		<td width="52"><font size="2">10</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.20</font></td>
		<td width="222"><font size="2">200</font></td>
		<td width="176"><font size="2">190</font></td>
		<td width="136"><font size="2">200</font></td>
		<td width="52"><font size="2">54</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">2.110.12</font></td>
		<td width="222"><font size="2">300</font></td>
		<td width="176"><font size="2">350</font></td>
		<td width="136"><font size="2">450</font></td>
		<td width="52"><font size="2">745</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">12.128.43</font></td>
		<td width="222"><font size="2">150</font></td>
		<td width="176"><font size="2">150</font></td>
		<td width="136"><font size="2">4664</font></td>
		<td width="52"><font size="2">55</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">6.142.42</font></td>
		<td width="222"><font size="2">100</font></td>
		<td width="176"><font size="2">100</font></td>
		<td width="136"><font size="2">234</font></td>
		<td width="52"><font size="2">77</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">9.86.50</font></td>
		<td width="222"><font size="2">125</font></td>
		<td width="176"><font size="2">135</font></td>
		<td width="136"><font size="2">654</font></td>
		<td width="52"><font size="2">80</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">18.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">12.43.250</font></td>
		<td width="222"><font size="2">75</font></td>
		<td width="176"><font size="2">80</font></td>
		<td width="136"><font size="2">233</font></td>
		<td width="52"><font size="2">101</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.10</font></td>
		<td width="222"><font size="2">100</font></td>
		<td width="176"><font size="2">99</font></td>
		<td width="136"><font size="2">100</font></td>
		<td width="52"><font size="2">10</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.20</font></td>
		<td width="222"><font size="2">200</font></td>
		<td width="176"><font size="2">210</font></td>
		<td width="136"><font size="2">200</font></td>
		<td width="52"><font size="2">54</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">2.110.12</font></td>
		<td width="222"><font size="2">300</font></td>
		<td width="176"><font size="2">309</font></td>
		<td width="136"><font size="2">450</font></td>
		<td width="52"><font size="2">745</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">12.128.43</font></td>
		<td width="222"><font size="2">150</font></td>
		<td width="176"><font size="2">155</font></td>
		<td width="136"><font size="2">4664</font></td>
		<td width="52"><font size="2">55</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">6.142.42</font></td>
		<td width="222"><font size="2">100</font></td>
		<td width="176"><font size="2">100</font></td>
		<td width="136"><font size="2">234</font></td>
		<td width="52"><font size="2">77</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">9.86.50</font></td>
		<td width="222"><font size="2">125</font></td>
		<td width="176"><font size="2">90</font></td>
		<td width="136"><font size="2">654</font></td>
		<td width="52"><font size="2">80</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">19.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">12.43.250</font></td>
		<td width="222"><font size="2">75</font></td>
		<td width="176"><font size="2">60</font></td>
		<td width="136"><font size="2">233</font></td>
		<td width="52"><font size="2">101</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.10</font></td>
		<td width="222"><font size="2">100</font></td>
		<td width="176"><font size="2">90</font></td>
		<td width="136"><font size="2">100</font></td>
		<td width="52"><font size="2">10</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.20</font></td>
		<td width="222"><font size="2">200</font></td>
		<td width="176"><font size="2">190</font></td>
		<td width="136"><font size="2">200</font></td>
		<td width="52"><font size="2">54</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">2.110.12</font></td>
		<td width="222"><font size="2">300</font></td>
		<td width="176"><font size="2">350</font></td>
		<td width="136"><font size="2">450</font></td>
		<td width="52"><font size="2">745</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">12.128.43</font></td>
		<td width="222"><font size="2">150</font></td>
		<td width="176"><font size="2">156</font></td>
		<td width="136"><font size="2">4664</font></td>
		<td width="52"><font size="2">55</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">6.142.42</font></td>
		<td width="222"><font size="2">100</font></td>
		<td width="176"><font size="2">120</font></td>
		<td width="136"><font size="2">234</font></td>
		<td width="52"><font size="2">77</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">9.86.50</font></td>
		<td width="222"><font size="2">125</font></td>
		<td width="176"><font size="2">140</font></td>
		<td width="136"><font size="2">654</font></td>
		<td width="52"><font size="2">80</font></td>
		</tr>
	<tr>
		<td width="54" ><font size="2">20.01.2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">12.43.250</font></td>
		<td width="222"><font size="2">75</font></td>
		<td width="176"><font size="2">90</font></td>
		<td width="136"><font size="2">233</font></td>
		<td width="52"><font size="2">101</font></td>
		</tr>
	<tr>
		<td width="54" >...</td>
		<td width="161" >....</td>
		<td width="124">....</td>
		<td width="222">....</td>
		<td width="176">....</td>
		<td width="136">....</td>
		<td width="52">....</td>
		</tr>
</table>

<p> </p>
<p><b><font size="2">tbl_ANNUAL_PRG</font></b></p>
<table border="1" width="552" id="table2">
	<tr>
		<td width="62"><b><font size="2">fld_YEARS</font></b></td>
		<td><b><font size="2">fld_PRODUCER_UNITCODE</font></b></td>
		<td><b><font size="2">fld_PRODUCT_CODE</font></b></td>
		<td width="177"><b><font size="2">fld_PRODUCTION_PROGRAM</font></b></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.10</font></td>
		<td width="177"><font size="2">15000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.20</font></td>
		<td width="177"><font size="2">22000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">2.110.12</font></td>
		<td width="177"><font size="2">35000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">12.128.43</font></td>
		<td width="177"><font size="2">50000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">6.142.42</font></td>
		<td width="177"><font size="2">170000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">9.86.50</font></td>
		<td width="177"><font size="2">12000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2006</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">12.43.250</font></td>
		<td width="177"><font size="2">123000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.10</font></td>
		<td width="177"><font size="2">18000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">IST</font></td>
		<td width="124"><font size="2">1.20</font></td>
		<td width="177"><font size="2">25000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">2.110.12</font></td>
		<td width="177"><font size="2">32000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">ANK</font></td>
		<td width="124"><font size="2">12.128.43</font></td>
		<td width="177"><font size="2">55000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">6.142.42</font></td>
		<td width="177"><font size="2">190000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">9.86.50</font></td>
		<td width="177"><font size="2">10000</font></td>
	</tr>
	<tr>
		<td width="62"><font size="2">2007</font></td>
		<td width="161" ><font size="2">IZM</font></td>
		<td width="124"><font size="2">12.43.250</font></td>
		<td width="177"><font size="2">129000</font></td>
	</tr>
</table>
<p> </p>
<p><b><font size="2">tbl_PRODUCT_GROUPS<br>
(This table gives the names to the product of the tbl_PRODUCTIONS table)</font></b></p>
<table border="1" width="366" id="table3">
	<tr>
		<td><b><font size="2">fld_PRODUCT_GROUP_CODE</font></b></td>
		<td width="175"><b><font size="2">fld_PRODUCT_GROUP_CODE</font></b></td>
	</tr>
	<tr>
		<td width="175"><font size="2">1.10</font></td>
		<td width="175"><font size="2">Pencil</font></td>
	</tr>
	<tr>
		<td width="175"><font size="2">1.20</font></td>
		<td width="175"><font size="2">Rubber</font></td>
	</tr>
	<tr>
		<td width="175"><font size="2">2.110.12</font></td>
		<td width="175"><font size="2">Ruler</font></td>
	</tr>
	<tr>
		<td width="175"><font size="2">12.128.43</font></td>
		<td width="175"><font size="2">Pencil Box</font></td>
	</tr>
	<tr>
		<td width="175"><font size="2">6.142.42</font></td>
		<td width="175"><font size="2">School Bag</font></td>
	</tr>
	<tr>
		<td width="175"><font size="2">9.86.50</font></td>
		<td width="175"><font size="2">Pen</font></td>
	</tr>
	<tr>
		<td width="175"><font size="2">12.43.250</font></td>
		<td width="175"><font size="2">Calendar</font></td>
	</tr>
</table>
<p> </p>
<p><font size="2">These 3 tables are in Oracle. I am trying to access the tables 
with an asp page. I succeed to retrieve the values to a page, but processing the 
values and joining the tables together is too complex for me. The table I want 
is like this. Would you help me creating <b>ASP</b> page and <b>SQL</b> queries 
to create this table:</font></p>
<p><font size="2">Parameters to send the asp page that process the SQL queries:<br>
startdate=18.01.2007<br>
finishdate=20.01.2007</font></p>
<p><b><font size="2">The Productions of -18.01.2007-20.01.2007</font></b></p>
<table border="1" width="1066" id="table4">
	<tr>
		<td><font size="2">Comes From tbl_PRODUCTIONS table</font></td>
		<td><font size="2">Comes From tbl_PRODUCT_GROUPS table</font></td>
		<td width="155"><font size="2">Comes from tbl_ANNUAL_PRG table</font></td>
		<td width="208"><font size="2">Comes from tbl_PRODUCTIONS table summing 
		each value for each product</font></td>
		<td width="179"><font size="2">Comes from tbl_PRODUCTIONS table summing 
		each value for each product</font></td>
		<td width="108"><font size="2">Comes from tbl_PRODUCTIONS retrieving the 
		values for "finishdate". Not cumulative like other values!</font></td>
		<td width="187"><font size="2">Comes from tbl_PRODUCTIONS table summing 
		each value for each product</font></td>
	</tr>
	<tr>
		<td><b><font size="2">Producer Locations</font></b></td>
		<td><b><font size="2">Products</font></b></td>
		<td width="155"><font size="2"><b>Annual Production Program</b></font></td>
		<td width="208"><font size="2"><b>Production Program for wanted period</b></font></td>
		<td width="179"><font size="2"><b>Production Quantity for wanted period</b></font></td>
		<td width="108"><font size="2"><b>Stock Quantity</b></font></td>
		<td width="187"><font size="2"><b>Selling Quantity for wanted period</b></font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">IST</font></td>
		<td width="61"><font size="2">Pencil</font></td>
		<td width="155"><font size="2">18000</font></td>
		<td width="208"><font size="2">300</font></td>
		<td width="179"><font size="2">304 </font></td>
		<td width="108"><font size="2">100</font></td>
		<td width="187"><font size="2">30</font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">IST</font></td>
		<td width="61"><font size="2">Rubber</font></td>
		<td width="155"><font size="2">25000</font></td>
		<td width="208"><font size="2">600</font></td>
		<td width="179"><font size="2">590</font></td>
		<td width="108"><font size="2">200</font></td>
		<td width="187"><font size="2">162</font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">ANK</font></td>
		<td width="61"><font size="2">Ruler</font></td>
		<td width="155"><font size="2">32000</font></td>
		<td width="208"><font size="2">900</font></td>
		<td width="179"><font size="2">1009</font></td>
		<td width="108"><font size="2">450</font></td>
		<td width="187"><font size="2">2235</font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">ANK</font></td>
		<td width="61"><font size="2">Pencil Box</font></td>
		<td width="155"><font size="2">55000</font></td>
		<td width="208"><font size="2">450</font></td>
		<td width="179"><font size="2">461</font></td>
		<td width="108"><font size="2">4664</font></td>
		<td width="187"><font size="2">165</font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">IZM</font></td>
		<td width="61"><font size="2">School Bag</font></td>
		<td width="155"><font size="2">190000</font></td>
		<td width="208"><font size="2">300</font></td>
		<td width="179"><font size="2">320</font></td>
		<td width="108"><font size="2">234</font></td>
		<td width="187"><font size="2">231</font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">IZM</font></td>
		<td width="61"><font size="2">Pen</font></td>
		<td width="155"><font size="2">10000</font></td>
		<td width="208"><font size="2">375</font></td>
		<td width="179"><font size="2">365</font></td>
		<td width="108"><font size="2">654</font></td>
		<td width="187"><font size="2">240</font></td>
	</tr>
	<tr>
		<td width="119" ><font size="2">IZM</font></td>
		<td width="61"><font size="2">Calendar</font></td>
		<td width="155"><font size="2">129000</font></td>
		<td width="208"><font size="2">225</font></td>
		<td width="179"><font size="2">230</font></td>
		<td width="108">233</td>
		<td width="187"><font size="2">303</font></td>
	</tr>
</table>

OS: WinXP Pro
DB: Oracle (Version ?)
Script: ASP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 07:09:53

quote:
DB: Oracle (Version ?)

You are using Oracle DB ? try posting over at http://www.dbforums.com/. This is a Microsoft SQL Server forum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 07:47:21

If he is willing to post an ORACLE query on a MS SQL Server forum, that might be his only chance to keep his job.

SELECT		p.fld_producer_unitcode,
		pg.fld_product_group_name,
		MIN(ap.fld_production_program),
		SUM(p.fld_daily_production_program),
		SUM(p.fld_production_quantity),
		AVG(p.fld_stock_quantity),
		SUM(p.fld_sell)
FROM		tbl_productions AS p
INNER JOIN	tbl_annual_prg AS ap ON ap.fld_years = DATEPART(year, p.fld_date)
			AND ap.fld_producer_unitcode = p.fld_producer_unitcode
			AND ap.fld_product_code = p.fld_product_code
INNER JOIN	tbl_product_groups AS pg ON pg.fld_product_group_code = p.fld_product_code
WHERE		p.fld_date >= @startdate
		and p.fld_date <= @finishdate
GROUP BY	p.fld_producer_unitcode,
		pg.fld_product_group_name
ORDER BY	p.fld_producer_unitcode,
		pg.fld_product_group_name

Peter Larsson
Helsingborg, Sweden

Hamsicik
Starting Member

2 Posts

Posted - 2007-02-06 : 10:40:10

Thank you very much peso. I perceived what kind queries I should write but I couldn't implement the code into asp. I think syntax is a bit different. I there anybody who uses ASP, help to me. Production program shouldn't be MIN(ap.fld_production_program) because it came from another table, not minimum values. And I don't want group the values according to fld_product_group_name. Ordering the values is not important. Stock quantity will not be average values. Only the last values will be used directly . And I couldn't understand what the prefixes (ie. p ap, pg).

Too much problem I have, I know, but if you help me I will be very thankful. Although our db is Oracle, there is nothing to do for oracle. SQL is enough to get the values.

Thanks in advance.

OS: WinXP Pro
DB: Oracle (Version ?)
Script: ASP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 10:47:07

With the test data above, my suggestion produces exactly the answer you want.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources