May 15, 2016 at 3:07 am
Hi All,
i need some help with the below table to show summary of changes in price and it's percentage
DECLARE @table1 TABLE (
itmId int,
name varchar(50)
)
DECLARE @prices TABLE (
itmId int,
price money
)
INSERT INTO @table1 VALUES (1, 'item1')
INSERT INTO @table1 VALUES (2, 'item2')
INSERT INTO @table1 VALUES (3, 'item3')
INSERT INTO @table1 VALUES (4, 'item4')
INSERT INTO @prices VALUES (1, 1000)
INSERT INTO @prices VALUES (1, 1500)
INSERT INTO @prices VALUES (1, 1800)
INSERT INTO @prices VALUES (2, 2000)
INSERT INTO @prices VALUES (2, 2500)
INSERT INTO @prices VALUES (2, 2700)
INSERT INTO @prices VALUES (2, 2900)
INSERT INTO @prices VALUES (3, 3000)
INSERT INTO @prices VALUES (3, 3300)
INSERT INTO @prices VALUES (3, 3900)
INSERT INTO @prices VALUES (4, 4000)
INSERT INTO @prices VALUES (4, 4700)
i need the result to show as below: (or at least for last 3 prices)
itmId price1 price2 price3 Pr ....................%p3 %p4
1 1000 1500 1800 0.50 0.20=(1800-1500)/1500
2 2000 2500 2700 2900 0.25 0.08 0.07
3 3000 3300 3900 0.10 0.18
4 4000 4900 0.225
May 15, 2016 at 3:15 am
do you have anything in your prices table that indicates order....eg datetime / Id ?
I think you need that to determine which price is Price1/Price2/ etc
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 15, 2016 at 3:26 am
no, only the itmId
may we can determine the highest one then the second then the third ?!
May 15, 2016 at 3:30 am
well you could use ROW_NUMBER to do that....but what happens when a price drops?
what you suggest will only work if proces are continualy increasing.
I find it very hard to believe that if this is a business system that there are no identifiers.....most systems would have an effective from date for each price at the very minimum.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 15, 2016 at 4:07 am
lets say that in our bussiness the price always increasing or fixed.
May 15, 2016 at 4:23 am
maybe....use following and then do a crosstab
WITH cteprices as (
SELECT itmId,
price,
ROW_NUMBER() OVER(PARTITION BY itmId ORDER BY price) rn
FROM @prices
)
SELECT itmId,
price,
rn,
(price - lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn)) / lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn) inc
FROM cteprices;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 12:29 pm
Hi J Livingston,
thanks and appreciate ur assist.:-):-)
I've trying all day to do the cross tab by myself with no luck then finally i export it to access database and it works fine.
do you know an easy way to be excellent in cross tab 🙂 ...i need to make it in sql.
May 16, 2016 at 12:47 pm
maybe...
DECLARE @prices TABLE (
itmId int,
price money
)
INSERT INTO @prices VALUES (1, 1000)
INSERT INTO @prices VALUES (1, 1500)
INSERT INTO @prices VALUES (1, 1800)
INSERT INTO @prices VALUES (2, 2000)
INSERT INTO @prices VALUES (2, 2500)
INSERT INTO @prices VALUES (2, 2700)
INSERT INTO @prices VALUES (2, 2900)
INSERT INTO @prices VALUES (3, 3000)
INSERT INTO @prices VALUES (3, 3300)
INSERT INTO @prices VALUES (3, 3900)
INSERT INTO @prices VALUES (4, 4000)
INSERT INTO @prices VALUES (4, 4700);
WITH cteprices as (
SELECT itmId,
price,
ROW_NUMBER() OVER(PARTITION BY itmId ORDER BY price) rn
FROM @prices
)
,
priceinc as(
SELECT itmId,
price,
rn,
(price - lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn)) / lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn) inc
FROM cteprices
)
SELECT itmId,
MAX(CASE when rn = 1 THEN price END ) Price1,
MAX(CASE when rn = 2 THEN price END ) Price2,
MAX(CASE when rn = 3 THEN price END ) Price3,
MAX(CASE when rn = 1 THEN inc END ) Price1inc,
MAX(CASE when rn = 2 THEN inc END ) Price2inc,
MAX(CASE when rn = 3 THEN inc END ) Price3inc
FROM priceinc
GROUP BY itmId
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 12:56 pm
just to remind you again....without any columns to order your prices by, you are leaving yourself open to report the wrong results.
making an assumption today. that prices will for now, and forever increase is going to bite you or your successor in the arse one day.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 12:56 pm
J Livingston SQL (5/15/2016)
maybe....use following and then do a crosstab
WITH cteprices as (
SELECT itmId,
price,
ROW_NUMBER() OVER(PARTITION BY itmId ORDER BY price) rn
FROM @prices
)
SELECT itmId,
price,
rn,
(price - lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn)) / lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn) inc
FROM cteprices;
I don't think that the ROW_NUMBER is necessary. The following should work just as well.
WITH cteprices as (
SELECT itmId,
price,
LAG(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY price) AS prev_price
FROM @prices
)
SELECT itmId,
price,
rn,
(price - prev_price)/prev_price inc
FROM cteprices;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2016 at 1:14 pm
drew.allen (5/16/2016)
J Livingston SQL (5/15/2016)
maybe....use following and then do a crosstab
WITH cteprices as (
SELECT itmId,
price,
ROW_NUMBER() OVER(PARTITION BY itmId ORDER BY price) rn
FROM @prices
)
SELECT itmId,
price,
rn,
(price - lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn)) / lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn) inc
FROM cteprices;
I don't think that the ROW_NUMBER is necessary. The following should work just as well.
WITH cteprices as (
SELECT itmId,
price,
LAG(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY price) AS prev_price
FROM @prices
)
SELECT itmId,
price,
rn,
(price - prev_price)/prev_price inc
FROM cteprices;
Drew
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 1:15 pm
drew.allen (5/16/2016)
J Livingston SQL (5/15/2016)
maybe....use following and then do a crosstab
WITH cteprices as (
SELECT itmId,
price,
ROW_NUMBER() OVER(PARTITION BY itmId ORDER BY price) rn
FROM @prices
)
SELECT itmId,
price,
rn,
(price - lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn)) / lag(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY rn) inc
FROM cteprices;
I don't think that the ROW_NUMBER is necessary. The following should work just as well.
WITH cteprices as (
SELECT itmId,
price,
LAG(price, 1, NULL) OVER(PARTITION BY itmId ORDER BY price) AS prev_price
FROM @prices
)
SELECT itmId,
price,
rn,
(price - prev_price)/prev_price inc
FROM cteprices;
Drew
Hi Drew, you are probably right, got caught up in thinbking about no defintive order on prices
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 1:29 pm
Although, if you are eventually going to "pivot" the data, it's probably better to use the ROW_NUMBER and skip the LAG/LEAD.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2016 at 4:19 am
Dears drew.allen & J Livingston thanks a lot
both ways working exellent
and also the cross tab provided by 'Livingston' is perfect except that i should desided how many colums do i need and i have to make it manually using "MAX(CASE when rn = 1 THEN price END ) Price1"
is there a way to let sql to do it automaticaly?
May 17, 2016 at 10:57 am
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply