How can i efficiently genereate reports in MS SQL 2005

I am quit new to programming and trying my best to learn. I search the internet to find a solution for my situation but could not find any. So i hope there is someone who can help me. I am gonna try to explain as clear as possible my situation.

I am trying to generate (sales) reports. The application (c#) is combining mssql2005. I can generate reports but it is not efficient and when there is big data it takes ages to generate.

I have the next tables which i use for generating reports (i'll filter the important columns)

table VAT

• -int Id
• -int Value

table invoice

• -int Ticketnumber
• -Datetime Date
• -nvarchar(50) SellerId

table articleline

• -int linenumber
• -int Ticketnumber
• -decimal(18,3) Amount
• -decimal(18,2) Price
• -nvarchar(50) Item
• -decimal(18,2) Discount
• -int VatId

table transaction

• -int linenumber
• -int Ticketnumber
• -nvarchar(20) PayMethod (Card, Cash or Mix)
• -decimal(18,2) TicketSum (Sum of invoice)
• -decimal(18,2) Received (received amount from customer)
• -decimal(18,2) ExtraMoney (can withdraw extra money if paying card)
• -decimal(18,2) DiscountAmount (sum of discount of the invoice)
• -decimal(18,2) MixPaymentCard (if payed mix, sum here of card payment)
• -decimal(18,2) MixPaymentCash (if payed mix, sum here of cash payment)
• -nvarchar SumRounding (if rounding is active f.e 3,99 --> 4,00 or 3,97 --> 3,95 rounding options can be All, OnlyCash or None)

How I start

What i am doing is running code below. I found this code online and editted for my needs which works for me. I think I know how it works. It is looping though my VAT's and generates for every VAT a small report

DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectVATID int DECLARE @SelectVATValue int

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), Id int, Value int ) INSERT into @table1 (Id, value) SELECT Id, Value FROM VAT SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectVATId=Id, @SelectVATValue = Value FROM @table1 WHERE RowID=@CurrentRow

SELECT Articleline.Ticketnumber, sum((Articleline.Price * Articleline.Amount) - IsNull(Articleline.Discount,0)) AS Revenue, Transaction.PayMethod, Transaction.SumRounding, Articleline.VAT

FROM Articleline INNER JOIN Invoice ON Articleline.Ticketnumber = Invoice.Ticketnumber INNER JOIN Transaction ON Transaction.Ticketnumber = Invoice.Ticketnumber WHERE (Invoice.Date between @dateStart and @dateEnd) AND Articleline.VAT = @SelectVATID group by Articleline.Ticketnumber, Transaction.PayMethod, Transaction.SumRounfing, ArtiCleline.VAT END

Here below an example of the result. I get multiple results

result 1 (VAT 0%)

Ticketnumber     Revenue       PayMethod      Rounding       VAT
.                ..            ..             ..             ..

result 2 (VAT 6%)

Ticketnumber     Revenue       PayMethod      Rounding       VAT
1                17.25000      Cash           All            2
2                1.55000       Card           All            2
3                3.25000       Cash           All            2

result 3 (VAT 21%)

Ticketnumber     Revenue       PayMethod      Rounding       VAT
3                7.89000       Cash           All            3
4                4.95000       Card           All            3
5                18.95000      Mix            All            3

After I get these result tables from mssql I do a loop in C# to SUM the revenue. I do it this way so can calculate the VAT sum's in C# because this way i get them seperated.

After this I do another mssql query

select * from Transaction INNER JOIN Invoice ON Transaction.Ticketnumber = Invoice.Ticketnumber WHERE (Invoice.Date between @DateStart and @DateEnd)

this will give me the next result

Result transaction

TicketNr   Method   Sum   Received   ExtraMony   Discount   MixCard   MixCash
1          Cash     17.25 20.00      0           0          0         0
2          Card     1.55  1.55       0           0          0         0
3          Cash     11.14 20.00      0           0          0         0
4          Card     4.95  14.95      10.00       0          0         0
5          Mix      18.95 18.95      0           0          8.95      10.00

From here I loop though the table to get the sum's for cash payments, card payments, discounts etc.

From here I show the numbers on the screen. When the data is not that much it goes smooth and is generated instantly. But when there are lots of data it can take long time to finish which is a pain in the but. I hope there is someone who can help me. Maybe i can calculate all of this in 1 sql query or something.