Post reply

MOD Command to get a case split

  • March 16, 2010 at 10:59 am

    #386517

    HI I have a report which displays how many cases are in stock -

    c/s units

    33 12 in a case - which would be 396 bottles

    I have now got a situation where the cases are being split so above now shows

    c/s units

    33.5 12 in a case

    which the actual stock is 33 cases and 3 bottles as 1 case has had 9 bottle sold leaving 3

    Does anyone know how to display this correctly as below using an expression

    33 c/s and 3 btls ?

  • March 16, 2010 at 11:15 am

    #1134659

    ian your expanation doesn't make sense. half a case would be 6 bottles in a 12 unit case, right? where did "the actual stock is 33 cases and 3 bottles as 1 case has had 9 bottle sold leaving 3" come from?

    also, 33.5: is someone saying that it's 33 cases BUT one of the cases is half full? (really 32.5?)or is it really 33.5 cases (33.5 * 12 = 402 units)

    did you mean to put the value 33.25 instead?

    anyway, here's a WAG for you to digest:I'm assuming that the notation is "33 cases but one of the cases is half full", as that seems closes to the desired value:

    --results:

    units_from_whole_cases units_from_partial_cases

    384 6.000

    the code i used:

    create table inventory(cs decimal(9,2),units int)

    insert into inventory

    select 33.5,12

    select (floor(cs) - CASE

    WHEN CEILING(cs) = floor(cs)

    THEN 0

    ELSE 1

    END) * units as units_from_whole_cases ,

    ((cs * 1.0) - floor(cs)) * units as units_from_partial_cases

    from inventory

    ian.dunlop 7059 (3/16/2010)

    HI I have a report which displays how many cases are in stock -

    c/s units

    33 12 in a case - which would be 396 bottles

    I have now got a situation where the cases are being split so above now shows

    c/s units

    33.5 12 in a case

    which the actual stock is 33 cases and 3 bottles as 1 case has had 9 bottle sold leaving 3

    Does anyone know how to display this correctly as below using an expression

    33 c/s and 3 btls ?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 17, 2010 at 6:47 am

    #1135175

    Thanks below did the trick

Viewing 3 posts - 1 through 2 (of 2 total)

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