jov1569 avatar image
jov1569 asked

SQL Query to forward last month current bal to Balance last month of next month

Guys, I have 3 data tables to consolidate and calculate the balance_cm(current month) and balance_lm(last month) and Order Qty. I want to add the result of balance_cm and balance_lm and Order qty as column. Also the balance_cm of previous month would be my balance_lm of next month. Thank you for help in advance.... Please see below DDL and desired result. Formula Order Qty: CASE WHEN (t1.oemneedqty - (t1.OnHandqty + onorderqty)- ISNULL(t2.balance_lm,0)) <0 THEN 0 ELSE (t1.oemneedqty - (t1.OnHandqty + onorderqty)- ISNULL(t2.balance_lm,0)) END AS Order_Qty, Balance_CM formula: CASE WHEN (t1.oemneedqty - (t1.OnHandqty + onorderqty)- ISNULL(t2.balance_lm,0)) >= 0 THEN 0 ELSE (t1.oemneedqty - (t1.OnHandqty + onorderqty)- ISNULL(t2.balance_lm,0)) * -1 END AS balance_cm My Query select oe.po_month, oe.item, oe.x_oemneedqty as oemneedqty ,sum(case when oh.a_onhandqty is null then 0 else oh.a_onhandqty end) as OnHandqty ,sum(case when op.b_onorderqty is null then 0 else op.b_onorderqty end) as onorderqty ,SUBSTRING(CAST(CONVERT(date,DATEADD(month,1,CONVERT(datetime,SUBSTRING(oe.po_month,1,4)+SUBSTRING(oe.po_month,6,2)+'01'))) AS VARCHAR),1,7) AS po_month_before ,SUBSTRING(CAST(CONVERT(date,DATEADD(month,+1,CONVERT(datetime,SUBSTRING(oe.po_month,1,4)+SUBSTRING(oe.po_month,6,2)+'01'))) AS VARCHAR),1,7) AS po_month_curr FROM #oemneed oe LEFT OUTER join #onhand oh on oe.po_month= oh.oh_month LEFT OUTER join #openorder op on oe.po_month= op.delivery_month GROUP BY oe.po_month ,oe.item ,oe.x_oemneedqty Sample Desired Result: let say po_month '2019-08' balance_lm = 0 po_month----item---oemneedqty---OnHandqty--onorderqty----Order_Qty balance_cm balance_lm 2016-09-----A1003----1000---------900--------600------------0----------500---------0 2016-10-----A1003----2000---------600--------0------------900----------0----------500 2016-11-----A1003----1000---------500--------200----------300----------0-----------0 2016-12-----A1003----1000---------500--------600-----------0-----------100 --------0 2017-01-----A1003----1000---------500-------1200-----------0-----------800--------100
sql-server-2008ms sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@jov1569 What is the data type of the column named **po\_month**? Is it a datetime or you actually have it stored as varchar in **yyyy-MM** format? What is the purpose of the columns **po\_month\_before** and **po\_month\_curr**? They are present in the script but not in the desired result, but in any case, they formulas you use to format them don't necessarily have to be so complicated provided that **po\_month** is a **datetime** or **date** column. It looks like what you are asking is related to finding a way to have the information about the previous row while reading the current row. For that there are analytical functions named LEAD and LAG, but because it looks like your SQL Server is 2008, the functions are not available. This is not a problem at all because there are relatively easy ways to mimic their functionality. Please provide info about the questions above and I can come up with the script to get the desired results.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@jov1569 It looks like the formulas for order quantity and current month balance are the functions of the previous month balance. The previous month balance is in turn the same with current month balance of the previous row which in turn is a function of its own previous month balance, etc. This means that there is a recursive dependency here which stops at the earliest available record per item. Please confirm that this is correct. Also, is this true that there are OH inventory records for future months? This is somewhat unusual, because usually the OH inventory is only available for history, not for future periods.
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.