Dear All,
Need sql query help to delete all the transactions older than 2016. We just want to keep year 2016 data in the database.
Thanks & Regards,
Upendra Nath M.
*This post is locked for comments
well we implement alot of BI for RMS and custom modules
sentricorp.com
After working on with RMS for the past 3 yrs, i have known that the product does have limitation. RMS is good only for small to medium size retailer companies, problems will arise as soon as the data becomes bigger and RMS could not handle that data. Reason? DB Schemas is not well implemented. Can you imagine, for example, you have 30-50 branches out there operating say, 10am-10pm, 7 days a week, transactions grows bigger, transactionentry creates 2 records for every transaction. We sync this data to our HQ, say, hundreds of thousands of data a day, millions a month for a single client then how do you think RMS DB will handle such load?
Knowing RMS, it is a transactional database. Imagine, if a company have 10-100 employees in HQ, and take note, these people are generating reports at the same time, no wonder the HQ Manager eventually dead/hangs up. So whats the plan to prevent this?
IMPLEMENT A DATAWAREHOUSE SYSTEM.
In this, you can have better insights and avoids locking upon generating reports.
It also does create Dashboards which is really good if you want to see the overall production/sales for a certain year, month..etc
I am currently working on with Business Intelligence company and i am willing to entertain any questions regarding Datawarehouse system. Just email me.
If it is a single or a few transactions VOID them and issue refunds from the same accounts that were paid for. This way your customer account balances, inventories, tenders and profits will be right. It is not good practice to delete on SQL, transactions or anything else except journal images for the purpose of recovering space.
Look at this post to see what happened when items were deleted with a query in RMS Administrator. Read all of it.
morning tried all that not working.. if i can get the sql query i can delete it from that i hope..
When you recalled the sale, did you void it or refund it? I'm not sure if it would make a difference with reporting, just curious. I'm a bit weary giving sql to delete a transaction, as I haven't tested this and don't want to break your database, and leave orphan data.
do you have one to delete a single transaction?...a customer mistakenly add 2800000 to the cost price and she sold the item..but in the reports its showing the profit and the margins with that huge figure..we tried recalling the transaction and voiding but it still shows in the report and is throwing of their figures
Right back at ya TheNeos Happy Holidays!!!
I didn't have a mature response to his comments so I didn't reply, lol
Whats wrong with you "Butch" or "freeman"?
Nick's first sentence is "Backup first", Upendah's post was not about recovering space. There are situations that only current year needs to be.
So thanks Nick for your efforts and have a merry Xmas.
Neos
It seems unusual my friend that you don't condone deleting data, but ask in a public forum how to do it?
Brilliant Nick...put SQL statements in the customer forum so everyone can delete their history instead of archiving it or moving backups to recover disk space. Good plan, kudos for mentioning don't forget to backup though. Yes, I know you need a third party archive tool for RMS and I also know a 4TB external hard drive is about $100.00. [tag:fail]
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (