asked on

SQL queries for sales / expenses / profit calculation

I have DAILY sales data for 3 products in this form: TABLE1
Product1|SaleAmount|UnitsSold|TimeStamp (there are multiple records for each day)
Product2|SaleAmount|UnitsSold|TimeStamp (there are multiple records for each day)
Product2|SaleAmount|UnitsSold|TimeStamp (there are multiple records for each day)

I have DAILY expense data for 3 products in this form: TABLE2
Product1|ExpenseAmount|ExpenseType|TimeStamp (there are multiple records for each day)
Product2|ExpenseAmount|ExpenseType|TimeStamp (there are multiple records for each day)
Product2|ExpenseAmount|ExpenseType|TimeStamp (there are multiple records for each day)
TimeStamp in in YEAR:MONTH:DAY:HH:MM:SS

I need a few SQL statements:
1. I want to show the following columns on a MONTH BY MONTH basis for each product:
Product1|SaleAmountForMonth|UnitsSoldForMonth
Product2|SaleAmountForMonth|UnitsSoldForMonth
Product3|SaleAmountForMonth|UnitsSoldForMonth
Thus, the rows will contain the name of each product; there will be ONE row for each product for each month.
The Month will show as Jan-2018, Feb-2018

2. I also want to show the following columns on a MONTH BY MONTH basis for each product:
Product1|ExpenseAmountForMonth|ExpenseTypeForMonth
Product2|ExpenseAmountForMonth|ExpenseTypeForMonth
Product3|ExpenseAmountForMonth|ExpenseTypeForMonth
Thus, the rows will contain the name of each product; there will be MULTIPLE ROWS for each product for each month, i.e. the number of rows for each month will equal the number of expenses incurred. The Month will show as Jan-2018, Feb-2018

3. For each month, I then want to show the profit for each product in a table. The formula for each product:
Total sales for that product for that month - Total expenses of ALL types for that product for that month
Will I need to use a UNION or JOIN for this? Please note that I do not need to persist this information anywhere.

Raja Jegan R

This should help..

-- 1
SELECT ProductId,SUM(SaleAmount) SaleAmountForMonth, SUM(UnitsSold) UnitsSoldForMonth, REPLACE(RIGHT(convert(varchar(20),timestamp, 106),8),' ','-') Month_val
FROM Table1 
GROUP BY ProductId,REPLACE(RIGHT(convert(varchar(20),timestamp, 106),8),' ','-')
-- 2
SELECT ProductId,SUM(ExpenseAmount) ExpenseAmountForMonth, ExpenseType ExpenseTypeForMonth, REPLACE(RIGHT(convert(varchar(20),timestamp, 106),8),' ','-') Month_val
FROM Table2
GROUP BY ProductId, ExpenseType, REPLACE(RIGHT(convert(varchar(20),timestamp, 106),8),' ','-')
-- 3
SELECT Table1.ProductId,SUM(SaleAmount) - SUM(ExpenseAmount) ProfitPerMonth, REPLACE(RIGHT(convert(varchar(20),Table1.timestamp, 106),8),' ','-') Month_val
FROM Table1
JOIN Table2 on Table1.ProductId = table2.ProductId
GROUP BY Table1.ProductId, REPLACE(RIGHT(convert(varchar(20),Table1.timestamp, 106),8),' ','-')

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.