Inventory Movement - Pivot - with Opening Balance

Get Help
1

Hello everyone

I do have a table of item movement in which i keep every transaction of item.

Now
suppose filter from date and to date is 01-May to 15-May
and required output (pivot) is like
itemgroup | item category | item brand | item style | item season | Opening | Purchase | Sale | Adjustment | Purchase Return | Sales Return | Closing

I am unable to achieve this
through query i can achieve it but can not pivot that.

Thank You

2

We need to know more about how you want the data arranged for the pivot, in particular what time dimensions you want and how to aggregate the data.

You said you have a query to achieve the output you want. Can you post it? It should answer a bunch of these questions.

3

**Sorry for late reply

SELECT** DTL.CompCode, DTL.OrgID, DTL.LocationID, DTL.ItemID,

	DTL.BatchNo,DTL.SerialNo,DTL.VariantCode,

	DTL.ItemVCost LPP,

	DTL.ItemLandedCost WAC_WH,

	DTL.ItemWtAvgCost WAC,

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) < @**FROM** **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[1-OP\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '005' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[1-OPIN\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '004' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[2-PUR\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '012' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[2-PRT\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '009' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[3-**OUT**\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '010' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[3-**In**\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '001' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[4-POS\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '002' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[4-POSR\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode **IN**('011','019') **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[4-SAL\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '013' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[4-SRT\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode **in** ('016') **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[5-Prod\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode **in** ('015') **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[5-Cons\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode = '006' **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[6-Adj\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **AND** DTL.InvTxnCode **in**('017','014') **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[7-Pkg\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) >= @**FROM** **AND** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  \[8-STK\],

	(**CASE** **WHEN** **Convert**(**Date**,DTL.TxnDate) <= @**TO** **THEN** DTL.Qty\*DTL.ConvUnit **ELSE** 0 **END**)  **AS** \[9-QOH\]

FROM INVItemMovement(nolock) DTL

INNER JOIN ADMReportFilter FO WITH(NOLOCK)

ON FO.CompCode=DTL.CompCode AND FO.SystemName=@MacName

AND FO.ParamOrgID=DTL.OrgID AND FO.ProcedureName='grdOrgValue'

INNER JOIN MSTItem I WITH(NOLOCK)

ON I.CompCode=DTL.CompCode AND I.ID=DTL.ItemID

WHERE DTL.CompCode=@CompCode AND DTL.IsClosed=0

--AND I.ID=IIF(@ITM IS NULL OR @ITM=0,I.ID,@ITM)

--AND I.ArticleID=IIF(@ART IS NULL OR @ART=0,I.ArticleID,@ART)

AND DTL.TxnDate BETWEEN @From AND @To

4

This is just a table of states. A pivot needs aggregations (SUM(), etc.).

What are you trying to express with the pivot?

5

i need to give an option where a business user can choose attributes or item properties, can enter from and to date and see data in pivot where he can see opening of from date , inward and outward during from and to date, and closing of to date

6

What you need to do is wrap the inventory change columns in SUM() and drop any unneeded static columns from the query so the pivot knows how to compute the data columns when the time grouping, etc., changes. I would start small and work your way up, trying to pivot a dozen columns doesn’t work well.