[10$ bounty] SQL Query

[OmegaStarScream]

Posting this on behalf of a friend, I need someone to edit this following query, so it returns today's profit, or preferably profits made in a chosen timeframe.

The query is currently calculating the overall profits (First in, first out) and it's working perfectly fine. If you have any questions, feel free to PM me or post here.

[1715123932]

1715123932

[1715123932]

1715123932

[PX-Z]

I'm not really sure if this is what your friend is trying to do, got it for several tries using my smartphone lol.

Here is the one^[1] sorted by sale_date
This one^[2] is sorting with the purchase_date
And this one^[3] is for both sales and purchase

[1] sorting with sale_date
[2] sort with purchase date
[3] sort both purchase_date and sale_date

[OmegaStarScream]

I'm not really sure if this is what your friend is trying to do, got it for several tries using my smartphone lol.

-snip-

I appreciate you taking the time to look into this but that's not going to work, already tried it. The purchase date in this case is irrelevant, but if you set a WHERE statement for the sales, you'll get inaccurate data because you'll be subtracting today's sales (or x timeframe) from all purchases.

Filtering the date for both sales and purchases will also not work, because a user could've bought something on the 1st of January for example, and just decided to sell it today.

[NeuroticFish]

Filtering the date for both sales and purchases will also not work, because a user could've bought something on the 1st of January for example, and just decided to sell it today.

I expect that at every sale you have to match with some purchases you're selling, usually the oldest (but it's up to the user/your friend to choose; you need a clear rule here, else, if choosing randomly you can even get different result on each run), so, in 2013-06-11 (based on that data and the "oldest" logic) you should have nothing left from 1/1 to sell.
Unfortunately I don't know how to do this directly from SQL, I don't know how to add that much logic into that (maybe at/after/for counting in each buy/sell operation you should create new pair of temporary purchases and sales tables to work with?).

You may need actual code there.

[Woodie]

There might be data missing like closing inventory from the previous period and any new stock of the current period and the type of business run to know if payables are involved etc.