Modify stock based on another table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Modify stock based on another table

Author

Topic

iddqd
Starting Member

3 Posts

Posted - 2010-04-08 : 17:02:53

Hi, I'm using VB.net and SQL CE to do a fairly simple project involving rented DVDs. I'm very new to using SQL and so far all I've really used it for is very simple stuff; a simple search and an update column query Eg:

"SELECT MemberID, DVDID, RentedDateRented, RentedDateDue,
 CASE WHEN GETDATE() > RentedDateDue THEN 'Yes' ELSE 'No' END AS
 RentedOverdue FROM [Rented DVDs]"

Now I need to modify DVD stock in a DVDs table based on the number of times that DVD ID appears in a DVDs Rented table. I suppose I need to find a way of running through each DVD ID from the DVDs table and checking the number of times it appears in the DVDs Rented table then deducting that from the quantity of that DVD in the DVDs table and I really don't have a clue how to do this, even after searching the internet for hours I couldn't find anything.

So I was wondering if someone could give me some idea of what I need to do or an example?
Any help would be appreciated!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 18:34:16

Can you post the table structure of the 2 tables in question.

Also, how often do you plan on running this update? If it's deducting from a value in the DVDs table, and you run it twice, it will deduct twice (unless it's specifically written not to, which may require an additional flag in the rented table). Is this what you want? Or do you simply want to update the DVDs table with the number of times it's been rented? That query would look something like:

UPDATE DVDs
SET RentedCount = r.RentedCount
FROM DVDs d
INNER JOIN (
		SELECT ID, COUNT(RentedDate) AS RentedCount
		FROM [Rented DVDs] 
		GROUP BY ID) r
	ON d.ID = r.ID

There are 10 types of people in the world, those that understand binary, and those that don't.

iddqd
Starting Member

3 Posts

Posted - 2010-04-08 : 19:05:24

The tables look like this:
Rental DVDs which contains: DVDID [Primary Key], DVDName, DVDGenre, DVDAgeRating,DVDStock,DVDPrice
and
Rented DVDs which contains: MemberID[Foreign Key], DVDID[Foreign Key],RentedDateRented,RentedDateDue,RentedOverdue

I need it to only deduct each rental once from the DVD Stock so that the number it shows is the number currently in stock would I need to add another column to my rented table to do this properly then?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 19:24:17

If the DVDs are being rented, then I presume that means they're being returned also. Does this mean you'll also need to add them back into the DVDStock column when they're returned? If so, it might be a better idea to update the DVDStock column as DVDs are rented (Deduct 1) and returned (Add 1). Or you could leave it as is, letting is signify the total number of DVDs you have (including those which are currently rented), and write a select query that will calculate the number of DVDs currently in stock by counting the number currently on rent and subtracting it from the DVDStock column. eg, if DVDStock contains 10, and there are 4 records for that DVD that have not been returned, then said query would return 6 (10 - 4).

Also, with the [Rented DVDs] table, how do you specify that a DVD has been returned? Would it be a good idea to add a ReturnedDate Column, which is initially NULL when the DVD is rented, and then updated with GETDATE() when the DVD is returned?

There are 10 types of people in the world, those that understand binary, and those that don't.

iddqd
Starting Member

3 Posts

Posted - 2010-04-08 : 20:10:54

quote:
Also, with the [Rented DVDs] table, how do you specify that a DVD has been returned? Would it be a good idea to add a ReturnedDate Column, which is initially NULL when the DVD is rented, and then updated with GETDATE() when the DVD is returned?

The Rented DVDs table only shows DVDs which are currently rented, so when that DVD is returned the entry will be deleted from that table.

quote:
If the DVDs are being rented, then I presume that means they're being returned also. Does this mean you'll also need to add them back into the DVDStock column when they're returned? If so, it might be a better idea to update the DVDStock column as DVDs are rented (Deduct 1) and returned (Add 1)

That's so obvious and so much simpler, I'm really not sure why I didn't think of that! I guess I'll do it this way, thanks for the help!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 20:20:00

No worries. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources