"【Question】 I want to build an opening & closing report – ITEM_TRANSACTION TABLE- Consider be .."

blackduckie RaqForum 28 No.
477 View • 4 Years ago

Multilevel Group Operation by Fixed Values

SQL(179)

【Question】

I want to build an opening & closing report –

ITEM_TRANSACTION TABLE-
Consider below data–
1.When item is prepared entry is made into table with NULL indicator.
2. When item is issued or sold entry is made into table with ISSUE indicator.

Date Iname Iquantity Indicator
1-Apr-14 Item1 15
2-Apr-14 Item1 10 ISSUE
2-Apr-14 Item1 3
3-Apr-14 Item1 5 ISSUE

I want to generate an opening & closing report (where closing quantity for today will be opening for tomorrow).

Date Iname Opening_Qty Today manufactured Total_balance Issued Closing
1-Apr-14 Item1 0 15 15 0 15
2-Apr-14 Item1 15 3 18 10 8
3-Apr-14 Item1 8 0 8 5 3

【Answer】

Here’s the item table recording date, name, quantity and status for items. INDICATOR field contains status values where “null” represents prepared and “issue” represents sold.

undefined

Requirement: Get the quantity of the item prepared and issued (sold) and, for each item, return the initial quantity, prepared quantity, sum of the initial and prepared quantity, sold quantity and remaining quantity per date. This requires that records be first grouped by specified dates and then re-grouped (aligned) by specified items. It is a multilevel grouping operation by fixed values. SQL achieves the algorithm using a nested query or a complex JOIN statement, as well as the window function to handle group & aggregate and inter-row calculations. That makes coding rather difficult.

SPL script:

	A
1	=connect(“test”)
2	=A1.query(“select _DATE, _NAME,sum(CASE _INDICATOR WHEN ‘ISSUE’ THEN 0 ELSE _QUANTITY END) TodayManufactured,sum(CASE _INDICATOR WHEN ‘ISSUE’ THEN _QUANTITY ELSE 0 END) Issued from ITEM_TRANSACTION group by _DATE, _NAME order by _NAME, _DATE”)
3	=A2.group(_NAME)
4	=A3.conj((c=0,~.derive(c:Opening_Qty,(b=c+TodayManufactured):Balance,(c=b-Issued):Closing)))

A1: Connect to the database named test.

A2: Group records in ITEM_TRANSACTION, the source table, first by dates and then by items and convert the item statuses into numerical values, and finally sort the records by items and then by dates.

A3: Group A2’s table sequence by item names. SPL’s group() function only groups data but don’t aggregate it, which makes convenient to further process each group of data.

A4: For each item in A3, add the required columns and get the required quantities on each date and concatenate these groups. c is a temporary variable for storing the current item quantity and then pass to the next records for another calculation.

Source table:

undefined