I'm creating a webbased report that print all costs of a vehicle during a specific period of time, usually a month.
I've created a view called Invoices that lists all costs of all vehicles in the system (detail rows). Then I have a stored procedure that query that view with a number of parameters, nothing strange. I loop the result and print it to the webpage. The report can be up to 100 vehicles and is printed in just seconds. My customer thought the report was too large and wanted to have the fuel/gas costs grouped per month and gas company. My first thought was to run a stored procedure on each vehicle and retreive that data. Perfomance seems to be terrible doing that and I've tried differents ways to come around this.
The result of my invoice view looks like this:
LicenseNo Organisation InvoiceNo TypeOfCost Company DateOfPurchase Cost Description
ABC 123 Org1 2342 Fuel Chevron 4/12/04 12.34 Regular
ABC 123 Org1 2342 Fuel Chevron 6/12/04 23.45 Regular
ABC 123 Org1 53422 Fuel Exxon 7/12/04 34.23 Regular
I want to create a stored procedure that gives the output:
Month Company MonthCost
December Chevron 35.79
December Exxon 34.23
This can be done like this:
SELECT DATEPART(month, DateofPurchase) AS 'Month',
Company,
SUM(Cost) AS 'MonthCost'
FROM Invoices
WHERE DateOfPurchase >= @date_from AND DateOfPurchase <= @date_to
AND TypeOfCost = 'Fuel'
AND LicenseNo = @license_no
GROUP BY DATEPART(month, DateofPurchase), Company
However this is what gives the bad performance. I'm thinking of creating a second view based on Invoices that has already calculated the sum of each vechicle per month and company but I need to have DateOfPurchase to be able to enter search critera and it ruins the Group by clause, anyone who can help me?
I've created a view called Invoices that lists all costs of all vehicles in the system (detail rows). Then I have a stored procedure that query that view with a number of parameters, nothing strange. I loop the result and print it to the webpage. The report can be up to 100 vehicles and is printed in just seconds. My customer thought the report was too large and wanted to have the fuel/gas costs grouped per month and gas company. My first thought was to run a stored procedure on each vehicle and retreive that data. Perfomance seems to be terrible doing that and I've tried differents ways to come around this.
The result of my invoice view looks like this:
LicenseNo Organisation InvoiceNo TypeOfCost Company DateOfPurchase Cost Description
ABC 123 Org1 2342 Fuel Chevron 4/12/04 12.34 Regular
ABC 123 Org1 2342 Fuel Chevron 6/12/04 23.45 Regular
ABC 123 Org1 53422 Fuel Exxon 7/12/04 34.23 Regular
I want to create a stored procedure that gives the output:
Month Company MonthCost
December Chevron 35.79
December Exxon 34.23
This can be done like this:
SELECT DATEPART(month, DateofPurchase) AS 'Month',
Company,
SUM(Cost) AS 'MonthCost'
FROM Invoices
WHERE DateOfPurchase >= @date_from AND DateOfPurchase <= @date_to
AND TypeOfCost = 'Fuel'
AND LicenseNo = @license_no
GROUP BY DATEPART(month, DateofPurchase), Company
However this is what gives the bad performance. I'm thinking of creating a second view based on Invoices that has already calculated the sum of each vechicle per month and company but I need to have DateOfPurchase to be able to enter search critera and it ruins the Group by clause, anyone who can help me?