Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spatel
Starting Member
2 Posts |
 Posted - 2011-10-07 : 14:56:18
|
| Hello All,We have 20 stores with daily sales broken by the 2 district managers. We are using TOAD Data Analysts Software to create these reports. When creating reports to sum the sales it works fine, however when summing percentages it takes it at face value and just sums the numbers instead of doing sum(sales)/sum(lysales) for example. AND this stupid program won't allow me to add multiple data sets into the report writer ( i figured initially i could just create a view with the manager name and the sum % value and bring that data into the report writer)....ANYWAY I have the following query and would like to add the real % value next to each employeename before i take the data into the report writer.SELECT CompSalesView.StoreNumber, TLO_EmployeeList.EmployeeName, Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.CashSales else 0 END) AS Today_Cash, Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.LYCash else 0 END) As Today_LYCash, Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.CashSales else 0 END) - Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.LYCash else 0 END) As Today_Change, (Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.CashSales else 0 END) - Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.LYCash else 0 END)) /Sum(CASE WHEN date = calendar__gregorian__yesterday__ THEN CompSalesView.LYCash else 0 END) As Pct_Today, Sum(CASE WHEN date <= getdate() THEN CompSalesView.CashSales else 0 END) AS WTD_Cash, Sum(CASE WHEN date <= getdate() THEN CompSalesView.LYCash else 0 END) AS WTD_LYCash, Sum(CASE WHEN date <= getdate() THEN CompSalesView.CashSales else 0 END) - Sum(CASE WHEN date <= getdate() THEN CompSalesView.LYCash else 0 END) AS WTD_Change, (Sum(CASE WHEN date <= getdate() THEN CompSalesView.CashSales else 0 END) - Sum(CASE WHEN date <= getdate() THEN CompSalesView.LYCash else 0 END)) / Sum(CASE WHEN date <= getdate() THEN CompSalesView.LYCash else 0 END) AS Pct_WTD FROM (Finance.dbo.LocationTable LocationTable INNER JOIN Finance.db_owner.CompSalesView CompSalesView ON (LocationTable.LocationInt = CompSalesView.StoreNumber)) INNER JOIN Finance.dbo.TLO_EmployeeList TLO_EmployeeList ON (LocationTable.DM = TLO_EmployeeList.EmployeeID)WHERE (CompSalesView.Date = calendar__gregorian__current_week__) GROUP BY StoreNumber, EmployeeName WITH CUBESo lets just say the john had total sales for his 10 stores = 100k and last yr it was 98k that would mean its a 2% increase. So i would like this 2% to be listed in a new coloumn.Disregard the dates in this query, toad software has this cool tool to select specfic date periods unlike sql's getdate function...any help would be much appreciated |
|
|
spatel
Starting Member
2 Posts |
Posted - 2011-10-07 : 16:16:09
|
| never mind....my brain is absolutly fried and missed the obvious (just add the view to my initial design..lol) |
 |
|
|
|
|
|
|
|
Sum Column