End
client problem:
The end client is a Rs. 150(one hundred and
fifty) crore rupee firm in the trading of steel tubes for casting purposes in
the automotive sector.
Attached
were the business issues for the end client:
·
The end client desires visibility into its
inventory in terms of the following
parameters:
o Ageing
analysis
o ABC categorization
o Upper and
lower control limit for inventory ageing
·
The end client data was in disparate sources
of excel spreadsheets. The following were the issues with the excel
spreadsheets:
o The data
in the spreadsheets was not clean
o The end
client business logic was to be coded.
o There are
multiple and disparate spreadsheets for stock, stock valuation and sales.
·
Code maintainability: the end client has
limited skills on sql. The end client managers needed a system that could be
maintained by its internal resources with ease and convenience. The end client
requirements were as follows:
o Maintaining
of the business logic as per its requirements. This was specifically true for
the inventory ageing analysis
o
Developing report and dashboards as per end
client requirements.
In order
to achieve the same, the etl team devised the following course of action.
·
Developed the solution using microsoft
powerbi by using the same as an etl tool and a reporting tool.
·
The entire sql business logic of the end
client was converted into microsoft powerbi etl logic.
·
The reports and dashboards were developed
from the cleansed data of microsoft powerbi
·
Knowledge transfer was done for the end
client to ensure smooth operations for the same.
Attached
is the detailed description of the data cleansing, data auditing and data
profiling for the end client.
The data
providers given are as follows:
Attached are the data providers for the end
client usage:
·
‘a
category ageing’: this gives a comprehensive analysis for ‘a’ category items in
terms of abc analysis, ageing.
·
Abc
category: this gives abc categorization by
weight
·
Ageing_new:
this module was later developer to include ageing analysis by value
·
Final(2):
sales analysis
·
Month_month_analysis:this
is for sales analysis with built in month data.
Stock
data:
This is
the raw data given by end client.the raw data requires data cleansing.
the
following were the errors marked for the end client:
1.) Grade can
be numeric as well as character.
2.) Missing
values of dim1, make, grade and dates. These details have to be marked to the
end client. For example in dates, the end client could misrepresent the year of
the date input.
Cleasing
part:
Attached are
the steps for cleansing:
·
An addendum to grade of ‘-x’ was added. This was later removed in the powerbi.
Sales
data:
·
Now the work for the etl team is to load the
data in the powerbi.
Stock
sheet:
Next step
for the etl team :
·
Remove the extra variables from the data
using powerbi.
·
It has to be done by the delimiter function.
A
conditional column was added within powerbi for flat,rounds and sections
business logic. Attached is the snapshot for the same:
The
excess column of price was removed.
Next
step:
·
Added custom column to find the value.
·
Value=wt*price
Attached
is the snapshot for the same:
After
that we have to do group by:
·
Grouped rows on the basis of
grade,make,condition,section stock
·
Operation =sum
This is
our required data for reporting.
Attached
is snapshot for the same:
Next
step for ETL resource is to do ABC categorization.
Steps for
the abc categorization are as follows:
(the
attached module sql code was converted into powerbi module for the same)
ALTER PROCEDURE [dbo].[SP_ABC_CATEGORIZATION_AGEING]
AS
BEGIN
TRUNCATE TABLE ABC_CATEGORIZATION_BBY;
WITH
CTE1
AS(
SELECT A.*
--,B.CATEGORY
FROM
BBY_STOCK_DEC A
--JOIN ABC_CATEGORIZATION1 B
-- ON A.GRADE=B.GRADE
-- AND A.CONDITION=B.CONDITION
-- AND A.MAKE=B.MAKE
-- AND A.DIM1=B.DIM1
WHERE D2 IS NULL
),
CTE2
AS(SELECT DISTINCT A.* FROM CTE1 A
JOIN
ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.CONDITION=B.CONDITION
AND A.MAKE=B.MAKE
AND A.D1=B.D1),
CTE2A
AS(SELECT DISTINCT A.*,B.CATEGORY FROM CTE1 A
JOIN
ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.CONDITION=B.CONDITION
AND A.MAKE=B.MAKE
AND A.D1=B.D1),
CTE3
AS(SELECT * FROM CTE1
EXCEPT
SELECT * FROM CTE2),
/* PICK UP THAT STOCK WHERE CONDITION IS NULL AND MAKE IS
NULL*/
CTE4
AS(SELECT DISTINCT A.* FROM CTE3 A
JOIN ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.D1=B.D1
WHERE A.CONDITION IS NULL
AND A.MAKE IS NULL),
CTE4A
AS(SELECT DISTINCT A.*,B.CATEGORY FROM CTE3 A
JOIN ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.D1=B.D1
WHERE A.CONDITION IS NULL
AND A.MAKE IS NULL),
CTE5
AS(SELECT * FROM CTE3
EXCEPT
SELECT * FROM CTE4),
CTE6
AS(SELECT DISTINCT A.* FROM CTE5 A
JOIN ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.CONDITION=B.CONDITION
AND A.D1=B.D1
WHERE
A.MAKE IS NULL),
CTE6A
AS(SELECT DISTINCT A.*,B.CATEGORY FROM CTE5 A
JOIN ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.CONDITION=B.CONDITION
AND A.D1=B.D1
WHERE
A.MAKE IS NULL),
CTE7
AS(SELECT
* FROM CTE5
EXCEPT
SELECT * FROM
CTE6),
CTE8 AS(SELECT DISTINCT A.* FROM CTE7 A
JOIN
ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.MAKE=B.MAKE
AND A.D1=B.D1
WHERE A.CONDITION IS NULL),
CTE8A
AS(SELECT DISTINCT A.*,B.CATEGORY FROM CTE7 A
JOIN
ABC_CATEGORIZATION B
ON A.GRADE=B.GRADE
AND A.MAKE=B.MAKE
AND A.D1=B.D1
WHERE A.CONDITION IS NULL),
CTE9
AS(SELECT * FROM CTE7
EXCEPT
SELECT * FROM CTE8),
CTE9X
AS(SELECT * FROM CTE9
WHERE DATE IS NOT NULL),
CTE9XA
AS(SELECT DISTINCT A.*,B.CATEGORY FROM CTE9X
A
JOIN
ABC_CATEGORIZATION B
ON A.CONDITION=B.CONDITION
AND A.MAKE=B.MAKE
AND A.D1=B.D1
WHERE A.GRADE IS NULL),
CTE9XAB
AS(SELECT DISTINCT A.* FROM CTE9X A
JOIN
ABC_CATEGORIZATION B
ON A.CONDITION=B.CONDITION
AND A.MAKE=B.MAKE
AND A.D1=B.D1
WHERE A.GRADE IS NULL),
CTE9XC
AS(SELECT * FROM CTE9X
EXCEPT
SELECT * FROM CTE9XAB),
CTE9A
AS(SELECT * FROM CTE2A
UNION ALL
SELECT * FROM CTE4A
UNION ALL
SELECT * FROM CTE6A
UNION ALL
SELECT * FROM CTE8A
UNION ALL
SELECT * FROM CTE9XA)
SELECT * INTO #CTE9A FROM
CTE9A;
/*AGEING LOGIC AND TOTAL VALUE LOGIC*/
WITH CTE10
AS(SELECT A.*,
DATEDIFF(DAY,ISNULL(CONVERT(DATETIME, DATE, 104),'2017-01-01 00:00:00.000'),GETDATE()) AS AGEING,
CAST((A.BAL_WT)AS DECIMAL (18,2))* CAST((A.PRICE) AS DECIMAL(18,2)) AS TOTAL_VALUE
FROM #CTE9A A)
INSERT INTO ABC_CATEGORIZATION_BBY
(
[DATE ]
,[LOT NO]
,[GRADE]
,[MAKE]
,[CONDITION]
,[D1]
,[D2]
,[BAL_WT]
,[PRICE]
,[CATEGORY]
,[AGEING]
,[TOTAL_VALUE]
)
SELECT
[DATE ]
,[LOT NO]
,[GRADE]
,[MAKE]
,[CONDITION]
,[D1]
,[D2]
,[BAL_WT]
,[PRICE]
,[CATEGORY]
,[AGEING]
,[TOTAL_VALUE]
FROM
CTE10
END;
·
First step is to find cummulative sum.
o
CUMULATIVE_SUM_2
= CALCULATE(SUM(STOCK_VALUATION_TABLE[VALUE_SUM]),FILTER(ALL(STOCK_VALUATION_TABLE),STOCK_VALUATION_TABLE[Index]<=MAX(STOCK_VALUATION_TABLE[Index])))
·
Abc
categorization:
ABC = IF(((STOCK_VALUATION_TABLE[CUMULATIVE_SUM_2]/SUM(STOCK_VALUATION_TABLE[VALUE_SUM]))*100)<=50,
"A",IF(((STOCK_VALUATION_TABLE[CUMULATIVE_SUM_2]/SUM(STOCK_VALUATION_TABLE[VALUE_SUM]))*100)>50
&& ((STOCK_VALUATION_TABLE[CUMULATIVE_SUM_2]/SUM(STOCK_VALUATION_TABLE[VALUE_SUM]))*100)<80,"B","C"))
Next step
is the ageing work for the stock.
Ageing
requires:
·
Date difference
o
date_diff =
DATEDIFF(AGEING_NEW[Stock.Date],today(),DAY)
Attached
is the ageing of the stock:
Formulae
for ageing:
·
ageing = IF(AGEING_NEW[date_diff]>0
&& AGEING_NEW[date_diff]<365,"LESS THAN 1
YEAR",IF(AGEING_NEW[date_diff]>365 &&
AGEING_NEW[date_diff]<730,"BETWEEN 1 AND 2
YEAR",IF(AGEING_NEW[date_diff]>730 && AGEING_NEW[date_diff]
< 1095,"BETWEEN 2 AND 3 YEAR","MORE THAN 3 YEAR")))
|
|
The
final output for the end client was as follows:
·
ABC
categorization in terms of stock value(sample attached below)
- Inventory ageing analysis in terms of weight(sample attached below)
- Month
and month analysis(sample attached below):
Dashboard
Inventory
valuations from turbodata using DAX functionality
The etl team had the following target
points for converting the sql code to dax for weighted average calculations.
·
Reduce the deployment
times-remove the sql server extraction step
·
Reduce the execution times:
remove the sql code execution steps
·
Handle large data loads: remove
the bottlenecks associated with ms excel
·
High end data auditing and data profiling solution using powerbi
·
Reduced costs of maintenance of
the solution: reduced dependence on sql developers for the solution execution.
What this solution does not handle?
·
Incremental data loads from
large disparate databases.
Attached document explains the conversion
of the complex sql code to powerbi code
·
Summarization of the debit and
credit entries: the etl team has assumed that all the debit entries and credit
entries entailing the stock item details shall be considered irrespective of
the voucher type details.
The attached sql code was converted into an
aggregate using dax functionality
SELECT DISTINCT STOCKITEM_NM AS STOCK_ITEM_NAME INTO #DAYBOOK_STOCK_ITEM_NM FROM STG_ITEM;
CREATE INDEX #DAYBOOK_STOCK_ITEM_NM_IDX ON #DAYBOOK_STOCK_ITEM_NM
(STOCK_ITEM_NAME);
--DELETE FROM MOVING_AVERAGE_RATE WHERE ITEM_NAME IN (SELECT
STOCK_ITEM_NAME FROM #DAYBOOK_STOCK_ITEM_NM);
WITH CTE1 AS (
SELECT E.DIM_COMPANY_ID,A.RATE,A.SALE_QUANTITY,(A.RATE*A.SALE_QUANTITY) AS SALE_VALUE,A.ORDER_DATE AS DATE_DESC,A.ITEM_SUPPLIER_ORDER_ID,A.ORDER_NO, W.LOCATION ,B.ITEM ,B.DIM_ITEM_ID,W.DIM_WAREHOUSE_ID,D.DIM_DATE_ID
FROM ITEM_SALES_ORDER
A
JOIN DIM_ITEM
B
ON A.DIM_ITEM_ID=B.DIM_ITEM_ID
JOIN DIM_WAREHOUSE
W
ON A.DIM_WAREHOUSE_ID=W.DIM_WAREHOUSE_ID
JOIN DIM_DATE
D
ON A.ORDER_DATE=D.DATE_DESC
JOIN #DAYBOOK_STOCK_ITEM_NM
C
ON B.ITEM=C.STOCK_ITEM_NAME
JOIN DIM_COMPANY
E ON E.COMPANY_NAME=A.COMPANY_NAME
UNION ALL
SELECT E.DIM_COMPANY_ID,A.RATE,A.JOURNAL_CREDIT_QUANTITY,(A.RATE*A.JOURNAL_CREDIT_QUANTITY) AS JOURNAL_CREDIT_VALUE,A.ORDER_DATE ASDATE_DESC, A.ITEM_SUPPLIER_ORDER_ID,A.ORDER_NO,W.LOCATION ,B.ITEM ,B.DIM_ITEM_ID,W.DIM_WAREHOUSE_ID,D.DIM_DATE_ID
FROM ITEM_JOURNAL_CREDIT
A
JOIN DIM_ITEM
B
ON A.DIM_ITEM_ID=B.DIM_ITEM_ID
JOIN DIM_WAREHOUSE
W
ON A.DIM_WAREHOUSE_ID=W.DIM_WAREHOUSE_ID
JOIN DIM_DATE
D
ON A.ORDER_DATE=D.DATE_DESC
JOIN #DAYBOOK_STOCK_ITEM_NM
C
ON B.ITEM=C.STOCK_ITEM_NAME
JOIN DIM_COMPANY
E ON E.COMPANY_NAME=A.COMPANY_NAME
--where B.DIM_ITEM_ID=696
)
SELECT * INTO #CTE1 FROM CTE1;
--SELECT * FROM #CTE1
WITH CTE2 AS(
SELECT E.DIM_COMPANY_ID,A.RATE,A.PURCHASE_QUANTITY,(A.RATE*A.PURCHASE_QUANTITY) AS PURHASE_VALUE,A.ORDER_DATE ASDATE_DESC,A.ITEM_SUPPLIER_ORDER_ID,A.ORDER_NO, W.LOCATION ,B.ITEM ,B.DIM_ITEM_ID,W.DIM_WAREHOUSE_ID,D.DIM_DATE_ID
FROM ITEM_PURCHASE_ORDER
A
JOIN DIM_ITEM
B
ON A.DIM_ITEM_ID=B.DIM_ITEM_ID
JOIN DIM_WAREHOUSE
W
ON A.DIM_WAREHOUSE_ID=W.DIM_WAREHOUSE_ID
JOIN DIM_DATE
D
ON A.ORDER_DATE=D.DATE_DESC
JOIN #DAYBOOK_STOCK_ITEM_NM
C
ON B.ITEM=C.STOCK_ITEM_NAME
JOIN DIM_COMPANY
E ON E.COMPANY_NAME=A.COMPANY_NAME
--where
B.DIM_ITEM_ID=696
UNION ALL
SELECT E.DIM_COMPANY_ID,A.RATE,A.JOURNAL_DEBIT_QUANTITY,(A.RATE*A.JOURNAL_DEBIT_QUANTITY) AS JOURNAL_DEBIT_VALUE,A.ORDER_DATE ASDATE_DESC,A.ITEM_SUPPLIER_ORDER_ID,A.ORDER_NO, W.LOCATION ,B.ITEM ,B.DIM_ITEM_ID,W.DIM_WAREHOUSE_ID,D.DIM_DATE_ID
FROM ITEM_JOURNAL_DEBIT
A
JOIN DIM_ITEM
B
ON A.DIM_ITEM_ID=B.DIM_ITEM_ID
JOIN DIM_WAREHOUSE
W
ON A.DIM_WAREHOUSE_ID=W.DIM_WAREHOUSE_ID
JOIN DIM_DATE
D
ON A.ORDER_DATE=D.DATE_DESC
JOIN #DAYBOOK_STOCK_ITEM_NM
C
ON B.ITEM=C.STOCK_ITEM_NAME
JOIN DIM_COMPANY
E ON E.COMPANY_NAME=A.COMPANY_NAME
--where
B.DIM_ITEM_ID=696
)
SELECT * INTO #CTE2 FROM CTE2;
WITH
CTE3 AS(
SELECT DATE_DESC, LOCATION ,ITEM ,DIM_ITEM_ID,
DIM_WAREHOUSE_ID,
DIM_DATE_ID,DIM_COMPANY_ID
FROM
#CTE1
UNION ALL
SELECT DATE_DESC, LOCATION ,ITEM ,DIM_ITEM_ID,
DIM_WAREHOUSE_ID,
DIM_DATE_ID,DIM_COMPANY_ID
FROM #CTE2),
CTE4 AS(
SELECT ROW_NUMBER() OVER(ORDER BY
DIM_WAREHOUSE_ID ,
DIM_ITEM_ID,DIM_DATE_ID, DATE_DESC,DIM_COMPANY_ID)AS ROWID,DIM_DATE_ID,
DIM_WAREHOUSE_ID ,
DIM_ITEM_ID ,DATE_DESC,DIM_COMPANY_ID
FROM CTE3)
The dax code used to convert the sql code
is as follows:
SUMMARIZE('Cumulative inventory
total','Cumulative inventory total'[ITEM],'Cumulative inventory
total'[ordeR_date],"total_purchase",sum('Cumulative inventory
total'[quantity_debit]),"total_purchase_value",sum('Cumulative
inventory
total'[purchase_value_2]),"net_debit_credit_qty",sum('Cumulative
inventory total'[quantity_debit_credit]))
Explanation
of the quantity_debit: if('Cumulative inventory total'[is_deemed_positive]="yes",'Cumulative
inventory total'[quantity],0)
Purchase_value_2: IF('Cumulative inventory
total'[is_deemed_positive]="yes",'Cumulative inventory
total'[QUANTITY_APPENDED]*'Cumulative inventory total'[RATE_APPENDED],0)
Net_debit_credit_qty: SUMMARIZE('Cumulative inventory total','Cumulative
inventory total'[ITEM],'Cumulative inventory
total'[ordeR_date],"total_purchase",sum('Cumulative inventory
total'[quantity_debit]),"total_purchase_value",sum('Cumulative
inventory total'[purchase_value_2]),"net_debit_credit_qty",sum('Cumulative
inventory total'[quantity_debit_credit]))
Removal of the perpetual weighted average
inventory calculations using complex while do loop across all the stock items
as given. For the audit purposes the end clients expected the inventory
valuations on a daily basis to catch any data entry errors.
The dax code uses running sum across all date
ranges for the stock items to capture the historical stock valuations.
CTE5 AS(
SELECT MIN(ROWID)AS MINROW,DIM_DATE_ID,
DIM_WAREHOUSE_ID ,
DIM_ITEM_ID ,DATE_DESC,DIM_COMPANY_ID
FROM CTE4
GROUP BY DIM_DATE_ID,
DIM_WAREHOUSE_ID ,
DIM_ITEM_ID,DATE_DESC,DIM_COMPANY_ID)
INSERT INTO [MOVING_AVERAGE_RATE]( DIM_DATE_ID,DIM_ITEM_ID,DATE_DESC,DIM_COMPANY_ID)
SELECT
DIM_DATE_ID,DIM_ITEM_ID,DATE_DESC,DIM_COMPANY_ID
FROM CTE5
ORDER BY
DIM_ITEM_ID,DIM_DATE_ID, DATE_DESC,DIM_COMPANY_ID;
WITH CTE_PURCHASE AS (
SELECT SUM(PURCHASE_QUANTITY) AS PURCHASE_QUANTITY,SUM(PURHASE_VALUE) AS PURCHASE_VALUE,
DIM_ITEM_ID,DIM_DATE_ID,DIM_COMPANY_ID,DIM_WAREHOUSE_ID
FROM #CTE2
GROUP BY DIM_ITEM_ID,DIM_DATE_ID,DIM_COMPANY_ID,DIM_WAREHOUSE_ID)
UPDATE MOVING_AVERAGE_RATE
SET PURCHASE_QUANTITY= ISNULL(B.PURCHASE_QUANTITY,0),
PURCHASE_VALUE=ISNULL(B.PURCHASE_VALUE,0)
FROM MOVING_AVERAGE_RATE
A
LEFT OUTER JOIN CTE_PURCHASE
B
ON
--A.DIM_WAREHOUSE_ID=B.DIM_WAREHOUSE_ID
--AND
A.DIM_ITEM_ID=B.DIM_ITEM_ID
AND A.DIM_DATE_ID=B.DIM_DATE_ID
AND A.DIM_COMPANY_ID=B.DIM_COMPANY_ID;
WITH CTE_SALES AS (
SELECT SUM(SALE_QUANTITY) AS SALE_QUANTITY,SUM(SALE_VALUE) AS SALE_VALUE,
DIM_ITEM_ID,DIM_DATE_ID,DIM_COMPANY_ID,DIM_WAREHOUSE_ID
FROM #CTE1
GROUP BY DIM_ITEM_ID,DIM_DATE_ID,DIM_COMPANY_ID,DIM_WAREHOUSE_ID)
UPDATE MOVING_AVERAGE_RATE
SET SALE_QUANTITY= ISNULL(B.SALE_QUANTITY,0),
SALE_VALUE=ISNULL(B.SALE_VALUE,0)
FROM MOVING_AVERAGE_RATE
A
LEFT OUTER JOIN CTE_SALES
B
ON A.DIM_ITEM_ID=B.DIM_ITEM_ID
AND A.DIM_DATE_ID=B.DIM_DATE_ID
AND A.DIM_COMPANY_ID=B.DIM_COMPANY_ID;
WITH CTE1
AS(SELECT ROW_NUMBER() OVER(OrdeR BY ltrim(rtrim(replace(REPLACE(item_name,char(13),''),char(10),'')))) AS ROW_NUMBER2,
ltrim(rtrim(replace(REPLACE(item_name,char(13),''),char(10),''))) AS item_name,
CAST(QTY AS FLOAT)AS QTY,CAST(VALUE AS FLOAT) AS VALUE,CAST(RATE AS FLOAT) AS RATE FROM dbo.OPENING_STOCK
WHERE VALUE IS NOT NULL
GROUP BY ltrim(rtrim(replace(REPLACE(item_name,char(13),''),char(10),'')))
,CAST(VALUE AS FLOAT),CAST(QTY AS FLOAT),CAST(RATE AS FLOAT)
),
CTE2
AS(SELECT MIN(ROW_NUMBER2) AS MIN_ROW_ID,
item_name
FROM CTE1
GROUP BY item_name),
CTE3
AS(SELECT A.* FROM CTE1
A
JOIN CTE2 B ON
A.ROW_NUMBER2=B.MIN_ROW_ID
--WHERE A.QTY IS NOT NULL
)
UPDATE MOVING_AVERAGE_RATE
SET OPENING_STOCK=ISNULL(CAST(CTE3.QTY AS FLOAT),0),
OPENING_STOCK_VALUE=ISNULL(CAST(CTE3.VALUE AS FLOAT),0)
,
MOVING_AVERAGE_RATE=CASE WHEN (ISNULL(CAST(CTE3.QTY AS FLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(CAST(CTE3.QTY AS FLOAT),0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN 0
ELSE ((ISNULL(CAST(CTE3.VALUE AS FLOAT),0)+(PURCHASE_VALUE))/(ISNULL(CAST(CTE3.QTY ASFLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)))
END
/*CASE WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS
FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(OPENING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)>0 THEN
CAST(PURCHASE_VALUE AS FLOAT)/CAST(PURCHASE_QUANTITY AS FLOAT)
WHEN ISNULL(OPENING_STOCK,0) >0 AND PURCHASE_QUANTITY >0
THEN ((ISNULL(OPENING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(ISNULL(OPENING_STOCK,0)
AS FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT)))
ELSE (ISNULL(OPENING_STOCK_VALUE,0))/CAST(ISNULL(OPENING_STOCK,0)
AS FLOAT)
END*/
,
--COST_OF_GOODS_SOLD
= CASE WHEN PURCHASE_QUANTITY=0 THEN 0 ELSE
SALE_QUANTITY*(PURCHASE_VALUE/PURCHASE_QUANTITY) END,
/*
COST_OF_GOODS_SOLD=(CASE
--WHEN (OPENING_STOCK+CAST(PURCHASE_QUANTITY AS
FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN CAST(ISNULL(OPENING_STOCK,0) AS FLOAT)=0 AND CAST(PURCHASE_QUANTITY AS
FLOAT)>0 THEN CAST(PURCHASE_VALUE AS FLOAT)/CAST(PURCHASE_QUANTITY AS FLOAT)
WHEN CAST(ISNULL(OPENING_STOCK,0) AS FLOAT) >0 AND
CAST(PURCHASE_QUANTITY AS FLOAT) >0 THEN
((ISNULL(OPENING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(ISNULL(OPENING_STOCK,0)
AS FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT)))
ELSE (ISNULL(OPENING_STOCK_VALUE,0))/CAST(ISNULL(OPENING_STOCK,0)
AS FLOAT)
END)*SALE_QUANTITY,*/
CLOSING_STOCK_VALUE= ((ISNULL(CAST(CTE3.QTY AS FLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT)))*
(CASE WHEN (ISNULL(CAST(CTE3.QTY AS FLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(CAST(CTE3.QTY AS FLOAT),0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN 0
ELSE ((ISNULL(CAST(CTE3.VALUE AS FLOAT),0)+(PURCHASE_VALUE))/(ISNULL(CAST(CTE3.QTY ASFLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)))
END),
/*(CASE WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS
FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(OPENING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)>0 THEN
CAST(PURCHASE_VALUE AS FLOAT)/CAST(PURCHASE_QUANTITY AS FLOAT)
WHEN ISNULL(OPENING_STOCK,0) >0 AND PURCHASE_QUANTITY >0
THEN
((ISNULL(OPENING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(ISNULL(OPENING_STOCK,0)
AS FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT)))
ELSE (ISNULL(OPENING_STOCK_VALUE,0))/CAST(ISNULL(OPENING_STOCK,0)
AS FLOAT)
END)*(CAST(ISNULL(OPENING_STOCK,0) AS FLOAT)+CAST(PURCHASE_QUANTITY AS
FLOAT)-CAST(SALE_QUANTITY AS FLOAT)),*/
CLOSING_STOCK=(ISNULL(CAST(CTE3.QTY AS FLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT)),
COST_OF_GOODS_SOLD = ISNULL(CAST(CTE3.VALUE AS FLOAT),0)+CAST(PURCHASE_VALUE AS FLOAT)-((ISNULL(CAST(CTE3.QTY AS FLOAT),0)+CAST(PURCHASE_QUANTITY ASFLOAT)-CAST(SALE_QUANTITY AS FLOAT)))*
(CASE WHEN (ISNULL(CAST(CTE3.QTY AS FLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(CAST(CTE3.QTY AS FLOAT),0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN 0
ELSE ((ISNULL(CAST(CTE3.VALUE AS FLOAT),0))+(PURCHASE_VALUE))/(ISNULL(CAST(CTE3.QTY ASFLOAT),0)+CAST(PURCHASE_QUANTITY AS FLOAT))
END)
FROM MOVING_AVERAGE_RATE
A
JOIN DIM_ITEM
B
ON A.DIM_ITEM_ID=B.DIM_ITEM_ID
LEFT OUTER JOIN CTE3 ON CTE3.ITEM_NAME=B.ITEM
WHERE MOVING_AVERAGE_RATE_ID IN (SELECT MIN(MOVING_AVERAGE_RATE_ID) FROM MOVING_AVERAGE_RATE
A
GROUP BY DIM_ITEM_ID);
DELETE FROM MOVING_AVERAGE_RATE WHERE CLOSING_STOCK<0;
DECLARE @COUNT INT;
DECLARE @COUNT_ITEM_START INT;
DECLARE @COUNT_ITEM INT;
DECLARE @CHECKNO INT;
DECLARE @MOVING_AVERAGE_RATE DECIMAL(15,3);
DECLARE @CLOSING_STOCK DECIMAL(15,3);
DECLARE @CLOSING_STOCK_VALUE DECIMAL(15,3);
--DECLARE @MIN_DIM_WAREHOUSE_ID INT;
--SET @MIN_DIM_WAREHOUSE_ID=(SELECT MIN(DIM_WAREHOUSE_ID) FROM
OPENING_STOCK);
--DECLARE @MAX_DIM_WAREHOUSE_ID INT;
--SET @MAX_DIM_WAREHOUSE_ID=(SELECT MAX(DIM_WAREHOUSE_ID) FROM
OPENING_STOCK);
SET @COUNT_ITEM_START=1;
--WHILE(@MIN_DIM_WAREHOUSE_ID<=@MAX_DIM_WAREHOUSE_ID)
--BEGIN
SET @COUNT_ITEM_START=1;
SET @COUNT_ITEM=(SELECT COUNT(DIM_ITEM_ID) FROM DIM_ITEM);
WHILE @COUNT_ITEM_START<=@COUNT_ITEM
BEGIN
SET @COUNT=(SELECT MAX(MOVING_AVERAGE_RATE_ID) FROM MOVING_AVERAGE_RATE WHERE DIM_ITEM_ID=@COUNT_ITEM_START)
SET @CHECKNO=(SELECT MIN(MOVING_AVERAGE_RATE_ID)+1 FROM MOVING_AVERAGE_RATE WHERE DIM_ITEM_ID=@COUNT_ITEM_START) ;
WHILE(@CHECKNO<=@COUNT)
BEGIN
UPDATE MOVING_AVERAGE_RATE
SET OPENING_STOCK=S.CLOSING_STOCK,
OPENING_STOCK_VALUE=S.CLOSING_STOCK_VALUE,
@MOVING_AVERAGE_RATE=S.MOVING_AVERAGE_RATE,
--
,COST_OF_GOODS_SOLD=S.MOVING_AVERAGE_RATE*A.SALE_QUANTITY,
@CLOSING_STOCK=ISNULL(S.CLOSING_STOCK,0),
@CLOSING_STOCK_VALUE=ISNULL(S.CLOSING_STOCK_VALUE,0)
FROM MOVING_AVERAGE_RATE
A
JOIN (SELECT A.MOVING_AVERAGE_RATE_ID,B.CLOSING_STOCK,B.CLOSING_STOCK_VALUE,B.MOVING_AVERAGE_RATE
FROM MOVING_AVERAGE_RATE
A
JOIN MOVING_AVERAGE_RATE
B
ON A.MOVING_AVERAGE_RATE_ID=B.MOVING_AVERAGE_RATE_ID+1
WHERE A.MOVING_AVERAGE_RATE_ID IN (SELECT MIN(MOVING_AVERAGE_RATE_ID) FROM MOVING_AVERAGE_RATE
WHERE OPENING_STOCK IS NULL
AND DIM_ITEM_ID=@COUNT_ITEM_START
GROUP BY DIM_ITEM_ID,DIM_DATE_ID)
)S
ON S.MOVING_AVERAGE_RATE_ID=A.MOVING_AVERAGE_RATE_ID
JOIN DIM_ITEM
D
ON A.DIM_ITEM_ID=D.DIM_ITEM_ID
AND A.DIM_ITEM_ID=@COUNT_ITEM_START
AND D.ITEM IN (SELECT STOCK_ITEM_NAME FROM #DAYBOOK_STOCK_ITEM_NM)
--AND
A.DIM_WAREHOUSE_ID=@MIN_DIM_WAREHOUSE_ID
--AND (ISNULL(A.OPENING_STOCK,0)+CAST(A.PURCHASE_QUANTITY AS
FLOAT)-CAST(A.SALE_QUANTITY AS FLOAT))>=0
UPDATE MOVING_AVERAGE_RATE
SET
CLOSING_STOCK=OPENING_STOCK+PURCHASE_QUANTITY-SALE_QUANTITY,
MOVING_AVERAGE_RATE=CASE WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(OPENING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN @MOVING_AVERAGE_RATE
WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))<0 THEN @MOVING_AVERAGE_RATE*(-1)
ELSE ((ISNULL(OPENING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(OPENING_STOCK AS FLOAT)+CAST(PURCHASE_QUANTITYAS FLOAT)))
END,
/*COST_OF_GOODS_SOLD=
/*(CASE WHEN
(ISNULL(@CLOSING_STOCK,0)+CAST(ISNULL(PURCHASE_QUANTITY,0) AS
FLOAT)-CAST(ISNULL(SALE_QUANTITY,0) AS FLOAT))=0 THEN 0
WHEN ISNULL(@CLOSING_STOCK,0)=0 AND CAST(ISNULL(PURCHASE_QUANTITY,0) AS
FLOAT)>0 THEN CAST(ISNULL(PURCHASE_VALUE,0) AS FLOAT)/CAST(PURCHASE_QUANTITY
AS FLOAT)
WHEN ISNULL(@CLOSING_STOCK,0)>0 AND ISNULL(PURCHASE_QUANTITY,0)
>0 THEN
((ISNULL(@CLOSING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(ISNULL(@CLOSING_STOCK,0)
AS FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT)))
WHEN ISNULL(@CLOSING_STOCK,0) >0 AND
ISNULL(PURCHASE_QUANTITY,0) =0 THEN (ISNULL(@CLOSING_STOCK_VALUE,0))/CAST(ISNULL(@CLOSING_STOCK,0)
AS FLOAT)
ELSE @MOVING_AVERAGE_RATE END)*/
(CASE WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS
FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(OPENING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN
@MOVING_AVERAGE_RATE
ELSE
((ISNULL(OPENING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(OPENING_STOCK AS
FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT)))
END)*A.SALE_QUANTITY,*/
--CLOSING_STOCK_VALUE=
OPENING_STOCK_VALUE+PURCHASE_VALUE-COST_OF_GOODS_SOLD
CLOSING_STOCK_VALUE=CASE WHEN (OPENING_STOCK+PURCHASE_QUANTITY-SALE_QUANTITY=0) THEN 0
ELSE
(OPENING_STOCK_VALUE+PURCHASE_VALUE-
((CASE WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(OPENING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN @MOVING_AVERAGE_RATE
WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))<0 THEN @MOVING_AVERAGE_RATE*(-1)
ELSE ((ISNULL(OPENING_STOCK_VALUE,0)+(PURCHASE_VALUE))/(CAST(OPENING_STOCK AS FLOAT)+CAST(PURCHASE_QUANTITYAS FLOAT)))
END)*A.SALE_QUANTITY)) END ,
COST_OF_GOODS_SOLD=
(CASE WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))=0 THEN 0
WHEN ISNULL(OPENING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)=0 THEN @MOVING_AVERAGE_RATE
WHEN (ISNULL(OPENING_STOCK,0)+CAST(PURCHASE_QUANTITY AS FLOAT)-CAST(SALE_QUANTITY AS FLOAT))<0 THEN @MOVING_AVERAGE_RATE*(-1)
ELSE
((ISNULL(OPENING_STOCK_VALUE,0)+PURCHASE_VALUE))/(CAST(OPENING_STOCK AS FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT))
END)*A.SALE_QUANTITY
/*ISNULL(@CLOSING_STOCK_VALUE,0)+PURCHASE_VALUE-(CASE WHEN
(ISNULL(@CLOSING_STOCK,0)+CAST(ISNULL(PURCHASE_QUANTITY,0) AS
FLOAT)-CAST(ISNULL(SALE_QUANTITY,0) AS FLOAT))=0 THEN 0
WHEN ISNULL(@CLOSING_STOCK,0)=0 AND CAST(PURCHASE_QUANTITY AS FLOAT)>0 THEN
CAST(PURCHASE_VALUE AS FLOAT)/CAST(PURCHASE_QUANTITY AS FLOAT)
WHEN @CLOSING_STOCK >0 AND PURCHASE_QUANTITY >0 THEN
((@CLOSING_STOCK_VALUE+(PURCHASE_VALUE))/(CAST(@CLOSING_STOCK AS
FLOAT)+CAST(PURCHASE_QUANTITY AS FLOAT)))
WHEN @CLOSING_STOCK >0 AND PURCHASE_QUANTITY =0
THEN (@CLOSING_STOCK_VALUE)/CAST(@CLOSING_STOCK AS FLOAT)
ELSE @MOVING_AVERAGE_RATE END)*A.SALE_QUANTITY*/
FROM MOVING_AVERAGE_RATE
A
JOIN DIM_ITEM
D
ON A.DIM_ITEM_ID=D.DIM_ITEM_ID
AND A.DIM_ITEM_ID=@COUNT_ITEM_START
--AND A.DIM_WAREHOUSE_ID=@MIN_DIM_WAREHOUSE_ID
WHERE A.MOVING_AVERAGE_RATE_ID IN (SELECT MAX(MOVING_AVERAGE_RATE_ID) FROM MOVING_AVERAGE_RATE
WHERE OPENING_STOCK IS NOT NULL
AND DIM_ITEM_ID=@COUNT_ITEM_START
--AND (ISNULL(A.OPENING_STOCK,0)+CAST(A.PURCHASE_QUANTITY AS
FLOAT)-CAST(A.SALE_QUANTITY AS FLOAT))>=0
GROUP BY DIM_ITEM_ID)
AND D.ITEM IN (SELECT STOCK_ITEM_NAME FROM #DAYBOOK_STOCK_ITEM_NM);
SET @CLOSING_STOCK=(SELECT (OPENING_STOCK+PURCHASE_QUANTITY-SALE_QUANTITY) FROM MOVING_AVERAGE_RATE
WHERE DIM_ITEM_ID=@COUNT_ITEM_START
AND MOVING_AVERAGE_RATE_ID=@CHECKNO);
IF @CLOSING_STOCK<0 BREAK;
SET @CHECKNO=@CHECKNO+1;
END;
SET @COUNT_ITEM_START=@COUNT_ITEM_START+1;
PRINT 'The following item id has
been loaded '+CAST(@COUNT_ITEM_START AS VARCHAR);
END;
--SET @MIN_DIM_WAREHOUSE_ID=@MIN_DIM_WAREHOUSE_ID+1;
--PRINT 'The following warehouse id has been loaded
'+CAST(@MIN_DIM_WAREHOUSE_ID AS VARCHAR);
--END;
END;
The following is the dax code for the same:
Cumulative
of purchase quantity:
cumulative_purchase_no
= CALCULATE(SUM('table2'[total_purchase]),filter(allexcept('table2','table2'[ITEM]),'table2'[ordeR_date]<=max('table2'[ordeR_date])))
cumulative
of purchase value:
cumulative_purchase
value =
CALCULATE(SUM('table2'[total_purchase_value]),filter(allexcept('table2','table2'[ITEM]),'table2'[ordeR_date]<=max('table2'[ordeR_date])))
cumulative_purchase value = CALCULATE(SUM('table2'[total_purchase_value]),filter(allexcept('table2','table2'[ITEM]),'table2'[ordeR_date]<=max('table2'[ordeR_date])))
cumulative_purchase_qty2 = 'table2'[cumulative_purchase_no]+'table2'[opening_stock qty]
net_closing_qty =
SUM('table2'[net_debit_credit_qty])
+ SUM('table2'[opening_stock qty])
opening_stock qty = LOOKUPVALUE(openinG_stock[DSPCLQTY],openinG_stock[DSPDISPNAME],'table2'[ITEM])
opening_stock rate = LOOKUPVALUE(openinG_stock[DSPCLRATE],openinG_stock[DSPDISPNAME],'table2'[ITEM])
weighted_average =
DIVIDE(
[cumulative_purchase
value2],
SUM('table2'[cumulative_purchase_qty2])
)
Final result:
Results of the
code:
·
Attached are the final results
for the process:
o
Reduced execution times
o
Reduced times for the weighted
average calculations
o
Better error handling and
logging capabilities
o
Reduced deployment requirements
Prepared by :
Ankur
- Linkedin profile: https://www.linkedin.com/in/ankur-gupta-3b9a71179
Email: apoorv@mnnbi.com
website: www.mnnbi.com
Phone: +91-8802466356
(For additional details on how PowerBI could be used for better reporting, contact above)
