Post reply

Create one record for each in quotient

  • January 6, 2010 at 8:21 pm

    #156557

    I have a problem I am not sure I remember how to solve, I think I have done something like this in the past using the partition function but it has been a while.

    Here is what I need to do:

    I have this dataset:

    sku | qty | pkg_qty

    ------------------

    123 | 100| 25

    321 | 200| 100

    What I need to do is use qty as the dividend and pkg_qty as the divisor and have one record for each in the quotient.

    The result should be something like:

    rec |sku |qty | pkg_qty

    ------------------

    1 |123 | 100| 25

    2 |123 | 100| 25

    3 |123 | 100| 25

    4 |123 | 100| 25

    1 |321 | 200| 100

    2 |321 | 200| 100

    I hope that made sense, any help would be greatly appreciated!

  • January 6, 2010 at 8:45 pm

    #1099997

    create table #temp

    (

    SKU int,

    Qty int,

    PKQTY int

    )

    insert into #temp

    select 123,100,25

    union all

    select 321,200,100

    union all

    select 555,500,100

    SELECT SKU,Qty,PKQTY

    FROM

    (select slno from

    (select 1 slno

    union all

    select 2

    union all

    select 3

    union all

    select 4

    union all

    select 5)as X) a /*(OR use the tally table for the high count of numbers) */

    CROSS JOIN #temp

    where slno <=(Qty/PKQTY)

  • January 6, 2010 at 11:13 pm

    #1100022

    Nice, fast, little numbers table... Nice job, Arun.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 6, 2010 at 11:25 pm

    #1100027

    Hi Jeff,

    Thanks, actually I need to put the tally table there, but most people ask the question, what the tally table is. That’s why the little numbers table.

  • January 6, 2010 at 11:40 pm

    #1100033

    I figured that's what it was. That's why I wrote the article on the Tally table... I got tired of explaining it and can just point them to the article, now.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 6, 2010 at 11:58 pm

    #1100037

    Hi Jeff,

    I aware of that article, ok, I point it out the tally table.

  • January 7, 2010 at 8:59 am

    #1100278

    Heh... might not be necessary here. You did great with the "on the fly" short list Tally. I was just telling you why I wrote the article. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 7, 2010 at 9:13 am

    #1100291

    Hi,

    Thanks so much for the help. This appears to be exactly what I need except for one issue, I need to return a record with a remainder that is less than the pkg qty. So I can print a label and have the "broken" qty print on that as well.

    Example:

    sku | Qty | Pkg Qty

    --------------------------------

    123 | 2500 | 1000

    Needs to return

    rec | sku | Qty | Pkg Qty

    --------------------------------

    1 | 123 | 1000 | 1000

    2 | 123 | 1000 | 1000

    3 | 123 | 500 | 1000

    Here is what I have so far, the result looks great other than dropping the remainder...

    DROP TABLE #temp_label

    SELECT dbo.oe_pick_ticket.order_no,

    item_id,

    line_number,

    item_desc,

    dbo.oe_pick_ticket_detail.qty_to_pick,

    item_uom.unit_size,

    CASE

    WHEN dbo.oe_pick_ticket_detail.qty_to_pick > item_uom.unit_size

    THEN dbo.oe_pick_ticket_detail.qty_to_pick / Coalesce(item_uom.unit_size,'1')

    ELSE '1'

    END AS number_pkgs

    INTO #temp_label

    FROM oe_pick_ticket

    INNER JOIN oe_pick_ticket_detail

    ON oe_pick_ticket_detail.pick_ticket_no = oe_pick_ticket.pick_ticket_no

    INNER JOIN inv_mast

    ON inv_mast.inv_mast_uid = oe_pick_ticket_detail.inv_mast_uid

    LEFT JOIN item_uom

    ON item_uom.inv_mast_uid = inv_mast.inv_mast_uid

    AND item_uom.unit_of_measure = 'PK'

    AND item_uom.delete_flag <> 'Y'

    WHERE oe_pick_ticket.pick_ticket_no = '#####'

    ORDER BY line_number

    SELECT *

    FROM #temp_label

    CROSS JOIN XXXwarehouse.dbo.XXXtally

    WHERE number_pkgs >= XXXtally.n

    Thanks !

  • January 7, 2010 at 9:17 am

    #1100298

    arun.sas (1/6/2010)

    SELECT SKU,Qty,PKQTY

    FROM

    (select slno from

    (select 1 slno

    union all

    select 2

    union all

    select 3

    union all

    select 4

    union all

    select 5)as X) a

    You don't need the "select slno from" part.

    SELECT SKU,Qty,PKQTY

    FROM

    (select 1 slno

    union all

    select 2

    union all

    select 3

    union all

    select 4

    union all

    select 5)as X

    works the same but looks a little "cleaner".


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 7, 2010 at 10:06 am

    #1100340

    This seems to do the trick, can anyone think of a better way to do this? It seems to work fine and I will not be dealing with huge record sets.

    DROP TABLE #temp_label

    SELECT dbo.oe_pick_ticket.order_no,

    item_id,

    line_number,

    item_desc,

    dbo.oe_pick_ticket_detail.qty_to_pick,

    item_uom.unit_size,

    CASE

    WHEN dbo.oe_pick_ticket_detail.qty_to_pick > item_uom.unit_size

    THEN dbo.oe_pick_ticket_detail.qty_to_pick / Coalesce(item_uom.unit_size,'1')

    ELSE '1'

    END AS number_pkgs

    INTO #temp_label

    FROM oe_pick_ticket

    INNER JOIN oe_pick_ticket_detail

    ON oe_pick_ticket_detail.pick_ticket_no = oe_pick_ticket.pick_ticket_no

    INNER JOIN inv_mast

    ON inv_mast.inv_mast_uid = oe_pick_ticket_detail.inv_mast_uid

    LEFT JOIN item_uom

    ON item_uom.inv_mast_uid = inv_mast.inv_mast_uid

    AND item_uom.unit_of_measure = 'PK'

    AND item_uom.delete_flag <> 'Y'

    WHERE oe_pick_ticket.pick_ticket_no = '3181111'

    ORDER BY line_number

    SELECT *,

    CASE

    WHEN

    CAST(#temp_label.qty_to_pick AS INT) - (CAST(#temp_label.unit_size AS FLOAT) * CAST(tally.N AS INT) ) < 0

    THEN #temp_label.unit_size + CAST(#temp_label.qty_to_pick AS INT) - (CAST(#temp_label.unit_size AS FLOAT) * CAST(tally.N AS INT) )

    ELSE

    #temp_label.unit_size END AS pkg_qty

    FROM #temp_label

    CROSS JOIN warehouse.dbo.tally tally

    WHERE ceiling(number_pkgs) >= tally.n

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply