Tricky requirement please help me to build this sql

  • Dell - Internal Use - Confidential

    CREATE TABLE #Test

    (

    ORD_NBR VARCHAR(100)

    ,CALENDER_DATE DATETIME

    ,FISICAL_QUARTER VARCHAR(100)

    ,[PROD_OFFRG_DESC] VARCHAR(100)

    ,[PAYOUT] MONEY

    )

    INSERT INTO #Test

    SELECT '101', '2015-02-01 00:00:00.000','2016-Q1','ENTERPRISE - RACKS','300'

    UNION ALL

    SELECT '102','2015-02-02 00:00:00.000','2016-Q1','ENTERPRISE - RACKS','300'

    UNION ALL

    SELECT '103','2015-02-02 00:00:00.000','2016-Q1','ENTERPRISE - RACKS','300'

    UNION ALL

    SELECT '104','2015-02-02 00:00:00.000','2016-Q1','ENTERPRISE - RACKS','400'

    UNION ALL

    SELECT '105','2015-02-02 00:00:00.000','2016-Q1','ENTERPRISE - RACKS','400'

    UNION ALL

    SELECT '106', '2015-02-03 00:00:00.000','2016-Q1','ENTERPRISE - KVM/KMM','300'

    UNION ALL

    SELECT '107','2015-02-03 00:00:00.000','2016-Q1','ENTERPRISE - KVM/KMM','300'

    UNION ALL

    SELECT '108','2015-02-03 00:00:00.000','2016-Q1','ENTERPRISE - KVM/KMM','300'

    UNION ALL

    SELECT '109','2015-02-03 00:00:00.000','2016-Q1','ENTERPRISE - KVM/KMM','400'

    UNION ALL

    SELECT '110','2015-02-03 00:00:00.000','2016-Q1','ENTERPRISE - KVM/KMM','400'

    UNION ALL

    SELECT '111', '2015-02-05 00:00:00.000','2016-Q1','ENTERPRISE - UPS','300'

    UNION ALL

    SELECT '112','2015-02-06 00:00:00.000','2016-Q1','ENTERPRISE - UPS','300'

    UNION ALL

    SELECT '113','2015-02-07 00:00:00.000','2016-Q1','ENTERPRISE - UPS','300'

    UNION ALL

    SELECT '114','2015-02-08 00:00:00.000','2016-Q1','ENTERPRISE - UPS','400'

    UNION ALL

    SELECT '115','2015-02-09 00:00:00.000','2016-Q1','ENTERPRISE - UPS','400'

    UNION ALL

    SELECT '116','2015-02-09 00:00:00.000','2016-Q1','ENTERPRISE - UPS','400'

    I have kind of tricky requirement.

    A.We have Total Budget cap for ‘2016-Q1' is 3600. If the Budget cap goes beyond 3600 we are not going make payment. In the above example Total Payout is 5100.

    B.We are having Budget Cap for [PROD_OFFRG_DESC] for ENTERPRISE - RACKS'->1200, ENTERPRISE - KVM/KMM'->1200, ENTERPRISE - UPS'->1200, But if you check total sum based on each

    [PROD_OFFRG_DESC] it crosses 1200. for ENTERPRISE - RACKS'->1700, ENTERPRISE - KVM/KMM'->1700, ENTERPRISE - UPS'->1700

    c. Even though Order_Nbr 104(ENTERPRISE - RACKS') crosses 1200 we are going to pay or we are going to consider this order

    same with Order Nbr 109(ENTERPRISE - KVM/KMM') and ord_nbr 114(

    ENTERPRISE - UPS')

    C.In all the three Product_Offrg_Group we are going to neglect 105,110,115

    All the calculations are done based on the calendar_date column

    End result I want as below

    ORD_NBRCALENDER_DATEFISICAL_QUARTERPROD_OFFRG_DESCPAYOUT

    1012015-02-01 00:00:00.0002016-Q1ENTERPRISE - RACKS300.00

    1022015-02-02 00:00:00.0002016-Q1ENTERPRISE - RACKS300.00

    1032015-02-02 00:00:00.0002016-Q1ENTERPRISE - RACKS300.00

    1042015-02-02 00:00:00.0002016-Q1ENTERPRISE - RACKS400.00

    1062015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM300.00

    1072015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM300.00

    1082015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM300.00

    1092015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM400.00

    1112015-02-05 00:00:00.0002016-Q1ENTERPRISE - UPS300.00

    1122015-02-06 00:00:00.0002016-Q1ENTERPRISE - UPS300.00

    1132015-02-07 00:00:00.0002016-Q1ENTERPRISE - UPS300.00

    1142015-02-08 00:00:00.0002016-Q1ENTERPRISE - UPS400.00

  • Smash125 (5/13/2015)


    Dell - Internal Use - Confidential

    I have kind of tricky requirement.

    A.We have Total Budget cap for ‘2016-Q1' is 3600. If the Budget cap goes beyond 3600 we are not going make payment. In the above example Total Payout is 5100.

    However, in your sample output the total is 3900! Is your sample output incorrect or is something else going on?

    B.We are having Budget Cap for [PROD_OFFRG_DESC] for ENTERPRISE - RACKS'->1200, ENTERPRISE - KVM/KMM'->1200, ENTERPRISE - UPS'->1200, But if you check total sum based on each

    [PROD_OFFRG_DESC] it crosses 1200. for ENTERPRISE - RACKS'->1700, ENTERPRISE - KVM/KMM'->1700, ENTERPRISE - UPS'->1700

    OK, but what does that mean? In your sample output, you still have 1300 for ENTERPRISE - UPS

    c. Even though Order_Nbr 104(ENTERPRISE - RACKS') crosses 1200 we are going to pay or we are going to consider this order

    same with Order Nbr 109(ENTERPRISE - KVM/KMM') and ord_nbr 114(

    ENTERPRISE - UPS')

    not sure what you mean here

    C.In all the three Product_Offrg_Group we are going to neglect 105,110,115

    Do you mean orders 105, 110, and 115? If not, where do we see those numbers in the data?

    All the calculations are done based on the calendar_date column

    End result I want as below

    ORD_NBRCALENDER_DATEFISICAL_QUARTERPROD_OFFRG_DESCPAYOUT

    1012015-02-01 00:00:00.0002016-Q1ENTERPRISE - RACKS300.00

    1022015-02-02 00:00:00.0002016-Q1ENTERPRISE - RACKS300.00

    1032015-02-02 00:00:00.0002016-Q1ENTERPRISE - RACKS300.00

    1042015-02-02 00:00:00.0002016-Q1ENTERPRISE - RACKS400.00

    1062015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM300.00

    1072015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM300.00

    1082015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM300.00

    1092015-02-03 00:00:00.0002016-Q1ENTERPRISE - KVM/KMM400.00

    1112015-02-05 00:00:00.0002016-Q1ENTERPRISE - UPS300.00

    1122015-02-06 00:00:00.0002016-Q1ENTERPRISE - UPS300.00

    1132015-02-07 00:00:00.0002016-Q1ENTERPRISE - UPS300.00

    1142015-02-08 00:00:00.0002016-Q1ENTERPRISE - UPS400.00

    Gerald Britton, Pluralsight courses

  • In short, there exists a table say #limits(FISICAL_QUARTER ,[PROD_OFFRG_DESC], max_sum).

    The query should calculate orders' running totals (excluding current order) within FISICAL_QUARTER ,[PROD_OFFRG_DESC] and select only orders, where runnig total is less then max_sum from #limits.

    Is it what you need?

  • sounds to me like you need a recursive Common Table Expression

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

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