I have a BAQ that has worked fantastic for forecasting/planning weekly Demand and necessary into 9 different bucket based on 9 different buckets based on order week required by customer. Bucket for past due, current week, 1 week, 2 week,etc. through week 7, plus past due and TOTAL. With week 6 crossing a yearly boundary this year it is NOT calculating this week correctly.
I keep getting pulled off for other crisis’s so I have to start ALL over understanding what I am missing. Any help would be appreciated.
Here is my code:
select
[S080_GetInventoryS2].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S080_GetInventoryS2].[Part_PartDescription] as [Part_PartDescription],
[S080_GetInventoryS2].[Part_TypeCode] as [Part_TypeCode],
[S080_GetInventoryS2].[Calculated_AvgSalesDollarsPerPart] as [Calculated_AvgSalesDollarsPerPart],
(case
when S080_GetInventoryS2.Calculated_TotalOnHand > 0
then S080_GetInventoryS2.Calculated_TotalOnHand
else 0
end) as [Calculated_BegInv],
(case
when S080_GetInventoryS2.Calculated_PastDue > 0
then S080_GetInventoryS2.Calculated_PastDue
else 0
end) as [Calculated_PD_Demand],
(PD_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_PD_DemandDlrs],
(case
when PD_Demand > BegInv
then PD_Demand - BegInv
else 0
end) as [Calculated_PD_Need],
(PD_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_PD_BuildDlrs],
(case
when PD_Demand > BegInv
then 0
else BegInv - PD_Demand
end) as [Calculated_PD_EndInv],
(case
when S080_GetInventoryS2.Calculated_CurrWk > 0
then S080_GetInventoryS2.Calculated_CurrWk
else 0
end) as [Calculated_Curr_Demand],
(Curr_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_Curr_DemandDlrs],
(case
when Curr_Demand > PD_EndInv
then Curr_Demand - PD_EndInv
else 0
end) as [Calculated_Curr_Need],
(Curr_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_Curr_BuildDlrs],
(case
when Curr_Demand > PD_EndInv
then 0
else PD_EndInv - Curr_Demand
end) as [Calculated_Cur_EndInv],
(case
when S080_GetInventoryS2.Calculated_OneWkOut > 0
then S080_GetInventoryS2.Calculated_OneWkOut
else 0
end) as [Calculated_OneWkOut_Demand],
(OneWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_OneWkOut_DemandDlrs],
(case
when OneWkOut_Demand > Cur_EndInv
then OneWkOut_Demand - Cur_EndInv
else 0
end) as [Calculated_OneWkOut_Need],
(OneWkOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_OneWkOut_BuildDlrs],
(case
when OneWkOut_Demand > Cur_EndInv
then 0
else Cur_EndInv - OneWkOut_Demand
end) as [Calculated_OneWkOut_EndInv],
(case
when S080_GetInventoryS2.Calculated_TwoWkOut > 0
then S080_GetInventoryS2.Calculated_TwoWkOut
else 0
end) as [Calculated_TwoWkOut_Demand],
(TwoWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_TwoWkOut_DemandDlrs],
(case
when TwoWkOut_Demand > OneWkOut_EndInv
then TwoWkOut_Demand - OneWkOut_EndInv
else 0
end) as [Calculated_TwoWkOut_Need],
(TwoWkOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_TwoWkOut_BuildDlrs],
(case
when TwoWkOut_Demand > OneWkOut_EndInv
then 0
else OneWkOut_EndInv - TwoWkOut_Demand
end) as [Calculated_TwoWkOut_EndInv],
(case
when S080_GetInventoryS2.Calculated_ThreeWkOut > 0
then S080_GetInventoryS2.Calculated_ThreeWkOut
else 0
end) as [Calculated_ThreeWkOut_Demand],
(ThreeWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_ThreeWkOut_DemandDlrs],
(case
when ThreeWkOut_Demand > TwoWkOut_EndInv
then ThreeWkOut_Demand - TwoWkOut_EndInv
else 0
end) as [Calculated_ThreeWkOut_Need],
(ThreeWkOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_ThreeWkOut_BuildDlrs],
(case
when ThreeWkOut_Demand > TwoWkOut_EndInv
then 0
else TwoWkOut_EndInv - ThreeWkOut_Demand
end) as [Calculated_ThreeWkOut_EndInv],
(case
when S080_GetInventoryS2.Calculated_FourWkOut > 0
then S080_GetInventoryS2.Calculated_FourWkOut
else 0
end) as [Calculated_FourWkOut_Demand],
(FourWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_FourWkOut_DemandDlrs],
(case
when FourWkOut_Demand > ThreeWkOut_EndInv
then FourWkOut_Demand - ThreeWkOut_EndInv
else 0
end) as [Calculated_FourWkOut_Need],
(FourWkOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_FourWkOut_BuildDlrs],
(case
when FourWkOut_Demand > ThreeWkOut_EndInv
then 0
else ThreeWkOut_EndInv - FourWkOut_Demand
end) as [Calculated_FourWkOut_EndInv],
(case
when S080_GetInventoryS2.Calculated_FiveWkOut > 0
then S080_GetInventoryS2.Calculated_FiveWkOut
else 0
end) as [Calculated_FiveWkOut_Demand],
(FiveWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_FiveWkOut_DemandDlrs],
(case
when FiveWkOut_Demand > FourWkOut_EndInv
then FiveWkOut_Demand - FourWkOut_EndInv
else 0
end) as [Calculated_FiveWeekOut_Need],
(FiveWeekOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_FiveWkOut_BuildDlrs],
(case
when FiveWkOut_Demand > FourWkOut_EndInv
then 0
else FourWkOut_EndInv - FiveWkOut_Demand
end) as [Calculated_FiveWkOut_EndInv],
(case
when S080_GetInventoryS2.Calculated_SixWkOut > 0
then S080_GetInventoryS2.Calculated_SixWkOut
else 0
end) as [Calculated_SixWkOut_Demand],
(SixWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_SixWkOut_DemandDlrs],
(case
when SixWkOut_Demand > FiveWkOut_EndInv
then SixWkOut_Demand - FiveWkOut_EndInv
else 0
end) as [Calculated_SixWkOut_Need],
(SixWkOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_SixWkOut_BuildDlrs],
(case
when SixWkOut_Demand > FiveWkOut_EndInv
then 0
else FiveWkOut_EndInv - SixWkOut_Demand
end) as [Calculated_SixWkOut_EndInv],
(case
when S080_GetInventoryS2.Calculated_SevenWkOut > 0
then S080_GetInventoryS2.Calculated_SevenWkOut
else 0
end) as [Calculated_SevWkOut_Demand],
(SevWkOut_Demand * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_SevWkOut_DemandDlrs],
(case
when SevWkOut_Demand > SixWkOut_EndInv
then SevWkOut_Demand - SixWkOut_EndInv
else 0
end) as [Calculated_SevWkOut_Need],
(SevWkOut_Need * S080_GetInventoryS2.Calculated_AvgSalesDollarsPerPart) as [Calculated_SevWkOut_BuildDlrs],
(case
when SevWkOut_Demand > SixWkOut_EndInv
then 0
else SixWkOut_EndInv - SevWkOut_Demand
end) as [Calculated_SevWkOut_EndInv]
from (select
[S060_MergePivotWithDollars].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S060_MergePivotWithDollars].[Part_PartDescription] as [Part_PartDescription],
[S060_MergePivotWithDollars].[Part_TypeCode] as [Part_TypeCode],
[S060_MergePivotWithDollars].[Calculated_AvgSalesDollarsPerPart] as [Calculated_AvgSalesDollarsPerPart],
[S070_GetInventoryStep1].[Calculated_TotalOnHand] as [Calculated_TotalOnHand],
[S060_MergePivotWithDollars].[Calculated_PastDue] as [Calculated_PastDue],
[S060_MergePivotWithDollars].[Calculated_CurrWk] as [Calculated_CurrWk],
[S060_MergePivotWithDollars].[Calculated_OneWkOut] as [Calculated_OneWkOut],
[S060_MergePivotWithDollars].[Calculated_TwoWkOut] as [Calculated_TwoWkOut],
[S060_MergePivotWithDollars].[Calculated_ThreeWkOut] as [Calculated_ThreeWkOut],
[S060_MergePivotWithDollars].[Calculated_FourWkOut] as [Calculated_FourWkOut],
[S060_MergePivotWithDollars].[Calculated_FiveWkOut] as [Calculated_FiveWkOut],
[S060_MergePivotWithDollars].[Calculated_SixWkOut] as [Calculated_SixWkOut],
[S060_MergePivotWithDollars].[Calculated_SevenWkOut] as [Calculated_SevenWkOut],
[S060_MergePivotWithDollars].[Calculated_EightWkOut] as [Calculated_EightWkOut]
from (select
[S040_SumPivot].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S040_SumPivot].[Part_PartDescription] as [Part_PartDescription],
[S040_SumPivot].[Part_TypeCode] as [Part_TypeCode],
[S050_SummarizeSalesDollars].[Calculated_AvgSalesDollarsPerPart] as [Calculated_AvgSalesDollarsPerPart],
[S040_SumPivot].[Calculated_PastDue] as [Calculated_PastDue],
[S040_SumPivot].[Calculated_CurrWk] as [Calculated_CurrWk],
[S040_SumPivot].[Calculated_OneWkOut] as [Calculated_OneWkOut],
[S040_SumPivot].[Calculated_TwoWkOut] as [Calculated_TwoWkOut],
[S040_SumPivot].[Calculated_ThreeWkOut] as [Calculated_ThreeWkOut],
[S040_SumPivot].[Calculated_FourWkOut] as [Calculated_FourWkOut],
[S040_SumPivot].[Calculated_FiveWkOut] as [Calculated_FiveWkOut],
[S040_SumPivot].[Calculated_SixWkOut] as [Calculated_SixWkOut],
[S040_SumPivot].[Calculated_SevenWkOut] as [Calculated_SevenWkOut],
[S040_SumPivot].[Calculated_EightWkOut] as [Calculated_EightWkOut]
from (select
[S030_Pivot].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S030_Pivot].[Part_PartDescription] as [Part_PartDescription],
[S030_Pivot].[Part_TypeCode] as [Part_TypeCode],
(sum(S030_Pivot.S026_SecondBucket_0)) as [Calculated_PastDue],
(Sum(S030_Pivot.S026_SecondBucket_1)) as [Calculated_CurrWk],
(Sum(S030_Pivot.S026_SecondBucket_2)) as [Calculated_OneWkOut],
(Sum(S030_Pivot.S026_SecondBucket_3)) as [Calculated_TwoWkOut],
(Sum(S030_Pivot.S026_SecondBucket_4)) as [Calculated_ThreeWkOut],
(Sum(S030_Pivot.S026_SecondBucket_5)) as [Calculated_FourWkOut],
(Sum(S030_Pivot.S026_SecondBucket_6)) as [Calculated_FiveWkOut],
(sum(S030_Pivot.S026_SecondBucket_7)) as [Calculated_SixWkOut],
(Sum(S030_Pivot.S026_SecondBucket_8)) as [Calculated_SevenWkOut],
(Sum(S030_Pivot.S026_SecondBucket_9)) as [Calculated_EightWkOut]
from (select
[S026_SecondBucket].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S026_SecondBucket].[Part_PartDescription] as [Part_PartDescription],
[S026_SecondBucket].[Part_TypeCode] as [Part_TypeCode],
[S026_SecondBucket].[0] as [S026_SecondBucket_0],
[S026_SecondBucket].[1] as [S026_SecondBucket_1],
[S026_SecondBucket].[2] as [S026_SecondBucket_2],
[S026_SecondBucket].[3] as [S026_SecondBucket_3],
[S026_SecondBucket].[4] as [S026_SecondBucket_4],
[S026_SecondBucket].[5] as [S026_SecondBucket_5],
[S026_SecondBucket].[6] as [S026_SecondBucket_6],
[S026_SecondBucket].[7] as [S026_SecondBucket_7],
[S026_SecondBucket].[8] as [S026_SecondBucket_8],
[S026_SecondBucket].[9] as [S026_SecondBucket_9]
from (select
[S025_Filter_and_Bucket1].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S025_Filter_and_Bucket1].[Part_PartDescription] as [Part_PartDescription],
[S025_Filter_and_Bucket1].[Calculated_Shpwk] as [Calculated_Shpwk],
[S025_Filter_and_Bucket1].[Calculated_TotQty] as [Calculated_TotQty],
[S025_Filter_and_Bucket1].[Calculated_TotSalesDlrs] as [Calculated_TotSalesDlrs],
[S025_Filter_and_Bucket1].[Calculated_CurrWk] as [Calculated_CurrWk],
[S025_Filter_and_Bucket1].[Part_TypeCode] as [Part_TypeCode],
((case
when S025_Filter_and_Bucket1.Calculated_Bucket = 0 then
case
when S025_Filter_and_Bucket1.Calculated_Shpwk < 20 then
1 + S025_Filter_and_Bucket1.Calculated_Shpwk
else
S025_Filter_and_Bucket1.Calculated_Bucket
end
else
S025_Filter_and_Bucket1.Calculated_Bucket
end)) as [Calculated_Calculated_Bucket]
from (select
[S020_Sum_By_Part_By_Week2].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[S020_Sum_By_Part_By_Week2].[Part_PartDescription] as [Part_PartDescription],
[S020_Sum_By_Part_By_Week2].[Calculated_Shpwk] as [Calculated_Shpwk],
[S020_Sum_By_Part_By_Week2].[Calculated_TotQty] as [Calculated_TotQty],
[S020_Sum_By_Part_By_Week2].[Calculated_TotSalesDlrs] as [Calculated_TotSalesDlrs],
[S020_Sum_By_Part_By_Week2].[Calculated_CurrWk] as [Calculated_CurrWk],
(Case
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk <= 43 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk < S020_Sum_By_Part_By_Week2.Calculated_CurrWk Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = S020_Sum_By_Part_By_Week2.Calculated_CurrWk then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 1) then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 2) then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 3) then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 4) then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 5) then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 6) then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 7) then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = (S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 8) then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 44 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 32 and 43 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 44 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 45 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 46 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 47 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 48 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 49 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 45 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 33 and 44 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 45 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 46 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 47 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 48 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 49 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 46 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 34 and 45 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 46 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 47 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 48 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 49 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 47 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 35 and 46 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 47 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 48 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 49 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 3 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 48 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 36 and 47 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 48 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 49 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 3 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 4 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 49 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 37 and 48 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 49 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 3 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 4 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 5 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 50 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 38 and 49 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 50 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 3 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 4 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 5 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 6 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 51 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 39 and 50 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 51 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 3 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 4 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 5 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 6 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 7 then 9
else
10
end
When S020_Sum_By_Part_By_Week2.Calculated_CurrWk = 52 then
case
When S020_Sum_By_Part_By_Week2.Calculated_Shpwk Between 40 and 51 Then 0
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 52 then 1
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 1 then 2
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 2 then 3
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 3 then 4
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 4 then 5
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 5 then 6
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 6 then 7
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 7 then 8
when S020_Sum_By_Part_By_Week2.Calculated_Shpwk = 8 then 9
else
10
end
end) as [Calculated_Bucket],
[S020_Sum_By_Part_By_Week2].[Part_TypeCode] as [Part_TypeCode]
from (select
[S010_Ext_Open_OrdDtl_Data].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
(S010_Ext_Open_OrdDtl_Data.Calculated_ShipWeek) as [Calculated_Shpwk],
(Sum(OrderDtl_OrderQty)) as [Calculated_TotQty],
(Sum(S010_Ext_Open_OrdDtl_Data.Calculated_SalesDlrs)) as [Calculated_TotSalesDlrs],
(S010_Ext_Open_OrdDtl_Data.Calculated_CurCalWk) as [Calculated_CurrWk],
[Part].[TypeCode] as [Part_TypeCode]
from (select
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[DocOrdBasedPrice] as [OrderDtl_DocOrdBasedPrice],
(OrderDtl.OrderQty * OrderDtl.DocOrdBasedPrice) as [Calculated_SalesDlrs],
[OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
(DatePart(yy, OrderDtl.RequestDate)) as [Calculated_ShipYear],
(DatePart(ww,OrderDtl.RequestDate)) as [Calculated_ShipWeek],
(Concat(ShipYear, ShipWeek)) as [Calculated_YearWeek],
(DatePart(ww, Constants.Today)) as [Calculated_CurCalWk]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OpenLine = true and not OrderDtl.PartNum like 'note' and not OrderDtl.PartNum like 'Fee' and not OrderDtl.PartNum like 'Install')) as S010_Ext_Open_OrdDtl_Data
left outer join Erp.Part as Part on
S010_Ext_Open_OrdDtl_Data.OrderDtl_PartNum = Part.PartNum
group by [S010_Ext_Open_OrdDtl_Data].[OrderDtl_PartNum],
[Part].[PartDescription],
(S010_Ext_Open_OrdDtl_Data.Calculated_ShipWeek),
(S010_Ext_Open_OrdDtl_Data.Calculated_CurCalWk),
[Part].[TypeCode]) as S020_Sum_By_Part_By_Week2
where (S020_Sum_By_Part_By_Week2.Calculated_Shpwk <= S020_Sum_By_Part_By_Week2.Calculated_CurrWk + 8)) as S025_Filter_and_Bucket1) S026_SecondBucket_src
PIVOT
(
sum(Calculated_TotQty)
FOR Calculated_Calculated_Bucket in ([0],[ 1],[ 2],[ 3],[ 4],[ 5],[ 6],[ 7],[ 8],[ 9]) )
as S026_SecondBucket) as S030_Pivot
group by [S030_Pivot].[OrderDtl_PartNum],
[S030_Pivot].[Part_PartDescription],
[S030_Pivot].[Part_TypeCode]) as S040_SumPivot
left outer join (select
[S020_Sum_By_Part_By_Week1].[OrderDtl_PartNum] as [OrderDtl_PartNum],
(Sum(S020_Sum_By_Part_By_Week1.Calculated_TotQty)) as [Calculated_TotalItemPerPart],
(Case when Sum(S020_Sum_By_Part_By_Week1.Calculated_TotSalesDlrs) = 0
then Sum(S020_Sum_By_Part_By_Week1.Calculated_TotSalesDlrs)
else Sum(S020_Sum_By_Part_By_Week1.Calculated_TotSalesDlrs) / TotalItemPerPart
end) as [Calculated_AvgSalesDollarsPerPart]
from (select
[S010_Ext_Open_OrdDtl_Data].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
(S010_Ext_Open_OrdDtl_Data.Calculated_ShipWeek) as [Calculated_Shpwk],
(Sum(OrderDtl_OrderQty)) as [Calculated_TotQty],
(Sum(S010_Ext_Open_OrdDtl_Data.Calculated_SalesDlrs)) as [Calculated_TotSalesDlrs],
(S010_Ext_Open_OrdDtl_Data.Calculated_CurCalWk) as [Calculated_CurrWk],
[Part].[TypeCode] as [Part_TypeCode]
from (select
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[DocOrdBasedPrice] as [OrderDtl_DocOrdBasedPrice],
(OrderDtl.OrderQty * OrderDtl.DocOrdBasedPrice) as [Calculated_SalesDlrs],
[OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
(DatePart(yy, OrderDtl.RequestDate)) as [Calculated_ShipYear],
(DatePart(ww,OrderDtl.RequestDate)) as [Calculated_ShipWeek],
(Concat(ShipYear, ShipWeek)) as [Calculated_YearWeek],
(DatePart(ww, Constants.Today)) as [Calculated_CurCalWk]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OpenLine = true and not OrderDtl.PartNum like 'note' and not OrderDtl.PartNum like 'Fee' and not OrderDtl.PartNum like 'Install')) as S010_Ext_Open_OrdDtl_Data
left outer join Erp.Part as Part on
S010_Ext_Open_OrdDtl_Data.OrderDtl_PartNum = Part.PartNum
group by [S010_Ext_Open_OrdDtl_Data].[OrderDtl_PartNum],
[Part].[PartDescription],
(S010_Ext_Open_OrdDtl_Data.Calculated_ShipWeek),
(S010_Ext_Open_OrdDtl_Data.Calculated_CurCalWk),
[Part].[TypeCode]) as S020_Sum_By_Part_By_Week1
group by [S020_Sum_By_Part_By_Week1].[OrderDtl_PartNum]) as S050_SummarizeSalesDollars on
S040_SumPivot.OrderDtl_PartNum = S050_SummarizeSalesDollars.OrderDtl_PartNum) as S060_MergePivotWithDollars
left outer join (select
[PartBin].[PartNum] as [PartBin_PartNum],
(Sum(PartBin.OnhandQty)) as [Calculated_TotalOnHand]
from Erp.PartBin as PartBin
group by [PartBin].[PartNum]) as S070_GetInventoryStep1 on
S060_MergePivotWithDollars.OrderDtl_PartNum = S070_GetInventoryStep1.PartBin_PartNum) as S080_GetInventoryS2
where (S080_GetInventoryS2.Part_TypeCode = 'm')
