asked on

SQL Query comparing previous date and identify top sales increase week

Hi,

I have data below,needs to identify top sales increase week,need to compare week wise and identify which week made highest sales increase. below query answer is 23 (Increase) column.How to achieve this please

Week Sale Increase
20 10000 0
21 10023 23
22 10012 12

Thanks

HainKurt

what is your data?
this looks like a summary data

other wise

select top 1 from sales order by increase desc

Open in new window

is what you need

HainKurt

if this is not actual data. then it should be

Week    Sale          Increase
20          10000          0
21          10023          23
22          10012          -11

Open in new window

I guess :) if it is based on previous week

Pawan Kumar

Pls provide some sample data and expected output in tabular format.

Chandra Sekhar

ASKER

@HainKurt yes, you are spot on..I am looking based on previous week

HainKurt

maybe this

with t as (
select 20 week,  10000 Sale
union all select 21, 10023
union all select 22, 10012
),
s as (
select *, isnull(sale-lag(sale,1) over(order by week),0) Increase
  from t
)
select top 1 * from s order by Increase desc

week	Sale	Increase
21	10023	23

Open in new window

HainKurt

order by increase desc

with t as (
select 20 week,  10000 Sale
union all select 21, 10023
union all select 22, 10012
),
s as (
select *, 
       isnull(sale-lag(sale,1) over(order by week),0) Increase
  from t
)
select *, row_number() over (order by Increase desc) rn
  from s
 order by rn

week	Sale	Increase	rn
21	10023	23	1
20	10000	0	2
22	10012	-11	3

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chandra Sekhar

ASKER

Thanks @HainKurt really appreciate your help