May 13, 2015 at 6:26 am
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
May 13, 2015 at 7:52 am
Smash125 (5/13/2015)
Dell - Internal Use - ConfidentialI 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
May 13, 2015 at 8:18 am
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?
May 14, 2015 at 4:09 am
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