Hi, I would llike to mantain a table that contains only the last 50 sales per warehouse and sku. The table would be something like:

WarehouseId (the warehouse where the sale ocurred)
SkuId (the article being sold)
SalesDateTime ClientId
SalesQty SalesCost
SalesPrice
TransactionId (the transaction number)
TransactionRowNumber (the transaction row number)

I would like to only keep the last 50 sales for each WarehouseId/SkuId.

Reading the sales directly from the transactions would be costly because the transactions contain other kinds of transactions (purchases, transfers, adjustments), and at least 3 joins are necessary to determine which rows correspond to sales. Because of this, I was thinking that if I could mantain the last 50 sales in a separate table, I could more efficiently work with them.

The only idea I have is to have a row counter column, and when it reaches 50, delete the first one, renumber the records and insert the new #50. But this idea seems like a mess....

Any ideas on how to accomplish this task of mantaining only an "X" number of records for a given set: In this case 50 records per WarehouseId/SkuId.

Thanks, Edgard

asked 08 Jun '17, 09:01

elriba's gravatar image

elriba
51334
accept rate: 0%


You could do it with an AFTER trigger on an appropriate table that would insert the new sale with all relevant details into your "last 50 Sales" table and delete the oldest. You can use the SELECT TOP X syntax to establish which are your 50 newest sales.

However - do you really need to do this? 3 joins is not many tables for SQL Anywhere to deal with. We are often running queries with between ten and twenty tables involved and expect to do it in real time (with hundreds of users). You just need to be careful in your design of tables and indexes and also in your approach to locking (ie use isolation level 0).

permanent link

answered 08 Jun '17, 10:38

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

I second Justin's hint.

In case you really really need to store the last n rows in a separate table, you might also think of a materialized view. That would "update" the according data automatically when specified accordingly. Note, locking issues still need to be considered carefully.

(08 Jun '17, 16:45) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 08 Jun '17, 09:01

question was seen: 1,257 times

last updated: 08 Jun '17, 16:45