Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SonicClang
Starting Member
8 Posts |
 Posted - 2012-08-31 : 16:53:31
|
| I'm a total noob to SQL. I don't know what I need to accomplish this, but this is what I need to happen. We have a vault. During the work week, material is checked in and out as they use it. But on the weekend they have to physically check the material back in. But they don't check material back in if it was consumed to make product. So I want to update a bit field to 1 if material wasn't checked in before the weekend. If I don't do this, it will look like the material is perpetually checked out to a workstation. A stored procedure perhaps? Can you schedule a SP? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-31 : 17:46:39
|
| You could do it as an SP or just a script, but you need to do it as a scheduled job.-Chad |
 |
|
|
SonicClang
Starting Member
8 Posts |
Posted - 2012-09-04 : 10:47:08
|
quote:
Originally posted by chadmat
You could do it as an SP or just a script, but you need to do it as a scheduled job.-Chad
I figured I would do it that way, but... how? I don't even know where to begin.1) Whether a package is in or out of the vault isn't stored in the package table, it's in the location table. So to figure out if a package is in or out, I need to run a query.2) Can a stored procedure look at the results of a query, or would I built a query into the SP?3) How do you make a SP cycle through the results of a query and update all records that it returns?I know VERY little about this stuff. I'm willing and ready to learn though. I just need to be pointed in the right direction. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-04 : 11:19:41
|
quote:
1) Whether a package is in or out of the vault isn't stored in the package table, it's in the location table. So to figure out if a package is in or out, I need to run a query.2) Can a stored procedure look at the results of a query, or would I built a query into the SP?3) How do you make a SP cycle through the results of a query and update all records that it returns?
Short answers to your numbered items above:1) Yes, you would need to run a query - but it should be a simple join that uses the two tables that you listed - package and location. 2) Not quite clear what you mean, but usually, you would write a stored procedure that runs the query that I mentioned in (1) and use the data from that query to do whatever you need to do.3) You would not cycle/loop through the list - SQL can do the updates in a single set-based query.I am being vague and general in my responses because to be more specific, a bit more information on the tables and sample data is required. Take a look at Brett's blog here and post the details on your table along with some sample data and someone on the forum should be able to give you more precise answers: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
SonicClang
Starting Member
8 Posts |
Posted - 2012-09-05 : 10:13:44
|
| I took a look at that blog and it's frankly a bit overwhelming. I don't even know where to begin looking for my answer. And since I'm not trying to become a full-time SQL expert, I don't want to spend hours browsing just to find the solution to this one answer.I'm thinking I'm either going to need to hire someone to do this (I'm sure it would only take someone who knows how to do it a couple hours), or have a very nice and helpful person here help me figure it out. I'm open to giving as much detail as humanly possible if someone would like to help me out. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-05 : 10:28:54
|
quote:
Originally posted by SonicClang
I took a look at that blog and it's frankly a bit overwhelming. I don't even know where to begin looking for my answer. And since I'm not trying to become a full-time SQL expert, I don't want to spend hours browsing just to find the solution to this one answer.I'm thinking I'm either going to need to hire someone to do this (I'm sure it would only take someone who knows how to do it a couple hours), or have a very nice and helpful person here help me figure it out. I'm open to giving as much detail as humanly possible if someone would like to help me out.
The easier way out would be to hire someone - but even they would need to either be able to access your servers and understand the existing databases, tables etc. to propose a plan and to estimate the time and costs.If you want to try to get the answers here, one thing I can guarantee is that there is no dearth of nice people here. But, they can do only so much without knowing the details of the problem.If you do want to try it here, first thing to do is to post the schema for the two tables (assuming you have only those two tables that need to be part of this discussion). Doing that is relatively easy if you have the right permissions. In SQL Server Management Studio, in Object explorer, find the table name, right click on it and select Script Table as -> Create To -> New Query Editor Window. Then post the output you get.People may ask for more information, so it will be a process that takes time and efforts. |
|
|
|
SonicClang
Starting Member
8 Posts |
Posted - 2012-09-17 : 10:39:30
|
| I just wanted to post and let you know I figured this out. I was WAY over thinking it. All I had to do was write an update query and schedule it with the SQL Agent. It's working perfect. |
|
|
|
|
|
|
|
|
Need to update records over the weekend