April 23, 2010 at 2:54 pm
I read bunch of post here but couldnt able to find out and could not able to come up with the result i want.
Here is the data i have
Account Prod_Code Amount
F1008100391 36415 218.90
F1008100391 36415 401.90
F1008100391 37210 218.90
F1008100391 37210 401.90
F1008100391 80048 218.90
F1008100391 80048 401.90
F1008100391 81025 218.90
F1008100391 81025 401.90
Now what I would like to see is
Account Prod_Code Amount_1 Amount_2
F1008100391 36415 218.90 401.90
F1008100391 37210 218.90 401.90
F1008100391 80048 218.90 401.90
F1008100391 81025 218.90 401.90
I have read lot of post but they are with Aggregate function but for me i do not need that functionality here the only thing i need is my Amount column need to splite in two by product_code. Since product has regular valude and markdown value.
Is this possbile?
Please advice,
April 23, 2010 at 4:46 pm
Assuming Amount_1 and Amount_2 will always be in a consistent order (ege. being able to sort it by and ID column which is unfortunately not present (or at least not mentioned) you could use a subquery or CTE together with: ROW_NUMBER() OVER (PARTITION BY Account, Prod_Code ORDER BY ID)
Based on that you could use the "standard" CrossTab. Don't get confused by the aggregation used within the CrossTab - you'll need it as well. The aggregation is used to eliminate the values from the CASE statement that you don't want to display.
There are two ways to figure it out: follow the related link in my signature and modify the sample to meet your requirement or post some ready to use sample data as described in the first link in my signature to get a coded and tested answer.
April 23, 2010 at 5:14 pm
As Imu92 said, you don't give us a lot of details about how the data will look, but assuming it is all consistent with your sample and there are only two price/amount records per account/Prod_Code...
How about a self join?
SELECT P1.Account, P1.Prod_Code, P1.Amount, P2.Amount
FROM ProdTable P1 JOIN ProdTable P2
on P1.Account=P2.Account
AND P1.Prod_Code = P2.Prod_Code
AND P1.Amount < P2.Amount
Rob Schripsema
Propack, Inc.
April 23, 2010 at 5:21 pm
Rob Schripsema (4/23/2010)
As Imu92 said, you don't give us a lot of details about how the data will look, but assuming it is all consistent with your sample and there are only two price/amount records per account/Prod_Code...How about a self join?
SELECT P1.Account, P1.Prod_Code, P1.Amount, P2.Amount
FROM ProdTable P1 JOIN ProdTable P2
on P1.Account=P2.Account
AND P1.Prod_Code = P2.Prod_Code
AND P1.Amount < P2.Amount
If you don't care about performance at all, yes, it might be an option 😉
And what will the code look like if there is a need to have 5 or 6 codes? :w00t:
April 23, 2010 at 5:32 pm
lmu92 (4/23/2010)
Rob Schripsema (4/23/2010)
As Imu92 said, you don't give us a lot of details about how the data will look, but assuming it is all consistent with your sample and there are only two price/amount records per account/Prod_Code...How about a self join?
SELECT P1.Account, P1.Prod_Code, P1.Amount, P2.Amount
FROM ProdTable P1 JOIN ProdTable P2
on P1.Account=P2.Account
AND P1.Prod_Code = P2.Prod_Code
AND P1.Amount < P2.Amount
If you don't care about performance at all, yes, it might be an option 😉
And what will the code look like if there is a need to have 5 or 6 codes? :w00t:
Exactly - and that's why I preceded my suggestion with the caveat that the data would only look like what he gave in the example. For THAT data structure, and a small number of records (two records per product code), I'd doubt performance would be an issue. And the self-join was a relatively simple way to solve the problem.
Rob Schripsema
Propack, Inc.
April 23, 2010 at 6:14 pm
keyun (4/23/2010)
I read bunch of post here but couldnt able to find out and could not able to come up with the result i want.Here is the data i have
Account Prod_Code Amount
F1008100391 36415 218.90
F1008100391 36415 401.90
F1008100391 37210 218.90
F1008100391 37210 401.90
F1008100391 80048 218.90
F1008100391 80048 401.90
F1008100391 81025 218.90
F1008100391 81025 401.90
Now what I would like to see is
Account Prod_Code Amount_1 Amount_2
F1008100391 36415 218.90 401.90
F1008100391 37210 218.90 401.90
F1008100391 80048 218.90 401.90
F1008100391 81025 218.90 401.90
I have read lot of post but they are with Aggregate function but for me i do not need that functionality here the only thing i need is my Amount column need to splite in two by product_code. Since product has regular valude and markdown value.
Is this possbile?
Please advice,
The real problem with your data (other than it not being in a readily consumable format for us) is that you have absolutely nothing that allows for any method to determine what the previous row is. The data can only be checked for the max amount and the max amount that's not greater than the max amount. In the data you have, it's just as easy for the "latest" amount to be less than the max amount.
Despite what anyone posts, if you trully mean "previous row", then you must have something by date or ID that keeps track of the order of the rows.
--Jeff Moden
April 24, 2010 at 5:55 am
Guys,,,this is very good feed back for me to start work on your suggestion. Please accept my big apology that i didnt provide you detail. But in some case where product has more than two price due to Promotion and all other factors.
Let me start work on your suggestion and follow the link that you guys have provider and get back with you in a day.
Note* The unique column i have is customer Account Number which is first column and thats key which associate with Product.
April 24, 2010 at 6:00 am
Imu92,
I think i need to try your Row_Number() function where you have supply Account, Prod_code as partition by .. I am going to try to use your method and see what i can come up with.
Thanks a million. 🙂
April 24, 2010 at 12:58 pm
Rob, Sorry about not providing more detail but it is true that in some case where Product has more than two price. It is very difficult to say that each product code has fix two different price, Some case product has one price and other product contain up to five different price.
April 24, 2010 at 1:28 pm
Imu92 & Rob,
Here is my sample code where i found out how to do cross tab by using 92's suggestion on RoW_NUMBER() function.
DROP TABLE ##TBL1
CREATE TABLE ##TBL1
( Account varchar(20),
Prod_code numeric(18,0),
Amount Money
)
INSERT INTO ##TBL1 select 'F1008100391','36415','218.90'
INSERT INTO ##TBL1 select 'F1008100391','36415','401.90'
INSERT INTO ##TBL1 select 'F1008100391','37210','218.90'
INSERT INTO ##TBL1 select 'F1008100391','37210','401.79'
INSERT INTO ##TBL1 select 'F1008100391','80048','218.56'
INSERT INTO ##TBL1 select 'F1008100391','80048','401.90'
INSERT INTO ##TBL1 select 'F1008100391','80048','319.89'
INSERT INTO ##TBL1 select 'F1008100391','80048','699.19'
INSERT INTO ##TBL1 select 'F1008100391','81025','218.90'
INSERT INTO ##TBL1 select 'F1008100391','81025','401.90'
INSERT INTO ##TBL1 select 'F1008100391','81025','399.90'
select * from ##TBL1
Drop Table ##TBL2
select *, ROW_NUMBER() OVER (PARTITION BY Account, Prod_Code ORDER BY Account) as ProcductLineNo
INTO ##TBL2
From ##TBL1
Order by 1
-- 11
SELECT Account,
Prod_Code,
MAX(CASE WHEN ProcductLineNo = '1' THEN Amount END ) AS Amount_1,
MAX(CASE WHEN ProcductLineNo = '2' THEN Amount END ) AS Amount_2,
MAX(CASE WHEN ProcductLineNo = '3' THEN Amount END ) AS Amount_3,
MAX(CASE WHEN ProcductLineNo = '4' THEN Amount END ) AS Amount_4
FROM ##TBL2
GROUP BY Account, Prod_Code
Order by Account
Now, only the problem is that, as of today i know that there are maximum four different price per product but in case in future if it increase how would i know that i need to add # of Max () statement on my last query. Currently, above script is fine but let me know if you guys have any idea to write a script where it will pull data based on number of "ProductLineNo"??
Again, I really thanks to you guys that you responde me very quickly.:-P
Looking forward to hear from you soon.
Thanks.
April 24, 2010 at 2:49 pm
keyun (4/24/2010)
Imu92 & Rob,Here is my sample code where i found out how to do cross tab by using 92's suggestion on RoW_NUMBER() function.
DROP TABLE ##TBL1CREATE TABLE ##TBL1
( Account varchar(20),
Prod_code numeric(18,0),
Amount Money
)
INSERT INTO ##TBL1 select 'F1008100391','36415','218.90'
INSERT INTO ##TBL1 select 'F1008100391','36415','401.90'
INSERT INTO ##TBL1 select 'F1008100391','37210','218.90'
INSERT INTO ##TBL1 select 'F1008100391','37210','401.79'
INSERT INTO ##TBL1 select 'F1008100391','80048','218.56'
INSERT INTO ##TBL1 select 'F1008100391','80048','401.90'
INSERT INTO ##TBL1 select 'F1008100391','80048','319.89'
INSERT INTO ##TBL1 select 'F1008100391','80048','699.19'
INSERT INTO ##TBL1 select 'F1008100391','81025','218.90'
INSERT INTO ##TBL1 select 'F1008100391','81025','401.90'
INSERT INTO ##TBL1 select 'F1008100391','81025','399.90'
select * from ##TBL1
Drop Table ##TBL2
select *, ROW_NUMBER() OVER (PARTITION BY Account, Prod_Code ORDER BY Account) as ProcductLineNo
INTO ##TBL2
From ##TBL1
Order by 1
-- 11
SELECT Account,
Prod_Code,
MAX(CASE WHEN ProcductLineNo = '1' THEN Amount END ) AS Amount_1,
MAX(CASE WHEN ProcductLineNo = '2' THEN Amount END ) AS Amount_2,
MAX(CASE WHEN ProcductLineNo = '3' THEN Amount END ) AS Amount_3,
MAX(CASE WHEN ProcductLineNo = '4' THEN Amount END ) AS Amount_4
FROM ##TBL2
GROUP BY Account, Prod_Code
Order by Account
Now, only the problem is that, as of today i know that there are maximum four different price per product but in case in future if it increase how would i know that i need to add # of Max () statement on my last query. Currently, above script is fine but let me know if you guys have any idea to write a script where it will pull data based on number of "ProductLineNo"??
Again, I really thanks to you guys that you responde me very quickly.:-P
Looking forward to hear from you soon.
Thanks.
I'll say it again... this is "Slow and Painful Death By SQL" because there is absolutely nothing in the data that indicates its temporal position. The table needs an IDENTITY column or a DATETIME column or, better yet in case of DateTime Ties, BOTH.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply