Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: How to Query this...

    So I have this table (pls see attachment). First column is the number of unit I need. WK1-WK5 is when inventory will come in.

    I need it so that my desired outcome will look like this:

    | 19 | 5 | 0 | 10 | 4 | 0|
    | 4 | 4 | 0 | 0 | 0 | 0 |

    where
    row1 WK1+WK2+Wk3+WK4+WK5 = 19
    row1 WK1+WK2+Wk3+WK4+WK5 = 4

    So if I have inventory coming in on that week, it would match the maximum value I can take away from units needed (first column) until I get all units spread across the weeks.

    Any ideas how to handle this? I started off with the query below but the second case gives an error since W1 is in the query.

    select CASE
    WHEN WK1<>0 AND WK1 < BackOrder THEN WK1
    WHEN WK1<>0 AND WK1 > BackOrder THEN BackOrder
    ELSE 0
    END AS W1,
    CASE
    WHEN WK2<>0 AND WK2 < BackOrder-W1 THEN WK2
    WHEN WK2<>0 AND WK2 > BackOrder-W1 THEN BackOrder-W1
    ELSE 0
    END AS W2
    from table

    Thanks
    Attached Thumbnails Attached Thumbnails SQL.PNG  

  2. #2
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    I basically did it like below using temp tables. Any ideas how to handle it differently? Thanks


    DROP TABLE #TEMP1
    select BackOrder,
    CASE
    WHEN WK1<>0 AND WK1 <= BackOrder THEN WK1
    WHEN WK1<>0 AND WK1 > BackOrder THEN BackOrder
    ELSE 0
    END AS W1,
    WK2,WK3,WK4,WK5
    into #TEMP1
    from TEMP



    DROP TABLE #TEMP2
    select BackOrder,W1,
    CASE
    WHEN WK2<>0 AND BackOrder=(W1) THEN 0
    WHEN WK2<>0 AND WK2 < BackOrder-W1 THEN WK2
    WHEN WK2<>0 AND WK2 > BackOrder-W1 THEN BackOrder-W1
    ELSE 0
    END AS W2,
    WK3,WK4,WK5
    into #TEMP2
    from #TEMP1


    DROP TABLE #TEMP3
    select BackOrder,W1,w2,
    CASE
    WHEN WK3<>0 AND BackOrder=(W1+W2) THEN 0
    WHEN WK3<>0 AND WK3 < BackOrder-(W1+W2) THEN WK3
    WHEN WK3<>0 AND WK3 > BackOrder-(W1+W2) THEN BackOrder-(W1+W2)
    ELSE 0
    END AS W3,
    WK4,WK5
    INTO #TEMP3
    from #TEMP2


    DROP TABLE #TEMP4
    select BackOrder,W1,w2,W3,
    CASE
    WHEN WK4<>0 AND BackOrder=(W1+W2+w3) THEN 0
    WHEN WK4<>0 AND WK4 < BackOrder-(W1+W2+w3) THEN WK4
    WHEN WK4<>0 AND WK4 > BackOrder-(W1+W2+w3) THEN BackOrder-(W1+W2+w3)
    ELSE 0
    END AS W4,
    WK5
    INTO #TEMP4
    from #TEMP3


    DROP TABLE #TEMP5
    SELECT * FROM #TEMP4
    select BackOrder,W1,w2,W3,W4,
    CASE
    WHEN WK5<>0 AND BackOrder=(W1+W2+w3+W4) THEN 0
    WHEN WK5<>0 AND WK5 < BackOrder-(W1+W2+w3+W4) THEN WK5
    WHEN WK5<>0 AND WK5 > BackOrder-(W1+W2+w3+W4) THEN BackOrder-(W1+W2+w3+W4)
    ELSE 0
    END AS W5
    INTO #TEMP5
    from #TEMP4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •