Itemwise subtotalling of PurPrice needed in calculating a field value

mpapreja · April 23, 2020, 10:43am

Dear Experts,

Need your help. I having date in 3 tables

+----------+-------------+-------------+
| SalesABC | | |
+----------+-------------+-------------+
| Code | Cost Amount | Sale Amount |
| ABC | 70 | 100 |
+----------+-------------+-------------+

+-------------+-------------+-----------+----------+--------+
| PurchaseABC | | | | |
+-------------+-------------+-----------+----------+--------+
| RawCode | Description | Pur Price | Currency | Vendor |
| A | Kit | 0.495 | Euro | S |
| B | Barrel | 0.282 | $ | C |
| C | Rod | 15 | INR | L |
+-------------+-------------+-----------+----------+--------+

+----------+------------+-----------+------+------+
| BOMABC | | | | |
+----------+------------+-----------+------+------+
| Line No. | ParentCode | ChildCode | Qty. | Unit |
| 1000 | ABC | A | 1 | Pcs |
| 2000 | ABC | B | 1 | Pcs |
| 3000 | ABC | C | 1 | Pcs |
+----------+------------+-----------+------+------+

Following are the commands to create and populate above tables.

CREATE TABLE SalesABC(
Code VARCHAR(3) NOT NULL PRIMARY KEY
,Cost_Amount INTEGER NOT NULL
,Sale_Amount INTEGER NOT NULL
);
INSERT INTO SalesABC(Code,Cost_Amount,Sale_Amount) VALUES ('ABC',70,100);

CREATE TABLE PurchaseABC(
RawCode VARCHAR(1) NOT NULL PRIMARY KEY
,Description VARCHAR(6) NOT NULL
,Pur_Price NUMERIC(5,3) NOT NULL
,Currency VARCHAR(4) NOT NULL
,Vendor VARCHAR(1) NOT NULL
);
INSERT INTO PurchaseABC(RawCode,Description,Pur_Price,Currency,Vendor) VALUES ('A','Kit',0.495,'Euro','S');
INSERT INTO PurchaseABC(RawCode,Description,Pur_Price,Currency,Vendor) VALUES ('B','Barrel',0.282,'$','C');
INSERT INTO PurchaseABC(RawCode,Description,Pur_Price,Currency,Vendor) VALUES ('C','Rod',15,'INR','L');

CREATE TABLE BOMABC(
Line_No INTEGER NOT NULL
,ParentCode VARCHAR(3) NOT NULL
,ChildCode VARCHAR(1) NOT NULL
,Qty INTEGER NOT NULL
,Unit VARCHAR(3) NOT NULL
,PRIMARY KEY(Line_No,ParentCode)
);
INSERT INTO BOMABC(Line_No,ParentCode,ChildCode,Qty,Unit) VALUES (1000,'ABC','A',1,'Pcs');
INSERT INTO BOMABC(Line_No,ParentCode,ChildCode,Qty,Unit) VALUES (2000,'ABC','B',1,'Pcs');
INSERT INTO BOMABC(Line_No,ParentCode,ChildCode,Qty,Unit) VALUES (3000,'ABC','C',1,'Pcs');

I am using following query to get the output data

Declare @Euro as int, @Dollar as Int

set @Euro=81
set @Dollar=71;

with sale (Item,Cost,Sale)
as
(select * from SalesABC),

BOM (LineNum, ParentCode,ChildCode,Qty, Unit)
as
(select * from BOMABC),

Purchase (RawMaterial, Descrip, PurPrice,Currency,VendorName)
as
(select * from PurchaseABC)

Select sale.,BOM.ChildCode
, (CASE WHEN Purchase.Currency = 'Euro' THEN ROUND(Purchase.PurPrice@Euro,0)
WHEN Purchase.Currency = '$' THEN ROUND(Purchase.PurPrice*@Dollar,0)
WHEN Purchase.Currency = 'INR' THEN ROUND(Purchase.PurPrice,0)
eLSE 0 END) AS MasterCost
--, Purchase.PurPrice
, Purchase.VendorName
, sale.Sale-sale.Cost as 'Margin(Rs.)'
from sale
left join BOM on sale.Item = BOM.ParentCode
left join Purchase on BOM.ChildCode = Purchase.RawMaterial

I am getting following output using the above query

+-------+------+------+--------------+-------------+--------+--------------+
| Code | Cost | Sale | Raw Material | Master Cost | Vendor | Margin (Rs.) |
+-------+------+------+--------------+-------------+--------+--------------+
| ABC | 70 | 100 | A | 40 | S | 30 |
| ABC | 70 | 100 | B | 20 | C | 30 |
| ABC | 70 | 100 | C | 15 | L | 30 |
+-------+------+------+--------------+-------------+--------+--------------+

Actually the output needed is as follows:

+------+------+------+-----+-------------+--------+--------------+-----------------+-------------------+
| Code | Cost | Sale | R M | Master Cost | Vendor | Margin (Rs.) | Prop Margin (%) | Prop Margin (Rs.) |
+------+------+------+-----+-------------+--------+--------------+-----------------+-------------------+
| ABC | 70 | 100 | A | 40 | S | 30 | 53% | 16 |
| ABC | 70 | 100 | B | 20 | C | 30 | 27% | 8 |
| ABC | 70 | 100 | C | 15 | L | 30 | 20% | 6 |
| | | | | 75 | | | 100% | |
+------+------+------+-----+-------------+--------+--------------+-----------------+-------------------+
Proportionate Margin (%) = (Master Cost / Total Master Cost)

For eg. In first record, Proportionate Margin (%) = (40/75)*100

Proportionate Margin (Rs.) = Margin (Rs.) * Proportionate Margin (%)
For eg. In first record, Proportionate Margin (Rs.) = 30 * 53% = 15.9 Rs.

The biggest problem I am facing is how to do totalling of Master Cost itemwise i.e. there are 1000’s of items (codes) for which totaling is needed itemwise. The no. of rows in bom is also not fixed.
Multiple items data will look like a follows:-

+------+------+------+--------------+-------------+--------+--------------+--------------------------+----------------------------+
| Code | Cost | Sale | Raw Material | Master Cost | Vendor | Margin (Rs.) | Proportionate Margin (%) | Proportionate Margin (Rs.) |
+------+------+------+--------------+-------------+--------+--------------+--------------------------+----------------------------+
| ABC | 70 | 100 | A | 40 | S | 30 | 53% | 16 |
| ABC | 70 | 100 | B | 20 | C | 30 | 27% | 8 |
| ABC | 70 | 100 | C | 15 | L | 30 | 20% | 6 |
| XYZ | 140 | 200 | X | 80 | S | 60 | 53% | 32 |
| XYZ | 140 | 200 | Y | 40 | C | 60 | 27% | 16 |
| XYZ | 140 | 200 | Z | 30 | L | 60 | 20% | 12 |
+------+------+------+--------------+-------------+--------+--------------+--------------------------+----------------------------+
Can anybody extended your help to resolve the above issue.

harishgg1 · April 23, 2020, 11:13am

hi

hope this helps

DECLARE
@Euro AS INT
, @Dollar AS INT;

SET @Euro = 81;
SET @Dollar = 71
;
WITH sale
(Item, Cost, Sale)
AS (
SELECT * FROM #SalesABC
)
, BOM (LineNum, ParentCode, ChildCode, Qty, Unit)
AS (
SELECT * FROM #BOMABC
)
, Purchase (RawMaterial, Descrip, PurPrice, Currency, VendorName)
AS (
SELECT * FROM #PurchaseABC
)
, cte_fin
AS (
SELECT
sale.Item
, BOM.ChildCode
, (CASE WHEN
Purchase.Currency = 'Euro' THEN ROUND(Purchase.PurPrice * @Euro, 0)
WHEN
Purchase.Currency = '$' THEN ROUND(Purchase.PurPrice * @Dollar, 0)
WHEN
Purchase.Currency = 'INR' THEN ROUND(Purchase.PurPrice, 0)
ELSE 0
END) AS MasterCost
--, Purchase.PurPrice
, Purchase.VendorName
, sale.Sale - sale.Cost AS 'Margin(Rs.)'
FROM
sale
LEFT JOIN BOM
ON sale.Item = BOM.ParentCode
LEFT JOIN Purchase
ON BOM.ChildCode = Purchase.RawMaterial
)
SELECT
a.*
, CAST(((MasterCost / b.summc) * 100) AS INT) AS 'Prop Margin (%)'
, CAST([Margin(Rs.)] * (MasterCost * 1.0 / b.summc * 1.0) AS INT) AS 'Prop Margin (Rs.)'
FROM
cte_fin a
JOIN ( SELECT
sale.Item, SUM(MasterCost) AS summc
FROM
cte_fin) b ON a.Item = b.Item
GO

mike01 · April 23, 2020, 1:24pm

Seems like a CTE is excessive to rename columns.

Select Item, ChildCode, MasterCost, VendorName, [Margin(Rs.)], 
	   Cast(( ( mastercost / summc ) * 100 ) AS INT) AS 'Prop Margin (%)', 
       Cast([margin(rs.)] * ( mastercost * 1.0 / summc * 1.0 ) AS INT) AS 'Prop Margin (Rs.)' 
  from (
SELECT sale.Code as item, 
        bom.childcode, 
        ( CASE WHEN purchase.currency = 'Euro' THEN 
					Round(purchase.Pur_Price *  @Euro, 0) 
				WHEN purchase.currency = '$' THEN 
					Round(purchase.Pur_Price * @Dollar, 0) 
                WHEN purchase.currency = 'INR' THEN 
					Round(purchase.Pur_Price, 0) 
                ELSE 0 
            END )               AS MasterCost , 
        purchase.vendor as VendorName, 
        sale.sale_Amount - sale.cost_Amount AS 'Margin(Rs.)' 
		,sum( CASE WHEN purchase.currency = 'Euro' THEN 
					Round(purchase.Pur_Price * 81, 0) 
				WHEN purchase.currency = '$' THEN 
					Round(purchase.Pur_Price * 71, 0) 
                WHEN purchase.currency = 'INR' THEN 
					Round(purchase.Pur_Price, 0) 
                ELSE 0 
            END )  over (partition by sale.code) as Summc
    FROM  #SalesABC as sale 
        LEFT JOIN #BOMABC as bom 
                ON sale.Code = bom.parentcode 
        LEFT JOIN #PurchaseABC as purchase 
                ON bom.childcode = purchase.RawCode ) v

mpapreja · April 23, 2020, 4:00pm

Thanks Harish ok_hand:for your quick, prompt and accurate reply. The results are exactly as desired. But as a beginner i would like to understand few minute details like

Why the # symbol is used before the name of tables. I know there must be some logical reason behind that.
in calculating "Prop. Margin (Rs.)" i.e. CAST([Margin(Rs.)] * (MasterCost * 1.0 / b.summc * 1.0) AS INT) why have multiplied denominator and numerator by 1.0
What is the significance of Go in the end.
is there any documentation / link available on the net to learn CTE command in detail.

Once again thank you for taking pain.

mpapreja · April 23, 2020, 4:07pm

Dear Mike,
Thanks for taking pain in responding to my problem. Is the query giving the desired results at your end since i am getting errors at my end (may be i doing mistake at my end due lack of knowledge).

harishgg1 · April 23, 2020, 4:36pm

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Why the # symbol is used before the name of tables. I know there must be some logical reason behind that

Temporary Tables ... use the # symbol
they are not permanent .. they are created .. used ..and destroyed
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

in calculating "Prop. Margin (Rs.)" i.e. CAST([Margin(Rs.)] * (MasterCost * 1.0 / b.summc * 1.0) AS INT) why have multiplied denominator and numerator by 1.0

when you multiply by 1.0 .. decimal part shows up
example .... 1/3 will show 0
..... but 1/3.0 will show 0.33333
++++++++++++++++++++++++++++++++++++++++++++++++++++++

What is the significance of Go in the end !!

GO is a batch terminator .. in TSQL
meaning if you want to seperate ...
1
2
3

1
2
GO
3
mean 1 and 2 are 1 batch and 3 is seperate
++++++++++++++++++++++++++++++++++++++++++++++++++++

is there any documentation / link available on the net to learn CTE command in detail.

there are lots of videos .. articles ..
google search

but the whole thing as a beginner .. its very painful
as the smallest things wont make any sense and will take hours to understand !!

small mistakes happen .. the code does not work ..dont know where the mistake is
and takes hours and hours to find out

if you can understand articles and videos .. and .. do a lot of practice
it becomes easy ..
Or
you can takes NOTES .. and make it really FAST

+++++++++++++++++++++++++++++++++++++++++++++++++++++

yosiasz · April 23, 2020, 5:37pm

We could try to attempt to read your mind but might help if you provided the error you are getting.

harishgg1 · April 23, 2020, 5:44pm

Hi

Problem is tsql is very cryptic
Smallest things like typing abc instead of abd
Will give error

How to do it .. when you get errors

Break it up into small small pieces of code

put it together piece by piece
Is one idea

Google search
How to fix errors in t SQL

Select 1
Select 2
Select3

See select 1 working
Then add select 2 working
Then add select 3 working

If I can remote desk top to your machine
I could show you a few things

mpapreja · April 24, 2020, 6:50am

Dear Harish
I am really thankful to you for sharing few extremely helpful tips. My original querry is quite complicated and i really like the idea of taking my system remotely by you to share few more important tips on how to fix errors.

The solution suggested by you works perfectly fine if i consider only one item but when i consider multiple items there is an issue in calculation of 'Prop Margin (Rs.) since the Margin(Rs.) is itemwise and it changes with change in Item (Code). The issue is Margin(Rs.) is itemwise whereas the MasterCost is in totality of two items which is giving incorrect 'Prop Margin (Rs.)'. The desired can be any of the two options as follows:

+-----------------------------+------+------+--------------+-------------+------------------+--------+-----------------------+--------------------------+----------------------------+
| ITEMWISE MARGIN CALCULATION | | | | | | | | | |
+-----------------------------+------+------+--------------+-------------+------------------+--------+-----------------------+--------------------------+----------------------------+
| Code | Cost | Sale | Raw Material | Master Cost | summc (Itemwise) | Vendor | Margin (Rs.) itemwise | Proportionate Margin (%) | Proportionate Margin (Rs.) |
| ABC | 70 | 100 | A | 40 | 75 | S | 30 | 53% | 16 |
| ABC | 70 | 100 | B | 20 | 75 | C | 30 | 27% | 8 |
| ABC | 70 | 100 | C | 15 | 75 | L | 30 | 20% | 6 |
| XYZ | 140 | 200 | X | 80 | 150 | S | 60 | 53% | 32 |

the other optional output is as follows:-

+--------------------------+------+------+--------------+-------------+---------------+--------+--------------+--------------------------+----------------------------+
| GROSS MARGIN CALCULATION | | | | | | | | | |
+--------------------------+------+------+--------------+-------------+---------------+--------+--------------+--------------------------+----------------------------+
| Code | Cost | Sale | Raw Material | Master Cost | summc (GROSS) | Vendor | Margin (Rs.) | Proportionate Margin (%) | Proportionate Margin (Rs.) |
| ABC | 70 | 100 | A | 40 | 225 | S | 90 | 53% | 48 |
| ABC | 70 | 100 | B | 20 | 225 | C | 90 | 27% | 24 |
| ABC | 70 | 100 | C | 15 | 225 | L | 90 | 20% | 18 |
| XYZ | 140 | 200 | X | 80 | 225 | S | 90 | 53% | 48 |

if would rather like to have solution to both the above option as sometimes i need to calculated itemswise and sometimes gross 'Prop Margin (Rs.)'

INSERT INTO #SalesABC(Code,Cost_Amount,Sale_Amount) VALUES ('XYZ',140,200);

INSERT INTO #PurchaseABC(RawCode,Description,Pur_Price,Currency,Vendor) VALUES ('X','Kit',0.99,'Euro','S');

INSERT INTO #PurchaseABC(RawCode,Description,Pur_Price,Currency,Vendor) VALUES ('Y','Barrel',0.564,'$','C');

INSERT INTO #PurchaseABC(RawCode,Description,Pur_Price,Currency,Vendor) VALUES ('Z','Rod',30,'INR','L');

INSERT INTO #BOMABC(Line_No,ParentCode,ChildCode,Qty,Unit) VALUES (1000,'XYZ','X',1,'Pcs');

INSERT INTO #BOMABC(Line_No,ParentCode,ChildCode,Qty,Unit) VALUES (2000,'XYZ','Y',1,'Pcs');

INSERT INTO #BOMABC(Line_No,ParentCode,ChildCode,Qty,Unit) VALUES (3000,'XYZ','Z',1,'Pcs');

if you need examine my actual query and i can show it remotely (remote desktop option) if its convenient for you.

Thanks again for your kind help.

harishgg1 · April 24, 2020, 7:18am

hi

please try this !!!

DECLARE
@Euro AS INT
, @Dollar AS INT;

SET @Euro = 81;
SET @Dollar = 71
;
WITH sale
(Item, Cost, Sale)
AS (
SELECT * FROM #SalesABC
)
, BOM (LineNum, ParentCode, ChildCode, Qty, Unit)
AS (
SELECT * FROM #BOMABC
)
, Purchase (RawMaterial, Descrip, PurPrice, Currency, VendorName)
AS (
SELECT * FROM #PurchaseABC
)
, cte_fin
AS (
SELECT
sale.Item
, BOM.ChildCode
, (CASE WHEN
Purchase.Currency = 'Euro' THEN ROUND(Purchase.PurPrice * @Euro, 0)
WHEN
Purchase.Currency = '$' THEN ROUND(Purchase.PurPrice * @Dollar, 0)
WHEN
Purchase.Currency = 'INR' THEN ROUND(Purchase.PurPrice, 0)
ELSE 0
END) AS MasterCost
--, Purchase.PurPrice
, Purchase.VendorName
, sale.Sale - sale.Cost AS 'Margin(Rs.)'
FROM
sale
LEFT JOIN BOM
ON sale.Item = BOM.ParentCode
LEFT JOIN Purchase
ON BOM.ChildCode = Purchase.RawMaterial
)
SELECT
a.*
, CAST(((MasterCost / b.summc) * 100) AS INT) AS 'Prop Margin (%)'
, CAST([Margin(Rs.)] * (MasterCost * 1.0 / b.summc * 1.0) AS INT) AS 'Prop Margin (Rs.)'
FROM
cte_fin a ,
( SELECT
SUM(MasterCost) AS summc
FROM
cte_fin) b
GO

mpapreja · April 24, 2020, 8:37am

Thanks but i am unable to figure out the diff. between this code and the previous suggested code. Upto 'Prop Margin (%)' column everything is fine. But values in last column i.e. 'Prop Margin (Rs.)' are not accurate. The total of values in last column should be 90 whether we take item wise or gross values.

sum of MasterCost = 225
'Prop Margin (%)' = Mastercost in particular Record / 225 = 17.7% (for first record)

upto above step it is fine.

For Calculating Gross Margin We need to add Margin on first item i.e. Rs. 30 & Margin on second item i.e. Rs. 60 totalling to Rs. 90/-. Margin to be considered only once for each item while totalling.
Now the above calculated 'Prop Margin (%)' is applied on Rs. 90/- to final values for each record in 'Prop Margin (Rs.)' column.

The other way round for calculating the 'Prop Margin (Rs.)' is itemwise which I feel is a better and desired way of getting the result. in this case we need we to make changes in mastercost. We need to calculate total of mastercost itemwise i.e. For the first item the total of master cost is 75 and second item is 150. Rest of the calculations will remain same.

Hope the requirement is clear now.

mike01 · April 24, 2020, 12:51pm

Your results don't match your data. You say the sum of MasterCost = 225, but that is incorrect. Based on your calculations, sum of MasterCost = 75 (40 for ChildCode A, 20 for B and 15 for C). The query I wrote above returns the correct values based on what you've given us.

harishgg1 · April 24, 2020, 2:19pm

if you want me to remote desk top to your machine .. please let me know ..

how to explain to others .. in very very simple terms !!
so that its very very easy to understand !!

is a PHD subject with lots of scientists Working on IT !!!
never ending !!

something like this .. its always a big big pain to explain to others

mpapreja · April 24, 2020, 4:02pm

you can take my system online as my query is quite complicated. Kindly confirm the time as per your convenience and software required for the above purpose. Thanks once again.

yosiasz · April 24, 2020, 4:07pm

A PHD on how to explain simple things to others so they can understand it?

harishgg1 · April 24, 2020, 4:11pm

I would rather TRY without .. Remote Desk Top ..

I dont want to put you in any MALWARE ... danger .. unknowingly

Please lets chat some where you and me are both online
at the same time .. and can exchange messages ( Large Messages )

Do you have WhatsApp ? or is there something you prefer

mpapreja · April 24, 2020, 4:15pm

i am available on whats app no. 9810927730

harishgg1 · April 24, 2020, 4:16pm

PHD .. because .. it goes into Lots of Tangents !!

Neuroscience
Brain Chemistry
Individual STYLES .. for grasping
( some See images and understand BEST .. some read text and understand BEST )
Neuron Synapses (strength of voltage ) ..
Thoughts Mapping ( a to b to c to d )
and etc etc etc etc

How about just over a CUP Of MOCHA !!

harishgg1 · April 24, 2020, 4:42pm

how to "make it simple" for others to understand !!!

mpapreja · April 24, 2020, 5:35pm

Dear Mike,
your query is giving me the desired results. Actually i was getting the errors as i didn't know that the names of the fields are case sensitive. On resolving the errors query is giving desired results item-wise. Now I need to implement same logic in my complicated query. Will try and revert to you.

Just to clarify the above query is giving results which are ITEMWISE & are fine. The another possibility is to do it on gross basis i.e. if we consider total of cost as 225 (75+150) then in that case our Margin should also be gross i.e. 90 (30+60). After calculating these 2 figures we will calculate 'Prop Margin (%)' which will be 40/225 i.e. 18% and accordingly our 'Prop Margin (Rs.)' will be calculated as 18% of Rs.90 i.e. Rs. 16. This method is useful to me for calculating proportionate gross margin of all the items which are more than 10000 in Nos. If it is still not clear i think we can ignore it. I could have explained it in a better way through images which are restricted here.

Anyway thanks for your help mike.