Rolling up prices
-
[font="Courier New"]The is my first post - so please bear with me if you can - cheers.
I think this is a fairly common problem but can't find a solution anywhere - probably because I can't express it with sufficient clarity - never mind here goes...
We've got customers (SoldTo) with multiple delivery sites (ShipTo) and want to display the most common ShipTos as a rolled up price breakdown, and the remainder as simple price breakdown.
So consider:
SoldTo ShipTo PriceType Value
STA SH1 List 100
STA SH1 Discount -10
STA SH1 Net 90
STA SH2 List 100
STA SH2 Discount -10
STA SH2 Delivery 5
STA SH2 Net 95
STA SH3 List 100
STA SH3 Discount -10
STA SH3 Net 90
STA SH4 List 100
STA SH4 Discount -10
STA SH4 Delivery 5
STA SH4 Temp disc -5
STA SH4 Net 90
So prices for SH1 and SH3 can be rolled up, wherea since SH2 is different then it won't, and even though SH4 has the same Net price the breakdown is different. So we would see something like:
SoldTo ShipTo PriceType Value
STA null List 100
STA null Discount -10
STA null Net 90
STA SH2 List 100
STA SH2 Discount -10
STA SH2 Delivery 5
STA SH2 Net 95
STA SH4 List 100
STA SH4 Discount -10
STA SH4 Delivery 5
STA SH4 Temp disc -5
STA SH4 Net 90
Now is there any way to do this without resorting to cursors? Potentially we've got many thousands of prices breakdowns to generate.
Any help is most gratefully received.
Cheers, Mark Picton
[/font]
-
Something like this?
WITH CTE AS (SELECT SoldTo,
ShipTo,
PriceType,
Value,
COUNT(*) OVER(PARTITION BY SoldTo,ShipTo) AS GrpCount
FROM MyTable)
SELECT SoldTo,
CASE WHEN COUNT(*)>1 THEN NULL ELSE MAX(ShipTo) END AS ShipTo,
PriceType,
Value
FROM CTE
GROUP BY SoldTo,PriceType,Value,GrpCount
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Something like this ?
Create Table #YourTab
(
SoldTo char(3),
ShipTo char(3),
PriceType varchar(30),
Value integer
)
go
delete from #YourTab
go
insert into #YourTab
select 'STA','SH1','List', 100
union
select 'STA','SH1','Discount', -10
union
select 'STA','SH1','Net', 90
union
select 'STA','SH2','List', 100
union
select 'STA','SH2','Discount', -10
union
select 'STA','SH2','Delivery', 5
union
select 'STA','SH2','Net', 95
union
select 'STA','SH3','List', 100
union
select 'STA','SH3','Discount', -10
union
select 'STA','SH3','Net', 90
union
select 'STA','SH4','List', 100
union
select 'STA','SH4','Discount', -10
union
select 'STA','SH4','Delivery', 5
union
select 'STA','SH4','Temp disc', -5
union
select 'STA','SH4','Net', 90;
go
with ctePivot
as
(
select SoldTo,ShipTo,max([List]) as List,max([Discount]) as Discount,max([Net]) as Net,max([Delivery]) as Delivery ,max([Temp disc]) as TempDisc
from
(Select SoldTo,ShipTo,PriceType,Value
from #yourtab ) as inp
pivot(
max(Value)
for PriceType in([List],[Discount],[Net],[Delivery],[Temp disc])
) as pvt
group by SoldTo,ShipTo
),
cteCounter
as
(
select *,count(*) over (partition by Discount , Net , Delivery , TempDisc ) as counter
from ctePivot
),
cteRank
as
(
select *,dense_rank() over (order by Counter desc,Discount , Net , Delivery , TempDisc ) as Ranker
from cteCounter
),
cteRow
as
(
Select * ,row_number() over (order by Ranker) as Rown
from cteRank
)
Select * from cteRow
where Ranker >1 or Rown=1
-
genius - that works a treat - with a little tweaking it'll work on my full data set - i really do need to get my head properly around the use of CTE, i've only really used them for hiearchy work and they can do so much more!
-
thanks for that - i like this as it offers pretty much what i want to display - unfortunately i didn't mention that i would need a variable number of columns as the price breakdown can be one of many price condition types
-
This will be just one of the many challenges you will face in an EAV model.
My code (or any variation of it) will require a finite number of attributes.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply