Track Sales Quantity

Transact-SQL
1

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.

2

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
 where [order date]>=dateadd(yy,@year1-1900,0)
   and [order date]<dateadd(yy,@year2-1899,0)
 group by item
3

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

4

Hi

Please find attached ur solution

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
*/

5

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

6

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
*/

7

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 :frowning:

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 :frowning: