Decimal calculation problem

[abhishekpant]

Table A

PRODUCTID PRICEDATE PRICE SOURCE
1 20080102 10.12 A
1 20080102 10.13 B
1 20080102 10.12 C
1 20080102 10.12 D
2 20080102 43.23 A
2 20080102 43.26 B
2 20080102 43.23 C
2 20080102 43.26 D

I have a table A with these fields PRODUCTID,PRICEDATE,PRICE,SOURCE
I need to find out which product id has differnt price from different souce

i.e suppose if for instance we take productid 1 , it has same price from 3 different source A,c,and D
and the price from source B is differ from .01

so in the result the need something like this'



PRODUCTID PRICEDATE PRICE SOURCE PRICE DIFFERENCE DIFFERENCESOURCE
1 20080102 10.12 A,C,D .01 B


Can someone help

Thanks
Abhishek

 

[jdaman]

What if the price listed for A, B, and C are all different?

 

[abhishekpant]

That will not be the case at all throughtout the table but if thats the case than we should show all the prices for that product...i.e all different prices


Thanks
ABhishek

 

[jdaman]

Sorry for the late reply...

If you have a static number of sources the following (untested) code should work:

DECLARE @a TABLE(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE CHAR(1) )

INSERT @a ( PRODUCTID, PRICEDATE, PRICE, SOURCE )
SELECT 1, '20080102', 10.12, 'A' UNION ALL
SELECT 1, '20080102', 10.13, 'B' UNION ALL
SELECT 1, '20080102', 10.12, 'C' UNION ALL
SELECT 1, '20080102', 10.12, 'D' UNION ALL
SELECT 2, '20080102', 43.23, 'A' UNION ALL
SELECT 2, '20080102', 43.26, 'B' UNION ALL
SELECT 2, '20080102', 43.23, 'C' UNION ALL
SELECT 2, '20080102', 43.26, 'D'

SELECT  PRODUCTID,
        PRICEDATE,
        CASE WHEN COLA IS NOT NULL THEN COLA
             WHEN COLB IS NOT NULL THEN COLB
             WHEN COLC IS NOT NULL THEN COLC
             ELSE COLD
        END AS MINPRICE,
        LEFT( CASE WHEN A IS NULL THEN 'A,' ELSE '' END +
              CASE WHEN B IS NULL THEN 'B,' ELSE '' END +
              CASE WHEN C IS NULL THEN 'C,' ELSE '' END +
              CASE WHEN D IS NULL THEN 'D,' ELSE '' END,
              LEN( CASE WHEN A IS NULL THEN 'A,' ELSE '' END +
                   CASE WHEN B IS NULL THEN 'B,' ELSE '' END +
                   CASE WHEN C IS NULL THEN 'C,' ELSE '' END +
                   CASE WHEN D IS NULL THEN 'D,' ELSE '' END ) - 1 ) AS MINSOURCES,
        LEFT( CASE WHEN A IS NOT NULL THEN 'A,' ELSE '' END +
              CASE WHEN B IS NOT NULL THEN 'B,' ELSE '' END +
              CASE WHEN C IS NOT NULL THEN 'C,' ELSE '' END +
              CASE WHEN D IS NOT NULL THEN 'D,' ELSE '' END,
              LEN( CASE WHEN A IS NOT NULL THEN 'A,' ELSE '' END +
                   CASE WHEN B IS NOT NULL THEN 'B,' ELSE '' END +
                   CASE WHEN C IS NOT NULL THEN 'C,' ELSE '' END +
                   CASE WHEN D IS NOT NULL THEN 'D,' ELSE '' END ) - 1 ) AS INCREASE_SOURCES,
        CASE WHEN A IS NOT NULL THEN A
             WHEN B IS NOT NULL THEN B
             WHEN C IS NOT NULL THEN C
             ELSE D
        END AS PRICE_INCREASE
FROM ( SELECT  PRODUCTID,
               PRICEDATE,
               DIFFSOURCE,
               PRICEDIFF,
               A AS COLA,
               B AS COLB,
               C AS COLC,
               D AS COLD
       FROM ( SELECT  a.PRODUCTID,
                      a.PRICEDATE,
                      a.SOURCE,
                      a.PRICE,
                      aa.SOURCE as DIFFSOURCE,
                      aa.PRICE as DIFFPRICE,
                      aa.PRICE - a.PRICE as PRICEDIFF
              FROM    @a a
                      JOIN @a aa ON a.PRODUCTID = aa.PRODUCTID
              WHERE   a.PRICE < aa.PRICE ) a
       PIVOT ( MAX(a.PRICE) FOR a.SOURCE IN ( [A], [B], [C], [D] ) ) pvt ) a
PIVOT ( MAX(a.PRICEDIFF) FOR a.DIFFSOURCE IN ( [A], [B], [C], [D] ) ) pvt

Output:
PRODUCTID PRICEDATE MINPRICE MINSOURCES INCREASE_SOURCES PRICE_INCREASE
----------- ----------------------- --------------------------------------- ---------- ---------------- ---------------------------------------
1 2008-01-02 00:00:00.000 10.12 A,C,D B 0.01
2 2008-01-02 00:00:00.000 43.23 A,C B,D 0.03

 

[Qik3Coder]

I thought there had to be an easier way. I just didn't think it would kill me...

Two things:
1. All you have to do is load the #baseTable however you want, then hit go.
2. It doesnt matter how many diffent suppliers, or price differences you have.

help taken from faq183-6485

If someone knows a way to clean the bolded rows in the result set, I am listening.

Result Set:
ProductID Price Source ExpPrice ExpSource
----------- ---------- -------------------- ---------- --------------------
1 10.12 A,C,D 10.13 B
2 43.23 A,C 43.26 B,D
3 10.00 A,B 10.01 C
3 10.00 A,B 10.02 D
3 10.01 C 10.02 D

Drop table #baseTable
Create Table #baseTable(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE CHAR(1))

INSERT #baseTable ( PRODUCTID, PRICEDATE, PRICE, SOURCE )
SELECT 1, '20080102', 10.12, 'A' UNION ALL
SELECT 1, '20080102', 10.13, 'B' UNION ALL
SELECT 1, '20080102', 10.12, 'C' UNION ALL
SELECT 1, '20080102', 10.12, 'D' UNION ALL
SELECT 2, '20080102', 43.23, 'A' UNION ALL
SELECT 2, '20080102', 43.26, 'B' UNION ALL
SELECT 2, '20080102', 43.23, 'C' UNION ALL
SELECT 2, '20080102', 43.26, 'D' UNION ALL

SELECT 3, '20080102', 10.00, 'A' UNION ALL
SELECT 3, '20080102', 10.00, 'B' UNION ALL
SELECT 3, '20080102', 10.01, 'C' UNION ALL
SELECT 3, '20080102', 10.02, 'D'

drop table #groupedProducts
Create Table #groupedProducts(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE CHAR(1),
ExpSource Char(1),
ExpPrice Decimal(8,2),
Done char(1))

Insert into #groupedProducts
select tbl1.*, tbl2.source, tbl2.Price, ''
from #baseTable tbl1
Inner join #baseTable tbl2 on
tbl1.productId = tbl2.productId
and tbl1.price <> tbl2.price
and tbl1.price < tbl2.price
order by tbl1.productID, tbl1.price, tbl2.price
--select distinct productid, price, source, expsource, expprice from #groupedProducts order by productID, price, source, expSource, expPrice

--Create return table
drop Table #outProducts
Create Table #outProducts(
PRODUCTID INT,
PRICE DECIMAL(8,2),
SOURCE CHAR(2000),
ExpSource Char(2000),
ExpPrice Decimal(8,2))

--Create table to hold products to "walk"
drop table #Products
Create Table #Products(
ProductID int,
Price Decimal(8,2),
ExpPrice Decimal(8,2),
done char(1))
--select * from #products

Insert into #Products
Select distinct ProductID, Price, ExpPrice, ''
From #groupedProducts
order by productID

--Setup Loop variables
Declare @flag as varchar(10)
set @flag = 'Not Done'
Declare @Source as varchar(800)
Declare @ExpSource as varchar(800)

Declare @curProductID as int
Declare @curPrice as Decimal(8,2)
Declare @curExpPrice as Decimal(8,2)

While(@flag='Not Done')
Begin
   --Clean variables
   Select @curProductID = 0, @curPrice = 0, @curExpPrice = 0
   --Get next product to work
   Select Top 1 @curProductID = ProductID, @curPrice = Price, @curExpPrice = ExpPrice
   from #products 
   where done = ''
   Print 'XXXX---'+rtrim(@curProductID)

   if @curProductID > 0
   Begin
	-- Clean the variable to concat all the row info
	Select @Source = '' --Add leading text here if needed

	-- in the select add the variable to itself and the value in the column and a comma --
	select @Source = @Source + cast(a.Source as varchar(200)) + ','
	from (
		Select distinct Source 
		from #groupedProducts 
		where productID = @curProductID 
		and price = @curPrice
		and expPrice = @curExpPrice) as a
	-- Retrieve vaules and strip of the last comma--
	select @Source = left(@Source,len(@Source)-1)
	--Select @Source

	--Clean the variable to concat all the row info
	Select @ExpSource ='' --Add leading text here if needed

	-- in the select add the variable to itself and the value in the column and a comma --
	select @ExpSource = @ExpSource + cast(a.ExpSource as varchar(200)) + ','
	from (
		Select distinct ExpSource 
		from #groupedProducts 
		where productID = @curProductID 
		and price = @curPrice
		and ExpPrice = @curExpPrice) as a
	-- Retrieve vaules and strip of the last comma--
	select @ExpSource = left(@ExpSource,len(@ExpSource)-1)
	--Select @ExpSource

	--Load Table with values
	Insert into #outProducts
	Select Distinct b.ProductID, b.Price, 
	@Source, @ExpSource, b.ExpPrice 
	FROM #groupedProducts b
	where b.ProductID = @curProductID
	and b.Price = @curPrice
	and b.expPrice = @curExpPrice
	
	update #products
	set done='X'
	where 
	ProductID = @curProductID
	and Price = @curPrice
	and ExpPrice = @curExpPrice
print rtrim(@curProductId) + '-' + rtrim(@curPrice) + '-' + rtrim(@curExpPrice)
   End
   Else
   Begin
	set @flag = 'Done'
   End
End


Select ProductID, Price, Left(Source,20) as Source, ExpPrice, Left(ExpSource,20) as ExpSource from #outProducts

-Sometimes the answer to your question is the hack that works

 

[jdaman]

I thought there had to be an easier way. I just didn't think it would kill me..."

Qik3Coder,

It really seems like it should be easier but, as my code attests to, I didnt find that easy solution.

I do see where I have a problem displaying multiple different prices for a product in my code. One solution I thought of was just remove the ugly case statement and provide the price increase for each product like this:

DECLARE @a TABLE(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE CHAR(1) )

INSERT @a ( PRODUCTID, PRICEDATE, PRICE, SOURCE )
SELECT 0, '20080102', 10.12, 'A' UNION ALL
SELECT 0, '20080102', 10.12, 'B' UNION ALL
SELECT 0, '20080102', 10.12, 'C' UNION ALL
SELECT 0, '20080102', 10.12, 'D' UNION ALL
SELECT 1, '20080102', 10.12, 'A' UNION ALL
SELECT 1, '20080102', 10.13, 'B' UNION ALL
SELECT 1, '20080102', 10.12, 'C' UNION ALL
SELECT 1, '20080102', 10.12, 'D' UNION ALL
SELECT 2, '20080102', 43.23, 'A' UNION ALL
SELECT 2, '20080102', 43.26, 'B' UNION ALL
SELECT 2, '20080102', 43.23, 'C' UNION ALL
SELECT 2, '20080102', 43.26, 'D'

SELECT  pvt.PRODUCTID,
        pvt.PRICEDATE,
        MIN(a.PRICE) AS BASE_PRICE,
        ISNULL(MAX(pvt.A), 0) AS A_PRICE_INCREASE,
        ISNULL(MAX(pvt.B), 0) AS B_PRICE_INCREASE,
        ISNULL(MAX(pvt.C), 0) AS C_PRICE_INCREASE,
        ISNULL(MAX(pvt.D), 0) AS D_PRICE_INCREASE
FROM ( SELECT  PRODUCTID,
               PRICEDATE,
               DIFFSOURCE,
               PRICEDIFF,
               A AS COLA,
               B AS COLB,
               C AS COLC,
               D AS COLD
       FROM ( SELECT  a.PRODUCTID,
                      a.PRICEDATE,
                      a.SOURCE,
                      a.PRICE,
                      aa.SOURCE as DIFFSOURCE,
                      aa.PRICE as DIFFPRICE,
                      aa.PRICE - a.PRICE as PRICEDIFF
              FROM    @a a
                      JOIN @a aa ON a.PRODUCTID = aa.PRODUCTID
              WHERE   a.PRICE < aa.PRICE
              UNION ALL
              SELECT  DISTINCT
                      a.PRODUCTID,
                      a.PRICEDATE,
                      aa.SOURCE,
                      aa.PRICE,
                      aa.SOURCE as DIFFSOURCE,
                      aa.PRICE as DIFFPRICE,
                      aa.PRICE - a.PRICE as PRICEDIFF
              FROM    @a a
                      JOIN @a aa ON a.PRODUCTID = aa.PRODUCTID
              WHERE   a.PRICE < aa.PRICE ) a
       PIVOT ( MAX(a.PRICE) FOR a.SOURCE IN ( [A], [B], [C], [D] ) ) pvt ) a
PIVOT ( MAX(a.PRICEDIFF) FOR a.DIFFSOURCE IN ( [A], [B], [C], [D] ) ) pvt
JOIN @a a ON a.PRODUCTID = pvt.PRODUCTID
GROUP BY pvt.PRODUCTID,
         pvt.PRICEDATE

But that doesnt give him the format he was asking for.

 

[jdaman]

forgot to post the result set:
PRODUCTID PRICEDATE BASE_PRICE A_PRICE_INCREASE B_PRICE_INCREASE C_PRICE_INCREASE D_PRICE_INCREASE
----------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 2008-01-02 00:00:00.000 10.12 0.00 0.01 0.00 0.00
2 2008-01-02 00:00:00.000 43.23 0.00 0.03 0.00 0.03

 

[Qik3Coder]

Jdaman, I'm not trying to rag on your code, but it is dependant upon the "source", both their quantity, and their actual names. The code example I put together is vendor independant.

I didn't include a "difference" column in the table structure, because that's just a calculated field.

What happens when you use this dataset:

Drop table #baseTable
Create Table #baseTable(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE VARCHAR(20))

INSERT #baseTable ( PRODUCTID, PRICEDATE, PRICE, SOURCE )
SELECT 1, '20080102', 10.12, 'Alaska' UNION ALL
SELECT 1, '20080102', 10.13, 'Brazil' UNION ALL
SELECT 1, '20080102', 10.12, 'China' UNION ALL
SELECT 1, '20080102', 10.12, 'D' UNION ALL
SELECT 2, '20080102', 43.23, 'Alaska' UNION ALL
SELECT 2, '20080102', 43.26, 'BBarbosa' UNION ALL
SELECT 2, '20080102', 43.23, 'C' UNION ALL
SELECT 2, '20080102', 43.26, 'D' UNION ALL

SELECT 3, '20080102', 10.00, 'Alamo' UNION ALL
SELECT 3, '20080102', 10.00, 'B' UNION ALL
SELECT 3, '20080102', 10.01, 'Constant' UNION ALL
SELECT 3, '20080102', 10.02, 'Deep Cheap' UNION ALL

SELECT 4, '20080102', 1.05, 'GoldDiggers' UNION ALL
SELECT 4, '20080102', 1.02, 'H' UNION ALL
SELECT 4, '20080102', 1.02, 'Indigo' UNION ALL
SELECT 4, '20080102', 1.05, 'Jokers' UNION ALL
SELECT 4, '20080102', 1.42, 'K'

-Sometimes the answer to your question is the hack that works

 

[jdaman]

Yes, you are correct. It is dependent on a static set of values in the source column otherwise it will fail. I keep thinking there must be a more elegant way to provide the data that doesnt involve looping or throwing my suggestion into a dynamic script (which at that point would be even more confusing and have other problems associated with using dynamic sql) but havent thought of one.

btw, I dont mind anyone ragging on my code... always good to have constructive criticism.

Thanks, Qik3Coder.