Newbie Help - SQL Query
-
Hi all,
I am in desperate need of some help please. I'm not a SQL expert but am trying to write an update query that I can later on re-use in MS Access (but the second part I am hoping to work out once I can get something running in SQL server. For info, I can't write a stored procedure so hoping to do everything as a single query.I have a number of ORDERs of a specific <product_Type> and need to allocate these orders to available <Delivery_Route> on the PACKAGE_TRACKER table. The allocation needs to be spread acrross the available <Delivery_Routes> as an equal percentage of available space. (I've done something in excel to try to make it clearer). In the excel, I have 5 routes, with a total available slots of 7,100. To make it easier, I have 710 orders which will fill 10% of the available slots. Route =A has 600 spare slots so I want to fill 10% of the available slots in RouteA, 10% in RouteB etc.
Finally, I need to update the 710 orders with the PACKAGE_TRACKER.delievry_route
I know it sounds complex (and I am really struggling to work out how to do it in a sincgle Select and update query) so any help you could provide would be very much appreciated.
- dump 10119 - Friday, February 3, 2017 1:13 PMHi all,
I am in desperate need of some help please. I'm not a SQL expert but am trying to write an update query that I can later on re-use in MS Access (but the second part I am hoping to work out once I can get something running in SQL server. For info, I can't write a stored procedure so hoping to do everything as a single query.I have a number of ORDERs of a specific <product_Type> and need to allocate these orders to available <Delivery_Route> on the PACKAGE_TRACKER table. The allocation needs to be spread acrross the available <Delivery_Routes> as an equal percentage of available space. (I've done something in excel to try to make it clearer). In the excel, I have 5 routes, with a total available slots of 7,100. To make it easier, I have 710 orders which will fill 10% of the available slots. Route =A has 600 spare slots so I want to fill 10% of the available slots in RouteA, 10% in RouteB etc.
Finally, I need to update the 710 orders with the PACKAGE_TRACKER.delievry_route
I know it sounds complex (and I am really struggling to work out how to do it in a sincgle Select and update query) so any help you could provide would be very much appreciated.
Wouldn't this be as simple as a basic update statement? You first have to determine the percentage. Easy enough to determine the number of rows and the total available. I have no idea where the 710 extras came from but I will leave that to you to determine.
declare @NewPercentage decimal(7,2) = .10 --This is 10%, No idea where you got this value fromUpdate pt
set Allocated_Package_Slots = Allocated_Package_Slots + (@NewPercentage * (select SUM(Default_Package_Slots - Allocated_Package_Slots)))
from PACKING_TRACKER pt_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hi Sean, thanks for your help.
I probably wasn't clear enough in my explanation. I am not trying to update the PACKING_TRACKER table. I am trying to work out how to fill the ORDERS.delievry_route field for each of the ORDERS.PACKAGE_TRACKER is basically a table containing the delivery routes and the available space on each route.
Sorry to use annother example, but I have 710 burgers (ORDERS) that need to be assigned to the 5 delievry vans (PACKAGE_TRACKER.delivery_routes). Once assigned I need to store for each burger (ORDER) which of the 5 delivery vans it's been assigned to (ORDER.delivery_route).
I am trying to spread the burgers across the 5 delivery vans to ensure that each van that has space has some burgers BUT IMPORTANTLY I need to allocate them proportionally so that each van is assigned burgers as a proportion of the available space within that van.
My maths may be a bit off but I would calculate available slots using
select SUM(Default_Package_Slots - Allocated_Package_Slots)
Then I calculate ORDERs
select COUNT(ORDER.id where product_type="burger" AND delivery_route IS NULL)
Assuming I have 710 to allocate (from 7,100 available slots) I want to allocate to 10% of the available slots.
Back to my example, RouteA has 600 available slots so I want to assign 60 orders to RouteA. RouteB has 1,000 so I allocate the next 100 to that route and so on.It's that type of logic I'm trying to solve so on the ORDERs table, the first 60 ORDER.delivery_route values are set to RouteA, the next 100 are set to RouteB and so on.
Once all the ORDERs have been assigned, I can then update the PACKAGE_TRACKER table with the updated assignments.
Apologies for writing a story but it feels easier than trying to show the snippets of code i have tried because that will confuse everybody even more than me. -
Can somebody point me in the right direction please ?
- dump 10119 - Sunday, February 5, 2017 1:40 AM
suggest you read the following article and post back with some scripts that we can all use to replicate your problem
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day - dump 10119 - Sunday, February 5, 2017 1:40 AM
NTILE is what you're looking for. Read up about it at the following URL.
https://technet.microsoft.com/en-us/library/ms175126(v=sql.110).aspx--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - dump 10119 - Friday, February 3, 2017 1:13 PMHi all,
I am in desperate need of some help please. I'm not a SQL expert but am trying to write an update query that I can later on re-use in MS Access (but the second part I am hoping to work out once I can get something running in SQL server. For info, I can't write a stored procedure so hoping to do everything as a single query.I have a number of ORDERs of a specific <product_Type> and need to allocate these orders to available <Delivery_Route> on the PACKAGE_TRACKER table. The allocation needs to be spread acrross the available <Delivery_Routes> as an equal percentage of available space. (I've done something in excel to try to make it clearer). In the excel, I have 5 routes, with a total available slots of 7,100. To make it easier, I have 710 orders which will fill 10% of the available slots. Route =A has 600 spare slots so I want to fill 10% of the available slots in RouteA, 10% in RouteB etc.
Finally, I need to update the 710 orders with the PACKAGE_TRACKER.delievry_route
I know it sounds complex (and I am really struggling to work out how to do it in a sincgle Select and update query) so any help you could provide would be very much appreciated.
I don't think you can do all that in a single statement. SQL Server doesn't support updating two tables in one update statement.
Apparently MySQL / MariaDB does allow this, but then you'd need to tinker with MySQL / MariaDb as your backend database.
https://dev.mysql.com/doc/refman/5.7/en/update.html
https://mariadb.com/kb/en/mariadb/update/ -
Hi Jeff and SSC,
Many thanks for the pointers. I had a look at NTILE which would seem to be what I would need if I was only using it in SQL server, but it's not a function that is also available in MS Access. (I wish I could use SQL Server instead but cant).
SSC, I am not trying to update both tables, with the update query. I'm only trying to update the ORDER table. (I can update the PACKAGE_TRACKER table as a seperate query)
I appreciate your help.
Mike
-
Maybe I should make this bigger...
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.I know that makes me look like a twit, but we cannot see what you see and cannot reproduce what you have without you posting some code to recreate it for us... Please read the article and follow the instructions. Then you'll get a tested answer.
- dump 10119 - Monday, February 6, 2017 1:12 PM
To be honest, I don't think you have a terribly realistic goal. SQL Server is so much more capable from a query perspective that any useful query someone may post here probably wouldn't be able to run in MS Access, and given that it's not exactly rocket science to use MS Access with either VBA code to run SQL queries against an actual SQL Server database (and a lot more reliable than storing that data in Access), or using "Pass-Through" queries (very similar functionality) against a SQL Server database, I'm pretty sure you don't want to have to try and code a solution that will work in MS Access, as it will likely SEVERELY limit your options. I don't store data in MS Access... EVER. And especially when multiple people need to share that data or database. Too much hassle and maintenance, to the point where allowing someone to do that could actually constitute negligence, if for no other reason than the additional cost in time used to fix problems. Given that you haven't even gone to the trouble of investing the time and effort in mocking up some sample data for us to crunch on, I'm not terribly motivated to try and help solve this.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply