conn.inc
<%
Dim CONN_STRING
Dim CONN_USER
Dim CONN_PASS
CONN_STRING = "Provider=SQLOLEDB;Data Source=bkrupp2;" _
& "Initial Catalog=mesonic;Connect Timeout=15;" _
& "Network Library=dbmssocn;"
CONN_USER = "meso"
CONN_PASS = "meso"
%>
table.inc
<%
Dim table, table2
table ="04we"
table2 ="04bu"
%>
date.inc
<%
Dim date
date="2004"
%>
ergebnis.asp
<BODY>
<!--#include file="./includes/conn.inc"-->
<!--#include file="./includes/date.inc"-->
<!--#include file="./includes/table.inc"-->
<%
Dim strSQL 'SQL command to execute
Dim objPagingConn 'The ADODB connection object
Dim objPagingRS 'The ADODB recordset object
strSQL1="(SELECT a.Guv as Guv, a.Was as Was, a.Wer as Wer, a.kto as Kto, d.c003 AS KtoBez, a.Budget as bud,a.BudgetVJ as vj, b.C015 AS Haben, b.C016 AS Soll, b.C017/1000 AS Saldo, c.C017/1000 AS SaldoVJ FROM T052"&table&" as b left join T052"&table2&" c on b.c000 = c.c000 INNER JOIN Budget2003 AS a ON b.C000 = a.Kto inner join T055"&table&" d on a.kto = d.c002 where a.Guv='"& Request.QueryString("id") &"' )"
strSQL="SELECT c.Guv as Guv, c.Was as Was, c.Wer as Wer,c.Kto as kto, c.KtoBez as KtoBez, Sum(bud) AS bud1, Sum(vj) as budvj, Sum(Saldo) AS Saldo0, sum(SaldoVJ) SVJ FROM "&strSQL1&" AS c GROUP BY c.Guv, c.Was, c.Wer, c.kto, c.KtoBez order by a.Guv"
strSQL2="SELECT Sum(bud) AS bud2, Sum(Saldo) AS Saldo1, Sum(VJ) as BVJ, Sum(SaldoVJ) as SVJ FROM "&strSQL1&" AS c "
' Now we finally get to the DB work...
' Create and open our connection
Set objPagingConn = Server.CreateObject("ADODB.Connection")
objPagingConn.Open CONN_STRING, CONN_USER, CONN_PASS
set objPagingRS1 = objPagingConn.Execute(strSQL2)
%>
<h3><center>Budget <%=date%> Einzeldarstellung <%=Request.QueryString("id")%> auf Kontenbasis</h3></center>
<br>
<table border='0' cellpadding='-2' width='100%' class='bcgu1 bso bw1 bgud'>
<tr>
<td width="12%"></td>
<td width="26%"></td>
<td width="8%" class='fsize8 bcgu0 bsor bw1'></td>
<td width="10%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2'><center>Budget</center></td>
<td width="10%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2'><center>Saldo</center></td>
<td width="11%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2'><center>%</center></td>
<td width="10%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2'><center>Budget VJ</center></td>
<td width="7%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2'><center>Saldo</center></td>
<td width="10%" class='fsize8' cellpadding='-2' ><center>%</center></td>
</tr>
<tr>
</tr>
<tr>
<td width="12%"></td>
<td width="26%"></td>
<td width="8%" class='fsize8 bcgu0 bsor bw1'></td>
<td width="10%" class='fsize8 bcgu0 bsor bw1'><center><%=formatnumber(objPagingRS1("bud2"),1)%></center></td>
<td width="10%" class='fsize8 bcgu0 bsor bw1'><center><%=formatnumber(objPagingRS1("Saldo1"),1)%></center></td>
<td width="11%" class='fsize8 bcgu0 bsor bw1'><center><%if formatnumber(objPagingRS1("bud2"),2) <> 0 then response.write formatnumber(((formatnumber(objPagingRS1("Saldo1"),2)/formatnumber(objPagingRS1("bud2"),2))*100),1) else response.write formatnumber(objPagingRS1("Saldo1"),1)%> %</center></td>
<td width="10%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2' ><center><%=formatnumber(objPagingRS1("bvj"),1)%></center></td>
<td width="7%" class='fsize8 bcgu0 bsor bw1' cellpadding='-2' ><center><%=formatnumber(objPagingRS1("Svj"),1)%></center></td>
<td width="10%" class='fsize8 ' cellpadding='-2' ><center><%if formatnumber(objPagingRS1("bvj"),2) <> 0 then response.write formatnumber(((formatnumber(objPagingRS1("Svj"),2)/formatnumber(objPagingRS1("bvj"),2))*100),1) else response.write formatnumber(objPagingRS1("Svj"),1)%> %</center></td>
</tr>
</table>
<br>
<table border='0' cellpadding='-2' width='100%' class='bcgu1 bso bw1 '>
<tr>
<td colspan="3" cellpadding='-2' width="64%" class='bcgu1 bsob bsor bw1 fsize8 bgue'><center>Budget GuV</center></td>
<td width="12%" cellpadding='-2' class=' bcgu1 bsor bw1 fsize8 bgue'><center>Budget</center></td>
<td width="12%" cellpadding='-2' class=' bcgu1 bsor bw1 fsize8 bgue'><center>Aktuell</center></td>
<td width="12%" cellpadding='-2' class='fsize8 bgue'><center>Abweichung</center></td>
<td width="12%" cellpadding='-2' class=' bcgu1 bsor bw1 fsize8 bgue'><center>Budget</center></td>
<td width="12%" cellpadding='-2' class=' bcgu1 bsor bw1 fsize8 bgue'><center>Endstand</center></td>
<td width="12%" cellpadding='-2' class='fsize8 bgue'><center>Auslastung</center></td>
</tr>
<tr>
<td width="12%" class='fsize8 bgue bsob'>Konto</td>
<td width="26%" class='fsize8 bgue bsob'>Was</td>
<td width="6%" class='bcgu0 bsor bw1 fsize8 bgue bsob' >Wer</td>
<td width="12%" class=' bcgu1 bsor bw1 fsize8 bgue bsob'><center><%=date%></center></td>
<td width="12%" class=' bcgu1 bsor bw1 fsize8 bgue bsob'><center>TEur</center></td>
<td width="12%" class='fsize8 bgue bsob'><center>%</center></td>
<td width="12%" class=' bcgu1 bsor bw1 fsize8 bgue bsob'><center>Vorjahr</center></td>
<td width="12%" class=' bcgu1 bsor bw1 fsize8 bgue bsob'><center>TEur</center></td>
<td width="12%" class='fsize8 bgue bsob'><center>%</center></td>
</tr>
<%
' Open RS
set objPagingRS = objPagingConn.Execute(strSQL)
WHILE NOT objPagingRS.EOF
%>
<tr>
<td width="12%" class='fsize8'><a href=konto_umsatz.asp?id=<%=objPagingRS("kto")%> ><%=objPagingRS("kto")%></a></td>
<td width="26%" class='fsize8'><%=objPagingRS("KtoBez")%></td>
<td width="6%" class='bcgu0 bsor bw1 fsize8'><a href=gesamt_verantwortlicher.asp?id=<%=objPagingRS("Wer")%>><%=objPagingRS("Wer")%></a></td>
<td width="12%" align=right style="padding-right: 15px" class='bcgu0 bsor bw1 fsize8'><%=formatnumber(objPagingRS("bud1"),0)%></td>
<td width="12%" align=right style="padding-right: 15px" class=' bcgu1 bsor bw1 fsize8'><%=formatnumber(objPagingRS("Saldo0"),1)%></td>
<td width="12%" align=right style="padding-right: 15px" class='fsize8'><%if formatnumber(objPagingRS("bud1"),2) <> 0 then response.write formatnumber(((formatnumber(objPagingRS("Saldo0"),2)/formatnumber(objPagingRS("bud1"),2))*100),1) else response.write formatnumber(objPagingRS("Saldo0"),1)%> %</td>
<td width="12%" align=right style="padding-right: 15px" class='bcgu0 bsor bw1 fsize8'><%=formatnumber(objPagingRS("budVJ"),0)%></td>
<td width="12%" align=right style="padding-right: 15px" class=' bcgu1 bsor bw1 fsize8 '><%=formatnumber(objPagingRS("SVJ"),1)%></td>
<td width="12%" align=right style="padding-right: 15px" class='fsize8'><%if formatnumber(objPagingRS("budVJ"),2) <> 0 then response.write formatnumber(((formatnumber(objPagingRS("SVJ"),2)/formatnumber(objPagingRS("budVJ"),2))*100),1) else response.write formatnumber(objPagingRS("SVJ"),1)%> %</td>
</tr>
<%
objPagingRS.MoveNext
WEND
%>
<tr>
<td width="6%" class='fsize8'></td>
<td width="26%" class='fsize8'></td>
<td width="12%" class='fsize8'></td>
<td width="12%" class=' bcgu0 bsor bw1 fsize8'></td>
<td width="12%" class='fsize8'></td>
<td width="12%" class='fsize8'></td>
<td width="12%" class=' bcgu0 bsor bw1 fsize8'></td>
<td width="12%" class='fsize8'></td>
<td width="12%" class='fsize8'></td>
</tr>
</table>