SQL Question: How to do an "Easy" Substraction

[Tec-Nico]

Obviously is not as "easy" for me because I can't find a way to do it. But I hope it is for you.

Here is my problem:

I have 2 fields that I would like to substract. The problem is that the substraction is not very "natural".

I mean, this will be an example resulting set:

JoinId____|__QuantityA_____|__QuantityB
1________|______10_______|______40
1________|______32_______|______40
1________|______5________|______40
2________|______10_______|______15
2________|______10_______|______15

These two quantities are taken from different tables, lets say TableA and TableB... They are joined by the id I show (It is not a primary key)

What I would like to do is have something like this:

JoinId___|__QuantityA_____|__QuantityB___|___Result
1_______|______10_______|______40_____|_____0_(We substracted 10 from 40)
1_______|______32_______|______40_____|_____2_(We substract 32 from remaining 30)
1_______|______5________|______40_____|_____5_(We substract 5 from remaining 0)
2_______|______10_______|______15_____|_____0_(We substracted 10 from 15)
2_______|______10_______|______15_____|_____5_(We substract 10 from remaining 5)

It looks complicated but what I want to do is a substraction with every member of the same JoinId. The remaining would be substracted to the next one.

Example:

JoinId___|__QuantityA__|__QuantityB
1_______|______10____|______40____(40 - 10 = 30 so we will substract below)
1_______|______32____|______30____(30 - 32 = -2 so we won't substract below)
1_______|______5_____|______0_____

2_______|______10____|______15____(15 - 10 = 5 so we will substract below)
2_______|______10____|______5_____(5 - 10 = -5 so we won't substract below)


And so the results of this would be like this:

JoinId___|__QuantityA__|__QuantityB__|_Result
1_______|______10____|______40____|___0
1_______|______32____|______30____|___2
1_______|______5_____|______0_____|___5

2_______|______10____|______15____|___0
2_______|______10____|______5_____|___5


I hope I am explaining myself... Please let me know if I didn't. I think (I am not sure) it could be done using sub-queries and GROUP BY clauses... But I am not quite sure how to do it. Any help would be appreciated!

Thanks in advance!

[westconn1]

hi

as i haven't got the table i just put yor data in an array, then put the results in another array. it works with the examples you supplied.

Private Sub Command1_Click()
 
Dim arrjoin As Variant
Dim arrresult
arrresult = Array()
arrjoin = Array("1 10 40", "1 32 40", "1 5 40", "2 10 15", "2 10 15")
For i = 0 To UBound(arrjoin)
iid = Int(Left(arrjoin(i), 1))
ia = Int(Mid(arrjoin(i), 3, 2))
ib = Int(Right(arrjoin(i), 2))
ReDim Preserve arrresult(iid)
 
 
        If IsEmpty(arrresult(iid)) Then arrresult(iid) = ib
        If arrresult(iid) > 0 Then arrresult(iid) = arrresult(iid) - ia
             
                    
Next
For i = 0 To UBound(arrresult)
Debug.Print i & "  " & arrresult(i)
Next
 
End Sub

rgds peter

[Tec-Nico]

Thank you, Peter. I was thinking of a similar solution to the one you provided... But I was wondering if this was possible to do with SQL.

This is mainly because I have a grid that will take the recordset and display it. (I could change that, but it would require more work) and because I have to also query it again but for the last month... Meaning I might use a lot of memory filling this table, and I would like to avoid it using SQL.

But as I said, thanks for your reply, Peter. If you wish information about my tables I am willing to give it

[westconn1]

i am NO expert with sql, but i can't see any reason why it can't be done, within an sql query.

if you send me more info i will have a look, but no promises.

peter

[jhermiz]

Thank you, Peter. I was thinking of a similar solution to the one you provided... But I was wondering if this was possible to do with SQL.

This is mainly because I have a grid that will take the recordset and display it. (I could change that, but it would require more work) and because I have to also query it again but for the last month... Meaning I might use a lot of memory filling this table, and I would like to avoid it using SQL.

But as I said, thanks for your reply, Peter. If you wish information about my tables I am willing to give it

Because column b is so dynamic there is no SQL solution...we can provide code to mimic this functionality but it would involve a lot of t-sql...

That logic is rather strange but i guess it fits your business needs ????
You could perform the math back on the front end and update results using ADO.net or ADO on the actual data.

As far as a single select / update / insert query not possible...

[Tec-Nico]

Thanks for your replies!

Peter, I will add more info about the tables.
jhermiz, I would like to know what you think after I give you a general view of what I am trying to achieve.

The main issue is that this is for a program that is already shipped and working. The accountant wanted a change, so his life would be easier when doing his work.

Now, since this program was intended for a little business (And we also asked what they wanted and needed) it uses an MDB file as its DataBase. This means I cannot make a stored procedure the same way I would in SQL Server. It also means they won't give up on this database.

Now... We have a table of Sales that has the following fields:

Sales
ProductId_|_Amount_|_Date_|

Now, I have a table of Products which is managing the amount they have of a product with a certain id. The problem is that the accountant says that the price of this product could vary through the month (Yet I was told by the project sponsor that this is not very likely to happen)

So I added this table:

Purchases
ProductId_|_Price_|_Date_|_Amount_|

In this table the primary key would be defined by the three first fields. The detail would be that any purchase made the same month with the same Price would just update the Amount and not create a new entry (I can handle that)

Lets see an example:

Month: 11

(Purchases)
ProductId = 50
Amount_|_Price_|
10_____|__90__|
15_____|__95__|
15_____|__100_|
(Sales of the Product in that month: 40
SELECT SUM(Amount) FROM Sales WHERE ProductId = 50 AND MONTH(Date) = 11

Result: 40)

(Purchases)
ProductId = 51
Amount_|_Price_|
20_____|__60__|
10_____|__70__|
5______|__75__|
(Sales of the Product in that month: 15
SELECT SUM(Amount) FROM Sales WHERE ProductId = 51 AND MONTH(Date) = 11

Result: 15)

Month 12: (Current Month)
(Purchases)
ProductId = 50
Amount_|_Price_|
10_____|__100_| \ The new amount's price would be the last one (100)
20_____|__105_| /
(In this example there would be no leftovers to work with)

(Purchases)
ProductId = 51
Amount_|_Price_|
5______|__60__| \
10_____|__70__| } Left-Overs from the previous month
5______|__75__| /

Well, this shows how the LeftOvers are handed... The grid I would show is the following: (Those with 0 LeftOvers won't be shown)
ProductId_|_LeftOvers_|_Price_|_Purchases_|_Sales_|
51_______|___5______|_60___|____0_____|__0___|
51_______|___10_____|_70___|____0_____|__0___|
51_______|___5______|_75___|____0_____|__0___|

If they sell 4 items of that product the table would be as follows:
ProductId_|_LeftOvers_|_Price_|_Purchases_|_Sales_|
51_______|___5______|_60___|____0_____|__4___|
51_______|___10_____|_70___|____0_____|__0___|
51_______|___5______|_75___|____0_____|__0___|

If they buy 12 items of that product from their retailer:
ProductId_|_LeftOvers_|_Price_|_Purchases_|_Sales_|
51_______|___5______|_60___|____0_____|__4___|
51_______|___10_____|_70___|____0_____|__0___|
51_______|___5______|_75___|____12____|__0___|

If they sell 7 items more of the product:
ProductId_|_LeftOvers_|_Price_|_Purchases_|_Sales_|
51_______|___5______|_60___|____0_____|__5___|
51_______|___10_____|_70___|____0_____|__6___|
51_______|___5______|_75___|____12____|__0___|

If they sell 6 items more:
ProductId_|_LeftOvers_|_Price_|_Purchases_|_Sales_|
51_______|___5______|_60___|____0_____|__5___|
51_______|___10_____|_70___|____0_____|__10__|
51_______|___5______|_75___|____12____|__2___|

As I see it, it is a process of two steps... One is to find the LeftOvers of each item and the second one is to find the purchases and sales of each of those LeftOvers and the new items (Here would be a new item example):
ProductId_|_LeftOvers_|_Price_|_Purchases_|_Sales_|
51_______|___5______|_60___|____0_____|__5___|
51_______|___10_____|_70___|____0_____|__10__|
51_______|___5______|_75___|____12____|__2___|
51_______|___0______|_80___|____13____|__0___|

(Please notice that the new item would be shown even if it had 0 leftOvers... I know it is a mess)

As you can see, I don't need to make any update of the data, I just need to query through what I have and show it on a grid... The difficult part is to get what I need in the query... I just want to know if there is a SQL way to get this (And should be a simple one because it is on Access ) so the program won't delay that much making the calculus of every single product.

Thank you for helping me to tackle down this problem.

[Tec-Nico]

Er... Any ideas?

I know the example looks big, horrible, etc... But just give it a read, please?

[westconn1]

sorry, missed your previous post, but will look through it shortly

p.

[dglienna]

I'm not the greatest at SQL, but I don't think that it is possible to do it like that. Send them another app that will query the table, and write another table, which can be used as a basis for next month, etc.

If there was a QOH field, it would be easier.

[westconn1]

without setting the whole thing up as a new project and actually writing the code to test i am unable to help you.

if i was trying to do that on an access database, which is what i have used in the past, with dao, i would be trying to do it creating a new recordset from the current one.

if you want to send me an example of your project with some dummy data, i will spend a bit of time on it to see if i can make it work.

p.