johnnyaction
asked on
SQL Syntax
I have a table that I need to write a report off of. It has 2 columns Sales and LYSales. I need to show the value of Sales for the parameter used for the week but I also need to SUM Sales and LYSales for the month(every record has a week and month field). So for instance I have this query to bring back the data I need for Sales and LYSales
Select StoreID, Store, SUM(Sales), SUM(LYSales)
from tableA
Where week = '42'
GROUP BY StoreID, Store
How could I get the all the Sales and LYSales for the month and not just the week. I was thinking a UDF that I pass in the StoreID and the Month to return the SUM for the month just not exactly sure how to do it? Or is there an easier way to extarct the Months Sales in the same query above?
Any help???
Select StoreID, Store, SUM(Sales), SUM(LYSales)
from tableA
Where week = '42'
GROUP BY StoreID, Store
How could I get the all the Sales and LYSales for the month and not just the week. I was thinking a UDF that I pass in the StoreID and the Month to return the SUM for the month just not exactly sure how to do it? Or is there an easier way to extarct the Months Sales in the same query above?
Any help???
You could do something like this (I assume you have a date field associated with each sale...):
Select StoreID, Store, SUM(CASE WHEN date <= monthstart and date >= monthend THEN Sales ELSE 0 END), SUM(CASE WHEN date >=yearstart and date <= yearend THEN LYSales ELSE 0)
from tableA
GROUP BY StoreID, Store
Select StoreID, Store, SUM(CASE WHEN date <= monthstart and date >= monthend THEN Sales ELSE 0 END), SUM(CASE WHEN date >=yearstart and date <= yearend THEN LYSales ELSE 0)
from tableA
GROUP BY StoreID, Store
I notice I missed the week requirement, in which case add SUM(CASE WHEN week = '42' THEN Sales ELSE 0 END)
Also, I missed END at the end of the second CASE statement above.
Also, I missed END at the end of the second CASE statement above.
ASKER
Sample data...
Store ID Store week Month Sales $ LY Sales $
1 DT SEATTLE 38 9 12065.15 10484.9
1 DT SEATTLE 39 9 9338.4 9083.1
1 DT SEATTLE 40 10 7673.9 14020.18
1 DT SEATTLE 41 10 6502 7511.6
1 DT SEATTLE 42 10 6681.45 11544.18
Store ID Store week Month Sales $ LY Sales $
1 DT SEATTLE 38 9 12065.15 10484.9
1 DT SEATTLE 39 9 9338.4 9083.1
1 DT SEATTLE 40 10 7673.9 14020.18
1 DT SEATTLE 41 10 6502 7511.6
1 DT SEATTLE 42 10 6681.45 11544.18
In that case, to get week 42:
Select StoreID, Store, SUM(CASE WHEN week = '42' THEN Sales ELSE 0 END) as current_week, SUM(CASE WHEN week = '42' THEN LYSales ELSE 0 END) as last_year_week, SUM(CASE WHEN MONTH = '10' THEN Sales ELSE 0 END) as current_month, SUM(CASE WHEN MONTH='10' THEN LYSales ELSE 0 END) as last_year_month
from tableA
GROUP BY StoreID, Store
Select StoreID, Store, SUM(CASE WHEN week = '42' THEN Sales ELSE 0 END) as current_week, SUM(CASE WHEN week = '42' THEN LYSales ELSE 0 END) as last_year_week, SUM(CASE WHEN MONTH = '10' THEN Sales ELSE 0 END) as current_month, SUM(CASE WHEN MONTH='10' THEN LYSales ELSE 0 END) as last_year_month
from tableA
GROUP BY StoreID, Store
ASKER
Only problem would be is that the week would be the only parameter. So a 42 wuold be passed in and I would have to come up with the number for Sales for that week, figure out what month it is and get the Sum of Sales for the month
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nice work my friend!!
just to clarify...