Bit of T_SQL help.

(3 posts) (2 voices)
  1. ajdjackson, Member

    Hi

    Looking a wee bit of help if possible please.

    I'm trying to price products based on their bill of materials.

    I've managed to explode the BOM ok but I'm stuck as to how to roll-up Component prices through the BOM levels.

    This is where I've got to:


    with cte_lcur as
    (select a.Currency as cur,
    a.Rate as fx
    from [dbo].[tblcurrency] a
    join
    (select Currency as Cur,
    max(CDate) as CDate
    from [dbo].[tblcurrency]
    group by Currency) b on a.Currency=b.Cur and a.CDate=b.CDate
    ),
    cte_lpo as
    (select a.[ItemCode] as ItemCode,
    c.Currency as Cur,
    a.PONumber as PONum,
    a.Price as SuppPrice,
    e.fx as FX,
    cast(a.Price/iif(d.PCurrency='GBP',1,e.fx) as decimal(15,5)) as Price,
    row_number() over (partition by a.ItemCode order by a.[PONumber] desc) as rnum
    from [Company001].[dbo].[PODetails] a
    join [Company001].[dbo].[POHeader] b on a.[PONumber]=b.[PONumber]
    join [Company001].[dbo].[Supplier] c on b.Supplier=c.SageCode
    join [dbo].[tblcurxref] d on c.[Currency]=d.[PCurrency]
    left join cte_lcur e on d.Currency=e.Cur
    where a.[ItemCode] is not null
    ),
    BOMCost
    AS
    (SELECT a.Parent_Code as SKU,
    a.Parent_Code as ItemCode,
    a.Component_Code as ComponentCode,
    a.[QuantityPer] as QtyPer,
    c.Price as Price,
    0 as BOMLevel
    FROM [Company001].[dbo].[BoM_Detail] AS a
    left join cte_lpo c on a.Component_Code=c.ItemCode and c.rnum=1
    WHERE a.parent_code = '662056' and a.[Component_flag]='P' and a.[Component_Code]<>'LABOUR'
    UNION ALL
    SELECT
    b.SKU,
    a.Parent_Code,
    a.Component_Code,
    a.[QuantityPer],
    c.Price as Price,
    (BOMLevel + 1) as BOMLevel
    FROM [Company001].[dbo].[BoM_Detail] AS a
    join BOMCost b on a.Parent_Code=b.ComponentCode
    join cte_lpo c on a.Component_Code=c.ItemCode and c.rnum=1
    where a.[Component_flag]='P' and a.[Component_Code]<>'LABOUR'
    )
    SELECT SKU, ItemCode,ComponentCode, QtyPer, Price,BOMLevel
    FROM BOMCost
    order by 1,6,2

    The first 2 CTEs grab the last purchase order prices and converts to GBP.

    The output from the above is:

    In the above example the product has 3 BOM levels.

    I need to roll cost of ItemCode 020001 from BOM Level 2 into ComponentCode 020001 in BOM Level 1, calculate the total cost of BOM Level 1 and then roll that up to BOM Level 0 for ComponentCode 040006. Hope you follow that. The end result should look like this.

    Any help most appreciated.

    Thanks

    Jake

  2. ajdjackson, Member

    Hi

    I've been playing around with the above and I think I have solution that works.

    The only thing I'm not sure of is whether it's efficient.

    What do you think?

    Thanks

    Jake

    with cte_lcur as (select a.Currency as cur, a.Rate as fx from [dbo].[tblcurrency] a join (select Currency as Cur, max(CDate) as CDate from [dbo].[tblcurrency] group by Currency) b on a.Currency=b.Cur and a.CDate=b.CDate ), cte_lpo as (select a.[ItemCode] as ItemCode, c.Currency as Cur, a.PONumber as PONum, a.Price as SuppPrice, e.fx as FX, cast(a.Price/iif(d.PCurrency='GBP',1,e.fx) as decimal(15,5)) as Price, row_number() over (partition by a.ItemCode order by a.[PONumber] desc) as rnum from [Company001].[dbo].[PODetails] a join [Company001].[dbo].[POHeader] b on a.[PONumber]=b.[PONumber] join [Company001].[dbo].[Supplier] c on b.Supplier=c.SageCode join [dbo].[tblcurxref] d on c.[Currency]=d.[PCurrency] left join cte_lcur e on d.Currency=e.Cur where a.[ItemCode] is not null ), BOMCost AS (SELECT a.Parent_Code as SKU, a.Parent_Code as ItemCode, a.Component_Code as ComponentCode, a.[QuantityPer] as QtyPer, c.Price as Price, 0 as BOMLevel FROM [Company001].[dbo].[BoM_Detail] AS a left join cte_lpo c on a.Component_Code=c.ItemCode and c.rnum=1 WHERE a.parent_code = '662056' and a.[Component_flag]='P' and a.[Component_Code]<>'LABOUR' UNION ALL SELECT b.SKU, a.Parent_Code, a.Component_Code, a.[QuantityPer], c.Price as Price, (BOMLevel + 1) as BOMLevel FROM [Company001].[dbo].[BoM_Detail] AS a join BOMCost b on a.Parent_Code=b.ComponentCode join cte_lpo c on a.Component_Code=c.ItemCode and c.rnum=1 where a.[Component_flag]='P' and a.[Component_Code]<>'LABOUR' ), cte_sumbom as (select BOMLevel as blevel, ItemCode as icode, sum(qtyPer*isnull(Price,0)) as iprice from BOMCost group by BOMLevel,ItemCode) SELECT x.SKU, x.ItemCode,x.ComponentCode, x.QtyPer, x.Price,y.iprice,x.BOMLevel FROM BOMCost x left join cte_sumbom y on x.ComponentCode=y.icode and x.BOMLevel<y.blevel where x.BOMLevel=0 order by 7,2,3;

  3. myDBR Team, Key Master

    The efficiently really depends on the amount of data and indexing. Use the tools from SQL Server (showplan and statistics io) to see how the query is being executed.

    --
    myDBR Team


Reply

You must log in to post.