Rewriting Insert Statements

Hi Friends,
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)Maybe something along the lines of

SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
FROM dbo.tbl_Ticket
group by EName, Trendmonth, SeverityLevel|||Works Great!! Thank you very much.

Reusing an SQLconnection

Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone helpTake a look @.Using Web.Config in ASP.NET and ASP

Returning Values from a Dynamic SQL

I have an accounting database which contains data from various years.
The frontend is a VB.Net program. At the year end, the program creates
new voucher and transaction tables and creates new stored procedures for
them.

I just append the 'new year' at the end and create them

ie, Vouchers2001, Vouchers2002, Vouchers2003
Similarly Transactions2001, Transactions2002.

The data for all the years is in the same database.

Also, I maintain a table called 'Books' which contains the Years for
which data is present in the Database. The Structure of the Books table
is

BookID BookYear
1 2001
2 2002
3 2003
4 2004

My Problem is that i need to know the current balance of any ledger for
any year. The method to calculate the balance for any year is to start
from the Minimum year in the Books table and continue upto the required
year. The SQL is as follows.

DECLARE @.iLedgerID AS INT --will be passed as parameter
DECLARE @.iYear as INT --will be passed as parameter
DECLARE @.CurrentBalance as MONEY

SET @.iLedgerID =1

DECLARE @.MinBook as INTEGER
DECLARE @.String nVarchar(4000)

SELECT @.MinBook = Min(BookYear)
FROM Books

WHILE @.MinBook <= @.iYear
BEGIN

SET @.String = ' DECLARE @.TT Money ' + char(13) +
' SELECT @.TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'
+ ' FROM transactions' + CAST(@.MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID ' +
'WHERE (LedgerID = @.iLedgerID)'

EXEC sp_executesql @.String, N'@.iLedgerID Int', @.iLedgerID */

SET @.MinBook = @.MinBook + 1
END

Now this is just a sample code. It may have a few glitches. My question
is

a) Do I have to create a dynamic sql if the name of the database is not
known ahead of time. If No then
b) I need to add the balance of each year to the grand total. How do i
return a value from a dynamic sql.

TIA

*** Sent via Developersdex http://www.developersdex.com ***Oops, I missed out the last line in the string varible. The last line
returns the calculated variable. The SQL docmentation says that Return
can only return integer types. so how do i return a money value. I
cannot insert into a temporary table because the application is
Multi-User and may fail.

WHILE @.MinBook <= @.iYear
BEGIN

SET @.String =' DECLARE @.TT Money ' + char(13) +
' SELECT @.TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )' +
' FROM transactions' + CAST(@.MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID' +
' = dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + '.VoucherID ' + 'WHERE
(LedgerID = @.iLedgerID)' + char(13) +
' Return @.TT'

EXEC sp_executesql @.String, N'@.iLedgerID Int', @.iLedgerID

SET @.MinBook = @.MinBook + 1
END

*** Sent via Developersdex http://www.developersdex.com ***|||Bill Bob (nospam@.devdex.com) writes:
> Oops, I missed out the last line in the string varible. The last line
> returns the calculated variable. The SQL docmentation says that Return
> can only return integer types. so how do i return a money value. I
> cannot insert into a temporary table because the application is
> Multi-User and may fail.

1) You would have a much simpler task, if you made the year a key in the
Vouchers and Transactions table, rather than having one table per year.

2) So you don't have the possibility to do that, but then create views:

CREATE VIEW Vouchers AS
SELECT Year = '2000', * FROM Vouchers2000
UNION ALL
SELECT '2001', * FROM Vouchers2001
UNION ALL
...

3) That you cannot use temp tabls in a multi-user environment is a mis-
understanding. Temp tables are visible for the local connection only.

4) However, ther prefer method for getting scalar data back from
sp_executesql is output parameters. For a quick example, see
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
> a) Do I have to create a dynamic sql if the name of the database is not
> known ahead of time.

Yes

> b) I need to add the balance of each year to the grand total. How do i
> return a value from a dynamic sql.

You can use OUTPUT parameters in sp_executesql

SET @.String = ' SELECT @.TT = ISNULL( SUM( ISNULL(Debit,0) -
ISNULL(Credit,0) ),0 )' +
' FROM transactions' + CAST(@.MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@.MinBook AS CHAR(4)) + '.VoucherID' +
' = dbo.Vouchers' + CAST(@.MinBook AS CHAR(4)) + '.VoucherID ' + 'WHERE
(LedgerID = @.iLedgerID)' + char(13) +
' Return @.TT'

DECLARE @.TT Money
EXEC sp_executesql @.String, N'@.iLedgerID Int, @.TT Money', @.iLedgerID ,
@.TT OUTPUT|||... should be this

EXEC sp_executesql @.String, N'@.iLedgerID Int, @.TT Money OUTPUT',
@.iLedgerID ,
@.TT OUTPUT|||> 1) You would have a much simpler task, if you made the year > a key in
the Vouchers and Transactions table, rather than > having one table per
year.

My intial problem was something else. So, I had to switch to different
table for different years.

Each Voucher Table contains a VoucherDate and a VoucherTypeID and a
VoucherNo. The VoucherNo must be unique for a vouchertype and within a
financial year (1st April - 31st March).

SQL Server cannot create a unique index with this criteria. Hence, I had
to split the tables.

*** Sent via Developersdex http://www.developersdex.com ***|||Bill Bob (nospam@.devdex.com) writes:
> Each Voucher Table contains a VoucherDate and a VoucherTypeID and a
> VoucherNo. The VoucherNo must be unique for a vouchertype and within a
> financial year (1st April - 31st March).
> SQL Server cannot create a unique index with this criteria. Hence, I had
> to split the tables.

Of course it can! You would add Year as a column in the table, and the
primary key would be (Year, VoucherTypeID, VoucherNo).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx