compare the variance of sales from one day to another on the same table

Question

I got this table on a SQL Server named "sales" that shows the sales per day for each vendor, this is the current table:
+-------+-----------+-----------+--------------+
|   id  |   vendor  |   sales   |    date      |
+-------+-----------+-----------+--------------+
|   1   |   John    |     10    |   07-20      |
|   2   |   John    |     5     |   07-20      |
|   3   |   Jeff    |     15    |   07-21      |
|   4   |   Jeff    |     20    |   07-21      |
|   5   |   John    |     5     |   07-21      |
|   5   |   Jeff    |     30    |   07-20      |

and I would like to transform it into this table below I need to group by vendor and compare the columns of the sales for each day
   +-----------+--------------+-------------------+-----------
   | vendor    |sales  07/20  |  sales  07/21     | Variance |
   +-----------+--------------+-------------------+-----------
   |   John    |       15     |       5           |   -10    |
   |   Jeff    |        30    |       35          |    5     |

Dominique Boucher · Answer

There is probably a better way to do it but here's how I get the expected result.
I went with dynamic SQL as your date will change and that was the only way I can think of to get different column name depending on the content of the table.
/* Creating a temp table to reproduce */
create table #demo (id int identity(1,1), vendor varchar(30), sales int, [date] date);
insert into #demo (vendor, sales, [date])
values ('John',10,'2020-07-20'), ('John',5,'2020-07-20'), ('Jeff',15,'2020-07-21'), ('Jeff',20,'2020-07-21'), ('John',5,'2020-07-21'), ('Jeff',30,'2020-07-20');
--Adding data with another date to make sure the query works when there will be multiple days
insert into #demo (vendor, sales, [date])
values ('John',8,'2020-07-19'), ('Jeff',12,'2020-07-19');

declare @maxDate date;
declare @mindate date;
select @maxDate = max([date]) from #demo;
set @mindate = dateadd(day,-1,max(@maxDate));

declare @cmd varchar(max)
set @cmd ='
with Last2Days as (
select vendor, sum(sales) sales, [date]
from #demo 
group by vendor, [date]
having date between convert(date,'''+convert(varchar(10),@mindate)+''') and convert(date,'''+convert(varchar(10),@maxdate)+''')
)

select  vendor, ['+convert(varchar(10),@mindate)+'] as "Sale '+convert(varchar(10),@mindate)+'", ['+convert(varchar(10),@maxdate)+'] as "Sale '+convert(varchar(10),@maxDate)+'", pv.['+convert(varchar(10),@maxdate)+']-pv.['+convert(varchar(10),@minDate)+'] "Variance" 
from 
(select vendor, [date], sales from Last2Days) as s
Pivot
( max(sales) for [date] in ([2020-07-20] , [2020-07-21] )) as pv'

exec (@cmd);

drop table #demo;

To recap, I create a CTE to get only the data from the last 2 days. I also sum up the sales value per day/vendor.
From that CTE, I ran a select with a PIVOT to get the output expected.

compare the variance of sales from one day to another on the same table

One Answer

Add your own answers!

Ask a Question