March 15, 2018 at 3:25 pm
Hello
I would like to thank all of you who share your site.
I want to make a first-come-first-served list of profit calculation
declare @purchased table (id int,sku int,dt date,price money,qty int)
declare @sold table (id int,sku int,dt date,price money,qty int)
insert into @purchased
values
(1,123,'2018-01-01',20.15,5)
,(2,123,'2018-05-01',17.50,3)
,(3,123,'2018-05-02',15.00 ,1)
,(4,456,'2018-06-10',60.00,7)
insert into @sold
values(1,123, '2018-01-15',30.00,1)
,(2,123,'2018-01-20',28.00,3)
,(3,123,'2018-05-10',25.00,2)
,(4,456,'2018-06-11',80.00,1)
Profit to have;
| sku | profΔ±t |
| 123 | 45,75 |
| 456 | 20 |
March 15, 2018 at 4:22 pm
If I understand the requirements correctly, here's one method:
WITH
n AS (SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))x(n)),
n6 AS (SELECT n1.N FROM n n1, n n2, n n3, n n4, n n5, n n6), --This allows for qty to be up to 10^6, likely overkill
purchases_expanded AS
(
SELECT sku,dt,minus=price,rn=ROW_NUMBER() OVER (PARTITION BY sku ORDER BY dt ASC)
FROM @purchased CROSS APPLY (SELECT TOP (qty) N FROM N6)x
),
sold_expanded AS
(
SELECT sku,dt,plus=price,rn=ROW_NUMBER() OVER (PARTITION BY sku ORDER BY dt ASC)
FROM @sold CROSS APPLY (SELECT TOP (qty) N FROM N6)x
)
SELECT p.sku, profit=SUM(plus-minus)
FROM purchases_expanded p
INNER JOIN
sold_expanded s ON p.sku=s.sku AND p.rn=s.rn
GROUP BY p.sku
OPTION (RECOMPILE);--Just added because of the table variables, so we at least have accurate cardinalities
There are likely cleverer ways of doing this (I'll mull it over a while longer), but it's late in the day and this was my first thought. π
Cheers!
March 16, 2018 at 4:08 am
Jacob Wilkins - Thursday, March 15, 2018 4:22 PMIf I understand the requirements correctly, here's one method:
WITH
n AS (SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))x(n)),
n6 AS (SELECT n1.N FROM n n1, n n2, n n3, n n4, n n5, n n6), --This allows for qty to be up to 10^6, likely overkillpurchases_expanded AS
(
SELECT sku,dt,minus=price,rn=ROW_NUMBER() OVER (PARTITION BY sku ORDER BY dt ASC)
FROM @purchased CROSS APPLY (SELECT TOP (qty) N FROM N6)x
),
sold_expanded AS
(
SELECT sku,dt,plus=price,rn=ROW_NUMBER() OVER (PARTITION BY sku ORDER BY dt ASC)
FROM @sold CROSS APPLY (SELECT TOP (qty) N FROM N6)x
)SELECT p.sku, profit=SUM(plus-minus)
FROM purchases_expanded p
INNER JOIN
sold_expanded s ON p.sku=s.sku AND p.rn=s.rn
GROUP BY p.sku
OPTION (RECOMPILE);--Just added because of the table variables, so we at least have accurate cardinalitiesThere are likely cleverer ways of doing this (I'll mull it over a while longer), but it's late in the day and this was my first thought. π
Cheers!
I didn't understand how he gets profit =45.75 for SKU =123 . Can you kindly explain me?
Because for ID=3 and SKU=123 purchased quantity is 1 but sold quantity is 2.
Regards,
Saravanan
Saravanan
March 16, 2018 at 1:22 pm
Thank you for your interest. π
We can make the logic of this query Cursor.
Thank you
March 16, 2018 at 1:49 pm
doganmery2 - Friday, March 16, 2018 1:22 PMThank you for your interest. πWe can make the logic of this query Cursor.
Thank you
Are you asking if the code could be written with a cursor? Probably, but then that begs the question, why would you want to?
March 16, 2018 at 2:03 pm
Thanks Lynn Pettis
I do not know sql very well πππ
Could you make the question a bit simpler.
March 16, 2018 at 2:20 pm
doganmery2 - Friday, March 16, 2018 2:03 PMThanks Lynn PettisI do not know sql very well πππ
Could you make the question a bit simpler.
Why would you want this written as a cursor based routine?
March 16, 2018 at 2:26 pm
You are more knowledgeable. I would be glad if you could give me another alternative solution in this regard.
I do not write very well in English, I use translation. π
March 16, 2018 at 2:30 pm
doganmery2 - Friday, March 16, 2018 2:26 PMYou are more knowledgeable. I would be glad if you could give me another alternative solution in this regard.I do not write very well in English, I use translation. π
What don't you get? You were given a set-based solution (that we don't know if it meets your requirements since you haven't said anything). You asked if it could be written as a curor-based solution. I asked you why you would want a cursor-based solution?
March 16, 2018 at 2:36 pm
Reason for request as cursor User defined function
I will use it in.
If there is a solution in the function that can be solved outside the cursor, please guide me.
I am waiting for your help in this regard.
March 16, 2018 at 3:14 pm
Okay, first, after taking a closer look at what you asked and what was provided, that has been met.
You don't need, or want, a cursor-based solution. You were provided with a set-based solution that will perform and scale better than a cursor-based solution.
What you really need is a better set of requirements as to what you are looking for here. This solution can be converted to an itvf (in-line table valued function) that can be used in the FROM clause and perform better than scalar function, which is what you seem to be eluding to with your latter comments.
March 16, 2018 at 3:37 pm
Jacob Wilkins - Thursday, March 15, 2018 4:22 PMIf I understand the requirements correctly, here's one method:
WITH
n AS (SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))x(n)),
n6 AS (SELECT n1.N FROM n n1, n n2, n n3, n n4, n n5, n n6), --This allows for qty to be up to 10^6, likely overkillpurchases_expanded AS
(
SELECT sku,dt,minus=price,rn=ROW_NUMBER() OVER (PARTITION BY sku ORDER BY dt ASC)
FROM @purchased CROSS APPLY (SELECT TOP (qty) N FROM N6)x
),
sold_expanded AS
(
SELECT sku,dt,plus=price,rn=ROW_NUMBER() OVER (PARTITION BY sku ORDER BY dt ASC)
FROM @sold CROSS APPLY (SELECT TOP (qty) N FROM N6)x
)SELECT p.sku, profit=SUM(plus-minus)
FROM purchases_expanded p
INNER JOIN
sold_expanded s ON p.sku=s.sku AND p.rn=s.rn
GROUP BY p.sku
OPTION (RECOMPILE);--Just added because of the table variables, so we at least have accurate cardinalitiesThere are likely cleverer ways of doing this (I'll mull it over a while longer), but it's late in the day and this was my first thought. π
Cheers!
I would like to thank all of you who share your site.
I want to make a first-come-first-served list of profit calculation
declare @purchased table (id int,sku int,dt date,price money,qty int)
declare @sold table (id int,sku int,dt date,price money,qty int)
insert into @purchased
values
(1,123,'2018-01-01',20.15,5)
,(2,123,'2018-05-01',17.50,3)
,(3,123,'2018-05-02',15.00 ,1)
,(4,456,'2018-06-10',60.00,7)
insert into @sold
values(1,123, '2018-01-15',30.00,1)
,(2,123,'2018-01-20',28.00,3)
,(3,123,'2018-05-10',25.00,2)
,(4,456,'2018-06-11',80.00,1)
Profit to have;
In SQL, we’ve got a couple of decades behind you, you’ll find that the real work is done in the DDL and not in the DML. If your schema is properly designed. The problem tends to solve itself, with the simplest of code.
Your design currently has a common error called “attribute splitting†which takes the values the given attribute and makes them into either separate columns or in a separate tables, splitting them from the attribute. That’s what what you have and how to rewrite it:
CREATE TABLE Inventory
(sku CHAR(5) NOT NULL,
transaction_type CHAR(1) NOT NULL
CHECK(transaction_type IN (‘P’, ‘S’)),
transaction_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
transaction_qty INTEGER NOT NULL
CHECK (transaction_qty <> 0),
unit_price DECIMAL (12, 2) NOT NULL,
PRIMARY KEY (sku, transaction_type, transaction_date));
Let’s look at this DDL line by line. The “SKU†is an industry-standard term for stock keeping unit, and probably doesn’t need to be qualified. But it does need to be represented as a string and not an integer! This is the basic thing you should of learned the first week of your data modeling class; you do not do any kind of math on identifiers! What do you think the square root of your credit card number means?
The type of inventory transaction is where you did your split in your original design. We need to correct that and bring it back into the inventory.
The transaction did date of the sale or purchase can be defaulted to the current timestamp. As a general rule in SQL you want to use as many defaults in check constraints as possible. It doesn’t just save the poor application programmer extra work and the possibility of error, but it actually passes information on to the optimizer and makes the queries run faster.
Transaction quantity cannot be zero because it would make any sense to do nothing to an inventory. However, quantities can be added or subtracted.
I am assuming, based on absolutely nothing you posted, that your “price†actually refers to a unit price for each SKU. I shouldn’t have to try to figure it out from the data, so I’m not even going to try. Never, never, never use the old Sybase money data types; they literally do not work. They have errors in multiplication and division that will screw you to the wall and send you to jail (had to testify in a court case about errors in the accounting system that use that).
Finally, the first week of your RDBMS class. You should of learned that every table has to have a key, but by using local declared semi tables (that’s a term I made up, it’s where newbies use weaknesses in SQL, like the declare table, etc. to mimic their old punchcards and magnetic tape files instead of actually creating a schema.
I see you grew up with punch cards and old file systems from what you did post. No RDBMS programmer would ever do anything like a row number two mimic the position of a physical record within a magnetic tape or deck of punch cards. This is why we have keys. But the real giveaway is that you put the leading, on each line of your posting! Wow! That’s just what I used to do in the 1960s when working with punch cards. This let us rearrange the deck and not have to replace them. Then you crammed all the data on each line (punch card) as tight as you could because a punch card has only 80 columns and you can’t waste space to make code readable.
No competent programmer has done that since about 1975, after it was possible to reformat your code by pushing a “pretty printer†button in your development tool.
INSERT INTO Inventory
VALUES
(‘00123’, ‘P’, '2018-01-01', 20.15, 5)
(‘00123’, ‘P’, '2018-05-01', 17.50, 3)
(‘00123’, ‘P’, '2018-05-02', 15.00, 1)
(‘00456’, ‘P’, '2018-06-10', 60.00, 7)
('00123', ‘S’, '2018-01-15', 30.00, 1)
('00123', ‘S’, '2018-01-20', 28.00, 3)
('00123', ‘S’, '2018-05-10', 25.00, 2)
(‘00456’, ‘S’, '2018-06-11', 80.00, 1)
but wait! Look at this data. The S and the P flags can be replaced with plus and minus signs on the quantity. Essentially, your two tables are the same flaw that was made in European bookkeeping during the Renaissance. They had no concept of negative numbers. Let’s go ahead and change that. Drop the transaction type column
INSERT INTO Inventory
VALUES
('00123', '2018-01-15', 30.00, -1),
('00123', '2018-01-20', 28.00, -3),
('00123', '2018-05-10', 25.00, -2),
(‘00123’, '2018-01-01', 20.15, 5)
(‘00123’, '2018-05-01', 17.50, 3)
(‘00123’, '2018-05-02', 15.00, 1)
(‘00456’, '2018-06-10', 60.00, 7)
(‘00456’, '2018-06-11', 80.00, -1);
At this point you should be able to write a fairly simple windowed query that will give you your running totals.
Let’s look at this DDL line by line. The “SKU†is an industry-standard term for stock keeping unit, and probably doesn’t need to be qualified. But it does need to be represented as a string and not an integer! This is the basic thing you should of learned the first week of your data modeling class; you do not do any kind of math on identifiers! What do you think the square root of your credit card number means?The type of inventory transaction is where you did your split in your original design. We need to correct that and bring it back into the inventory.The transaction did date of the sale or purchase can be defaulted to the current timestamp. As a general rule in SQL you want to use as many defaults in check constraints as possible. It doesn’t just save the poor application programmer extra work and the possibility of error, but it actually passes information on to the optimizer and makes the queries run faster. Transaction quantity cannot be zero because it would make any sense to do nothing to an inventory. However, quantities can be added or subtracted.I am assuming, based on absolutely nothing you posted, that your “price†actually refers to a unit price for each SKU. I shouldn’t have to try to figure it out from the data, so I’m not even going to try. Never, never, never use the old Sybase money data types; they literally do not work. They have errors in multiplication and division that will screw you to the wall and send you to jail (had to testify in a court case about errors in the accounting system that use that).Finally, the first week of your RDBMS class. You should of learned that every table has to have a key, but by using local declared semi tables (that’s a term I made up, it’s where newbies use weaknesses in SQL, like the declare table, etc. to mimic their old punchcards and magnetic tape files instead of actually creating a schema.I see you grew up with punch cards and old file systems from what you did post. No RDBMS programmer would ever do anything like a row number two mimic the position of a physical record within a magnetic tape or deck of punch cards. This is why we have keys. But the real giveaway is that you put the leading, on each line of your posting! Wow! That’s just what I used to do in the 1960s when working with punch cards. This let us rearrange the deck and not have to replace them. Then you crammed all the data on each line (punch card) as tight as you could because a punch card has only 80 columns and you can’t waste space to make code readable.No competent programmer has done that since about 1975, after it was possible to reformat your code by pushing a “pretty printer†button in your development tool. INSERT INTO InventoryVALUES(‘00123’, ‘P’, '2018-01-01', 20.15, 5)(‘00123’, ‘P’, '2018-05-01', 17.50, 3)(‘00123’, ‘P’, '2018-05-02', 15.00, 1)(‘00456’, ‘P’, '2018-06-10', 60.00, 7)('00123', ‘S’, '2018-01-15', 30.00, 1)('00123', ‘S’, '2018-01-20', 28.00, 3)('00123', ‘S’, '2018-05-10', 25.00, 2)(‘00456’, ‘S’, '2018-06-11', 80.00, 1)but wait! Look at this data. The S and the P flags can be replaced with plus and minus signs on the quantity. Essentially, your two tables are the same flaw that was made in European bookkeeping during the Renaissance. They had no concept of negative numbers. Let’s go ahead and change that. Drop the transaction type columnINSERT INTO InventoryVALUES('00123', '2018-01-15', 30.00, -1),('00123', '2018-01-20', 28.00, -3),('00123', '2018-05-10', 25.00, -2),(‘00123’, '2018-01-01', 20.15, 5)(‘00123’, '2018-05-01', 17.50, 3)(‘00123’, '2018-05-02', 15.00, 1)(‘00456’, '2018-06-10', 60.00, 7)(‘00456’, '2018-06-11', 80.00, -1);At this point you should be able to write a fairly simple windowed query that will give you your running totals.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 16, 2018 at 10:18 pm
Hi,
Thank you for your precious time.
I work a little bit for the solution π
March 21, 2018 at 10:06 am
Hi,
Check the solution.
declare @purchased table (id int,sku int,dt date,price money,qty int)
declare @sold table (id int,sku int,dt date,price money,qty int)
insert into @purchased
values( 1 , 123 , '2013-01-01 12:25' , 20.15 , 5)
,(2 , 123 , '2013-05-01 15:45' , 17.50 , 3)
,(3 , 123 , '2013-05-02 12:00' , 15.00 , 1)
,(4 , 456 , '2013-06-10 16:00' , 60.00 , 7)
insert into @sold
values(1 , 123 , '2013-01-15 11:00' , 30.00 , 1)
,(2 , 123 , '2013-01-20 14:00' , 28.00 , 3)
,(3 , 123 , '2013-05-10 15:00' , 25.00 , 2)
,(4 , 456 , '2013-06-11 12:00' , 80.00 , 1)
; with cte_sold as (select sku,sum(qty) as qty, SUM(qty*price) as total_value
from @sold
group by sku
)
,cte_purchased as (select id,sku,price,qty
from @purchased
union all select id,sku,price,qty-1 as qty
from cte_purchased
where qty>1
)
,cte_purchased_ordened as(select ROW_NUMBER() over (partition by sku order by id,qty) as buy_order
,sku
,price
,1 as qty
from cte_purchased
)
select P.sku
,S.total_value - SUM(case when P.buy_order <= S.qty then P.price else 0 end) as margin
from cte_purchased_ordened P
left outer join cte_sold S
on S.sku = P.sku
group by P.sku,S.total_value,S.qty
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply