[SOLVED] Need Query to limt by last sale price of each item

[widowmaker]

Hi,
Very Old uneducated man with poor memory here that could use a bit of help.
Today, I started testing out a new db I constructed using LibreOffice Base 6.0 and HSQL.

I ran into multiple things I need to tweak. The first one involves a form containing multiple main forms and subforms.

One of those subforms displays a datasheet that is linked to a query. That particular query displays details relating to items (stocks) that have been sold such as when I sold them and for what price. The query works fine except for one important detail. It displays ALL of the (stocks) I have sold.

Since I frequently buy and sell the same stocks repeatedly, combined with the fact that prices change multiple times PER SECOND thus making the time it takes for me to view date very important, I need it to limit my display so that it only displays the most recent sale price for each stock I have sold, separated by brokerage house.

The query I am using only combines details from two tables. The Buy and Sell tables. The Buy# is the most important field, as it is used on every table to track ALL of the details involving that particular stock purchase.

The Buy# is a PK from the Buy Table and FK on the Sell Table. The Sell# is the PK on the Sell Table and FK on the Buy Table. (I use numerous other tables for Mergers, Dividends, Splits and other details and all of those tables are linked to the Buy# and Sell#, but their data is not involved in this query except the (SYM) from the Stock Table.

Below is a copy of the sql from this particular query, If anyone can rewrite my sql or give me a sample on how I can restrict the listings to display the (Sell.ShrPrice) of each stock (SYM) restricting it to ONLY the last (Sell.SellDte) I would appreciate it.

SELECT "Buy"."Buy#", "Buy"."Sym", "Buy"."BDate", "Buy"."PerSh", "Buy"."BQty", "Sell"."SellDte", "Sell"."ShrPrice" AS "SellAmt", "Buy"."Broker" FROM { oj "Sell" RIGHT OUTER JOIN "Buy" ON "Sell"."Buy#" = "Buy"."Buy#" } WHERE "Sell"."SellDte" IS NOT NULL ORDER BY "Sell"."SellDte" DESC

Thank You for any helpful code or ideas

[chrisb]

Hi,
so the "Sym" identifies the product & you wish to show one row for each unique "Sym" where "Sell"."SellDte" is equal to the maximum "Sell"."SellDte" for said "Sym".
bear in mind that i cannot test the code.
1) hit the 'Queies' icon.
2) hit 'Create Query in SQL View'.
3) hit 'SQL' icon on toolbar.
4) paste the code & hit F5 to execute.

Edit: 2020-05-23 12:56. inserted a missing double quote before Buy"."PerSh"

Code: Select all

select "Buy"."Buy#", "Buy"."Sym", "Buy"."BDate", "Buy"."PerSh", "Buy"."BQty",
"Sell"."SellDte", "Sell"."ShrPrice" "SellAmt", "Buy"."Broker"

from "Sell"
join "Buy" ON "Sell"."Buy#" = "Buy"."Buy#"

join --this sub-query selects the maximum sell date for each unique Sym
(	 --the join ensures that only rows which match Sym & maximum sell date are displayed
	select 
		b."Sym", max(s."SellDte") "MaxSellDte"
	from
		(select "Buy#", "SellDte" from "Sell") s
	join
		(select "Buy#", "Sym" from "Buy") b
		on s."Buy#" = b."Buy#"
	group by "Sym"
) s1
on s1."Sym" = "Buy"."Sym" and s1."MaxSellDte" = "Sell"."SellDte"
order by "SellDte" desc

[widowmaker]

Thank you very much for the sql code. It did manage to limit the results to one date per "Sym" however I am still getting some duplicates.

The reason for the duplicates now is where a stock "SYM" is sold multiple times on the same date.

Selling the same stock multiple times on the same day can occur when I have bought the same "SYM" multiple times, thus giving me multiple "BUY NUMBERS" and multiple "SEll numbers" for the same SYM on the same date. The results show me cases where some stocks were sold at the same price and some were sold at different prices.

To limit the results by "Broker" might be one way to narrow down the results, because it is not uncommon for me to SELL stocks through different brokers and the seconds it takes to get from one screen to another one will have an effect on the "SELL" price.

The other duplicates seem to come from stocks that were bought and sold multiple times on the same date, through the same "Broker".

I can live with these results for awhile because they are giving me much better results then I was getting before, but I am wondering if there is a way to limit the results to only (1) result per "SYM" and "MaxSelDte" regardless of the BUY# or SELL#?

Thank you again for your help. It has made a major difference.

[chrisb]

the problem is caused by the structure of your database.
what you have is an IN/OUT database.
it is a mistake to link the tables "Buy" & "Sell" using the field "Buy#". there is no need for such a link.
the only link required is the product i.e. "Sym".

in order to identify the most recent sale price we need to be able to separate items of the same type which were sold on the same date.
add a time field named "SellTime" to the table "Sell".
it's also advisable to add a field to the table "Sell" which identifies the product ("Sym").
the table "Sell" is now independent.

i do not have access to your database & therefore cannot emphatically suggest the deletion of "Sell"."Buy#".
it seems to me that when an item is sold it is not necessary to link that sale with a particular purchase because the items are identical & the purchase price does not influence the sale price.

it's best that i give you a little demo so please download & study the attachment, it should help.
use the the form to retrieve the most recent sales data for any one or all items in a couple of seconds.
the form has one list box & one table control. the source code for both is embedded within the form document.

[widowmaker]

Thank you VERY much for your help. I think your idea of using a time stamp will solve my duplication situation.

I also want to thank you for the demo table/form and ideas about not linking the buy# and sell#. I will have to ponder that concept. I tried using a transaction# and transDetail# in the past, but scrapped that idea and opted for just using the buy# due to complications when trying to incorporate dividends, drips, washes, splits, etc.

I still haven’t figured out the best way to handle all that and might make a copy of my db with all the data stripped out, so that you can see it.

I know there will be some features I want, that will not be possible to create, especially with my old worn out brain, and possibly not even with others brains.

BTW, there are multiple reasons I ended up tying the buy and Sell tables together. There are certain legal rulings I have to follow, that can limit how long I have to own some stocks before I can sell it as well as how long I may have to wait before I can buy the same stock back through the same broker account. Plus I try to keep a running total of the amount received in dividend payments between the buy date and within x days after I sell it. That running total of dividends received can have an effect on when I might want to sell the stock, so I try to keep that total on my main form along with all the dates.

Since I am new to this forum. If I decide to upload a db, is there a preferred location as to where I should upload it. Since I own multiple websites, I have many safe choices.

Anyway, thanks again. I will spend some time studying your demo and see what I can learn from it and ponder the options.

[Hagar Delest]

If I decide to upload a db, is there a preferred location as to where I should upload it. Since I own multiple websites, I have many safe choices.

As you wish. If smaller than 128kB, it can be uploaded here.

[widowmaker]

I am uploading my STOCK db.
I know it is sloppy and incomplete at this point, but hopefully it gives an idea of what I am trying to accomplish.

If you read the Form named NOTES, as it has my list of to do items,. in order for me to remember what areas I need to work on.

I don’t have any calculations set up yet. I have many of these calculations in my spreadsheet, but have not converted them over to my DB yet. Some of them I know how to do, and some I don’t.

I am not expecting anyone to help me with these calculations at this time, but I do have them included in my "ToDO" list located in the "NOTES Form" in case they could help others when it comes to informing me what changes I need to make to certain tables and queries.

I appreciate any ideas or help on this project.
Thank You all very much.

[chrisb]

hello widowmaker,
i think that this thread is solved, i gave a good answer & it's up to you to incorporate that solution into your database.

this forum works well when a user has a specific issue, clearly states that issue & provides adequate, relevant information/detail from which one can deduce a possible solution.
you have uploaded your db without any of the above.

i can understand what the buy, sell, stock & dividend tables could be although i have no idea how or when dividends may be deduced/applied.
i do not have a clue what the drip or event tables represent.

there are rules which must be adhered to when designing a database three of which are: avoid duplication & do not store null or calculated values.
be assured that the structure of the database is paramount & should be given due consideration. poor design often results in a restart.

the stock table is ok.
the buy table should only contain fields which apply to every purchase.
the buy table should not contain fields such as sell, sold, balance etc.
likewise the sell table.
anything which is deemed desirable but not absolutely essential (e.g. notes. unless every record contains a note) can be stored in a linked table/tables.

my ignorance denies an opinion on drips, events & dividends.
please do not let my unhelpful response dissuade you from making further posts, i have the will but lack the knowledge.

[widowmaker]

Thank you very much Chrisb, for your help.
You gave Very good responses. The guides and the SQL code you gave were quite helpful. I appreciate you taking the time and effort.
Appreciate all of the instructions and guidelines you offered.
Thanks Again. I will mark this closed now.