# Track Sales Quantity

Hi. I'm trying to achieve a few objectives

Track the quantities sold for each item in our inventory by year.
Track the difference in quantity sales between months of Year 1 and Year 2

``````Data

Order Number	Order Date	Item		Quantity	Category
A12345		 4 Jan 2014	Pencil		5		Stationery
A12346		16 Jan 2014	Pencil		5		Stationery
A12347		 2 Feb 2014	Pencil		20		Stationery
A12348		 5 Feb 2014	Pencil		40		Stationery
---------------------------------------------------------------------------
A12351		15 Jan 2015	Pencil		15		Stationery
A12352		21 Jan 2015	Pencil		5		Stationery
A12353		11 Feb 2015	Pencil		30		Stationery
A12354		 2 Mar 2015	Ruler		20		Stationery
A12355		12 Mar 2015	Ruler		20		Stationery

Search Parameter
SET @Year1 = 2014
SET @Year2 = 2015

Search Results
Item	2014	2015	 Change
Pencil	70	50	 -28.6%
Ruler	 0	40	4000.0%
``````

Any help is much appreciated.

Don't know how you calculate "change" colomn, but try this:

``````select item
,sum(case when datepart(yy,[order date])=@year1 then quantity else 0 end) as y1
,sum(case when datepart(yy,[order date])=@year2 then quantity else 0 end) as y2
from yourtable
group by item``````

Try like this!

with cte
as
(select *
from(
select DATEPART(YYYY,orddate) year,item,quan
from test2 ) as sales
pivot(sum(quan)
for year in ([2014],[2015]) ) as pivotsales)
select *,
case when ([2014] > [2015]) THEN (-1) * CAST ((([2015] * 100.00)/[2014]) as decimal(8,2))
else CAST ((([2015] * 100.00)/[2014]) as decimal(8,2))
end as change
from cte

In the above query the hard coded column names 2014 and 2015 will be replaced by variable names

Hi

DECLARE @Year1 int = 2014
DECLARE @Year2 int = 2015

;
WITH abc_cte
AS (
SELECT item
,sum(CASE
WHEN year(orderdate) = @Year1
THEN quantity
ELSE 0
END) AS Quan2014
,sum(CASE
WHEN year(orderdate) = @Year2
THEN quantity
ELSE 0
END) AS Quan2015
FROM #temp
GROUP BY item
)
SELECT item
,quan2014
,quan2015
,cast(cast(((quan2015 + 0.0 - quan2014) / replace(quan2014, 0, 1)) * 100 AS DECIMAL(19, 1)) AS VARCHAR) + '%'
FROM abc_cte

/*
drop table #temp
create table #temp
(
OrderNumber varchar(100) null,
OrderDate datetime null,
Item varchar(100) null,
Quantity int null,
Category varchar(100) null
)
insert into #Temp select 'A12345','4-Jan-2014','Pencil',5,'Stationery'
insert into #Temp select 'A12346','16 Jan 2014','Pencil',5,'Stationery'
insert into #Temp select 'A12347',' 2 Feb 2014','Pencil',20,'Stationery'
insert into #Temp select 'A12348',' 5 Feb 2014','Pencil',40,'Stationery'
insert into #Temp select 'A12351','15 Jan 2015','Pencil',15,'Stationery'
insert into #Temp select 'A12352','21 Jan 2015','Pencil',5,'Stationery'
insert into #Temp select 'A12353','11 Feb 2015','Pencil',30,'Stationery'
insert into #Temp select 'A12354',' 2 Mar 2015','Ruler',20,'Stationery'
insert into #Temp select 'A12355','12 Mar 2015','Ruler',20,'Stationery'

select * from #temp
*/

Hi

Please can you tell me how you got ur
sql to be like this .. i mean the window
with sql inside .. which is so nice ..
Thanks

Hi

I am posting the same thing again with nice formatting

``````DECLARE  @Year1 int  = 2014
DECLARE  @Year2 int  = 2015

;
WITH abc_cte
AS (
SELECT item
,sum(CASE
WHEN year(orderdate) = @Year1
THEN quantity
ELSE 0
END) AS Quan2014
,sum(CASE
WHEN year(orderdate) = @Year2
THEN quantity
ELSE 0
END) AS Quan2015
FROM #temp
GROUP BY item
)
SELECT item
,quan2014
,quan2015
,cast(cast(((quan2015 + 0.0 - quan2014) / replace(quan2014, 0, 1)) * 100 AS DECIMAL(19, 1)) AS VARCHAR) + '%'
FROM abc_cte
``````

/*
drop table #temp
create table #temp
(
OrderNumber varchar(100) null,
OrderDate datetime null,
Item varchar(100) null,
Quantity int null,
Category varchar(100) null
)
insert into #Temp select 'A12345','4-Jan-2014','Pencil',5,'Stationery'
insert into #Temp select 'A12346','16 Jan 2014','Pencil',5,'Stationery'
insert into #Temp select 'A12347',' 2 Feb 2014','Pencil',20,'Stationery'
insert into #Temp select 'A12348',' 5 Feb 2014','Pencil',40,'Stationery'
insert into #Temp select 'A12351','15 Jan 2015','Pencil',15,'Stationery'
insert into #Temp select 'A12352','21 Jan 2015','Pencil',5,'Stationery'
insert into #Temp select 'A12353','11 Feb 2015','Pencil',30,'Stationery'
insert into #Temp select 'A12354',' 2 Mar 2015','Ruler',20,'Stationery'
insert into #Temp select 'A12355','12 Mar 2015','Ruler',20,'Stationery'

select * from #temp
*/

Highlight code and press the [</>] button. That indents your code with 4 spaces so, personally, I hate that as it wrecks the indentation when the user copy&pastes it
or put

``````    ```
...
```
``````

around your code. Not all keyboards have that "back-tick" key though

or put

``````[code]
..
[/code]
``````

around your code. That is obviously easier for phones etc. BUT ... it loses any blank lines

If you don't do anything you run the risk that the leading spaces in code formatting cause some. but not all, of it to be formatted; also any @XXX which appears in plain-text sections will be treated as a notification to a user (on this forum) of the same name