CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2019
Teja Goud Kandula
Teja Goud Kandula

Reputation: 1574

Compare current month sales with previous month sales

Code to get the test data:

create table SalesCalls
(
    EmpId INT NOT NULL,
    EmpName nvarchar(20),
    month INT,
    Year INT,
    CallsMade INT
)
GO

Insert into SalesCalls values
(1,'ABC',12,2018,10),
(1,'ABC',1,2019,15),
(1,'ABC',2,2019,20),
(2,'DEF',12,2018,12),
(2,'DEF',1,2019,14),
(2,'DEF',2,2019,26)
GO

The objective is to compare the current month sales of an Employee with the previous month sales of that Employee and find out the percentage change in it. Achieved that using the below query:

With SalesCTE as 
(
    select EmpId,EmpName,
        Month As CurrentMonth,
        Year as CurrentMonthYear,
        Case When month = 1 then 12 Else (Month-1) End AS PrevMonth,
        Case when month = 1 then (Year - 1) Else Year End As PrevMonthYear,
        CallsMade 
    from SalesCalls
)
select 
    S1.EmpId, S1.EmpName, S1.CurrentMonth, S1.CurrentMonthYear, S1.CallsMade as CurrentMonthCalls,
    S2.CurrentMonth as PrevMont,
    S2.CurrentMonthYear as PrevMonthYear,
    S2.CallsMade as PrevMonthCalls,
    ( CONVERT(numeric(5,2),S1.CallsMade) / S2.CallsMade) * 100 As PercentageChange
from SalesCTE S1
JOIN SalesCTE S2 ON S1.EmpId = S2.EmpId
    AND S1.PrevMonth = S2.CurrentMonth   
    AND S1.PrevMonthYear = S2.CurrentMonthYear
ORDER BY S1.EmpId, S1.CurrentMonth, S1.CurrentMonthYear

The above query worked until the time there are no redundant records for an Employee for the same month.

But later data from multiple sources is coming in and an Employee table can have multiple records for the same month and it is still valid. Because the employee could be making calls in different ways. An as example the below record is inserted into the table:

Insert into SalesCalls values
(1,'ABC',1,2019,1)

Now the above query which worked fine above for the comparison of current month SalesCalls with the previous month is no longer working.

Phase 2 of the use case: So to fix this I have build an intermediate temp table that contains aggregate data. The query used is:

Select EmpId, EmpName, month, Year, SUM(CallsMade) as CallsMade 
into #SalesCalls
from SalesCalls
group by EmpId, EmpName, month, Year

Now the SalesCalls table inside the CTE is replaced with #SalesCalls and then the above query works fine.

But this #SalesCalls table needs to be dropped and recreated every time to see the latest comparison data.

The question is, is it possible to get the comparison data using a single query only and no intermediate temp tables or views.

Upvotes: 1

Views: 6538

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271181

Just use window functions:

select EmpId, EmpName, month, Year,
       sum(CallsMade) as CallsMade,
       (case when lag(year * 12 + month) over (partition by empId order by year, month) = year * 12 + month - 1
             then lag(sum(callsMade)) over (partition by empId order by year, month)
        end) as prevMonthCalls,
       (case when lag(year * 12 + month) over (partition by empId order by year, month) = year * 12 + month - 1
             then callsMade * 100.0 / lag(sum(callsMade) over (partition by empId order by year, month)
        end) as as perentageChange
from SalesCalls
group by EmpId, EmpName, month, Year;

No joins, CTEs, subqueries, or temporary tables are needed at all.

Upvotes: 2

Teja Goud Kandula
Teja Goud Kandula

Reputation: 1574

One of the simplest solutions :

select EmpId, EmpName, month, Year,
       sum(CallsMade) as CallsMade,
        lag(sum(callsMade)) over (partition by empId order by year, month) AS prevMonthCalls,
        sum(CallsMade) * 100.0 / lag(sum(CallsMade)) over (partition by empId order by year, month) as PercentageChange
from SalesCalls
group by EmpId, EmpName, month, Year
order by EmpId,  Year,month;

Upvotes: 0

Related Questions