Rolling up prices
-
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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply