September 26, 2012 at 4:44 am
I have three Views for a report:
ViewOne is called: SalesByClass_One
SELECT Invoice_Date, UserId, InvoiceNo, SUM(Invoice_Val) AS Invoice_Value, SUM(Cost_Pr) AS Cost_Price, Class
FROM dbo.Sales
GROUP BY UserId, Invoice_Date, InvoiceNo, Class
ViewTwo is called SalesByClass_Two
SELECT UserId, Class, SUM(Invoice_Value) AS Sum_InvoiceValue, SUM(Cost_Price) AS Sum_CostPrice, Invoice_Date
FROM dbo.SalesByClass_One
GROUP BY UserId, Class, Invoice_Date
ViewThree is called SalesByClass_Three
SELECT Class, Invoice_Date, SUM(Sum_InvoiceValue) AS SumofInvoiceValue, SUM(Sum_CostPrice) AS SumofCostPrice, (SUM(Sum_InvoiceValue)
- SUM(Sum_CostPrice)) / SUM(Sum_InvoiceValue) * 100 AS Margin
FROM dbo.SalesByClass_Two
GROUP BY Class, Invoice_Date, Sum_InvoiceValue, Sum_CostPrice
In Business Intelligence studio i have created a report with the following dataset:
SET DATEFORMAT DMY
SELECT Class,Invoice_Date, SumofInvoiceValue, SumofCostPrice, Margin
FROM SalesByClass_Three
WHERE (Invoice_Date BETWEEN @startdate AND @enddate)
If i search for one day i get the result:
Class Invoice_Date SumofInvoice SumofCostPrice Margin
ETH 01/09/2012 75.060 685.56 9.320
GEN 01/09/2012 235.56 788.56 5.389
GNR 01/09/2012 598.56 487.46 19.32
PI 01/09/2012 789.56 489.56 2.60
However if i search on two dates i get the following.
Class Invoice_Date SumofInvoice SumofCostPrice Margin
ETH 01/09/2012 75.060 685.56 9.320
GEN 01/09/2012 235.56 788.56 5.389
GNR 01/09/2012 598.56 487.46 19.32
PI 01/09/2012 789.56 489.56 2.60
ETH 02/09/2012 458.58 478.56 58.598
GEN 02/09/2012 245.58 145.56 2.589
GNR 02/09/2012 325.56 369.46 23.58
PI 02/09/2012 248.56 598.56 5.89
I have used the GROUP in my SQL.
I would like my reports to Sum everything for each class.
So basically i could search from a start date to an enddate and have the total sum for the days searched.
Please could you show how i could total classs between search dates.
Thank you.
September 26, 2012 at 5:51 am
SQL is behaving as you told it as it is grouping correctly. What you will need to do is remove the date field from the data set so that it can combine the rows together as that is the thing which is stopping the grouping.
September 26, 2012 at 7:19 am
I need to Select the Invoice_Date as it is used in the WHERE clause to identify the date being searched on:
SET DATEFORMAT DMY
SELECT Class,Invoice_Date, SumofInvoiceValue, SumofCostPrice, Margin
FROM SalesByClass_Three
WHERE (Invoice_Date BETWEEN @startdate AND @enddate)
I have removed the invoice_date and the results I am now getting are in this format:
Class SumofInvoiceValue, SumofCostPrice, Margin
ETH
GEN
GNR
PI
DRG
ETH
GEN
GNR
PI
DRG
ETH
GEN
GNR
PI
DRG
I would like to be able to search between two dates e.g. Sept 05 to Sept 09
Then i would like the total results in the format:
Class SumofInvoiceValue, SumofCostPrice, Margin
ETH
GEN
GNR
PI
DRG
Any help would be appreciated.
September 26, 2012 at 7:23 am
You would want to do something like this
SELECT Class, sum(SumofInvoiceValue), sum(SumofCostPrice), Margin
FROM SalesByClass_Three
WHERE (Invoice_Date BETWEEN @startdate AND @enddate)
group by class, margin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply