SQLTeam.com | Weblogs | Forums

Strategy


#1

As a newbie in SQL I am trying to do a comparison of sales data with in one file. There is one record per sale so I have to sum up all the sales per customer for the time period. I want to show a customers sales for the current year/month compared to the previous year/month but all the data is in the same table.

I want to show for example Customer Name, 1/2016 Units Sales, 1/2016 Sales $$$, 1/2016 GP $$$, 1/2015 Unit Sales, 1/2015 Sales $$$, 1/2015 GP $$$.

I am thinking go thru my customer table and calling a stored procedure that has parameters of customer number and dates to calculate sales from.. Am I on the right track or over thinking it?


#2

This can be done with a SELECT statement using GROUP BY and aggregates.
Look at SUM.

If you get stuck let us know.


#3

How would I do a select statement and get two separate sums, I would need to sum say Customer 123's sales for say January of 2016 and then do another sum to get their sales for January of 2015. How could you do that in 1 select statement?


#4

I was going to suggest a couple of SUM(CASE statements but I realized I do not know what your data looks like.

Please give the structure of the table, some sample data and the expected output.
In other words
the create statement for the table,
consumable data INSERT INTO ... VALUES
and expected output sample

Thank you,


#5

Yep, CASE is incredibly useful. Something like this:

SELECT 
    tn.customer_name AS [Customer Name],
    SUM(CASE WHEN tn.sales_date >= current_month THEN tn.units ELSE 0 END) AS [1/2016 Units Sales],
    SUM(CASE WHEN tn.sales_date >= current_month THEN tn.sales_amount ELSE 0 END) AS [1/2016 Sales $$$],
    SUM(CASE WHEN tn.sales_date >= current_month THEN tn.[?col_name?] ELSE 0 END) AS [1/2016 GP $$$],
    SUM(CASE WHEN tn.sales_date <  current_month THEN tn.units ELSE 0 END) AS [1/2015 Units Sales],
    SUM(CASE WHEN tn.sales_date <  current_month THEN tn.sales_amount ELSE 0 END) AS [1/2015 Sales $$$],
    SUM(CASE WHEN tn.sales_date <  current_month THEN tn.[?col_name?] ELSE 0 END) AS [1/2015 GP $$$]
FROM dbo.table_name tn
CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS current_month,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) AS prior_year_month
) AS assign_alias_names
WHERE
    ((tn.sales_date >= prior_year_month AND tn.sales_date < DATEADD(MONTH, 1, prior_year_month)) OR
     (tn.sales_date >= current_month AND tn.sales_date < DATEADD(MONTH, 1, current_month)))
GROUP BY tn.customer_name
ORDER BY tn.customer_name

#6

Here is my code that I now have working how I want except for one thing. I need to take an entered period (like 2016-01 for January 2016) and be able to subtract one from the year to get the previous year period (IE 2015-01). @Period is my entered parameter and I need to change where I hard coded '2015-01' to be created off of what the entered parameter is.

SELECT asr.[ASR_Name] as ASRName
,asr.[CustomerId] as CustNum
,asr.[Name] as CustomerName
,asr.[ADDRESS1] as Address_1
,asr.[ADDRESS2] as Address_2
,asr.[City] as City
,asr.[State] as State
,asr.[RF_ZIP_ID] as ZipCode
,asr.[PHONE] as Phone_Num
,SUM(CASE WHEN ws.OlapDate = @Period THEN ws.Units ELSE 0 END) AS [Current Year Month Units Sales]
,SUM(CASE WHEN ws.OlapDate = @Period THEN ws.Sales ELSE 0 END) AS [Current Year Month Sales $$$]
,SUM(CASE WHEN ws.OlapDate = '2015-01' THEN ws.Units ELSE 0 END) AS [Prior Year Month Units Sales]
,SUM(CASE WHEN ws.OlapDate = '2015-01' THEN ws.Sales ELSE 0 END) AS [Prior Year Month Sales $$$]
FROM [TD_SOURCE].[dbo].[ASR500] asr LEFT OUTER JOIN

[TD_REPORTS].[dbo].[Whl_CustomerTireSales] ws ON asr.[CustomerId] = ws.[CustomerId]

Group By asr.[Name],asr.[ASR_NAME],asr.[CustomerId],asr.[ADDRESS1],asr.[ADDRESS2],asr.[City],asr.[State],asr.[RF_ZIP_ID],asr.[PHONE]
Order by asr.ASR_Name,asr.[Name]


#7
SELECT asr.[ASR_Name] as ASRName
,asr.[CustomerId] as CustNum
,asr.[Name] as CustomerName
,asr.[ADDRESS1] as Address_1
,asr.[ADDRESS2] as Address_2
,asr.[City] as City
,asr.[State] as State
,asr.[RF_ZIP_ID] as ZipCode
,asr.[PHONE] as Phone_Num
,[Current Year Month Units Sales]
,[Current Year Month Sales $$$]
,[Prior Year Month Units Sales]
,[Prior Year Month Sales $$$]
FROM [TD_SOURCE].[dbo].[ASR500] asr LEFT OUTER JOIN (
    SELECT CustomerId
    ,SUM(CASE WHEN OlapDate = @Period THEN Units ELSE 0 END) AS [Current Year Month Units Sales]
    ,SUM(CASE WHEN OlapDate = @Period THEN Sales ELSE 0 END) AS [Current Year Month Sales $$$]
    ,SUM(CASE WHEN OlapDate = CAST(CAST(LEFT(@period, 4) AS smallint) - 1 AS char(4)) + SUBSTRING(@Period, 5, 10)
              THEN Units ELSE 0 END) AS [Prior Year Month Units Sales]
    ,SUM(CASE WHEN OlapDate = CAST(CAST(LEFT(@period, 4) AS smallint) - 1 AS char(4)) + SUBSTRING(@Period, 5, 10)
              THEN Sales ELSE 0 END) AS [Prior Year Month Sales $$$]
    FROM [TD_REPORTS].[dbo].[Whl_CustomerTireSales]
    WHERE OlapDate IN (@period, CAST(CAST(LEFT(@period, 4) AS smallint) - 1 AS char(4)) + SUBSTRING(@Period, 5, 10))
) AS ws ON asr.[CustomerId] = ws.[CustomerId]
ORDER BY asr.ASR_Name,asr.[Name]