Posible solution to reservation part

Question

Posible solution to reservation part

Robero Londono

Grasshopper

Points: 12
More actions
September 3, 2008 at 11:56 pm

#99256

Hi everyone. I am working to develop a reservation system to equipments.
I have one table that has 3 columns: 1-equipmentID ,2-dateout ,3 -datein. Also I created another table with hollydays. It has only one column with the dates accepted as hollydays by my company. Now I created the availability of the equipment per month. in this case, I created one temporary table titled equimentavailability with the columns equipmentid, day1, day2.... day31.
My problem is that when I try to mark what day is hollyday in my temporary table of equimentavailability then I have problems to update.
Here is the code:
Declare @dayName nvarchar (50)
Declare @value int
set @value= 2
Set @dayName='Day4'
Update GUC_RTN_AbilityEquipment set @dayName = column = @value
Where @dayName = 1
Thanks for helping and your time
Robert

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Robero

Looks like you've got a little confused with SQL Servers' proprietary variable update, typically used for running totals and other operations where sequential data is updated according to values held in adjacent rows.

You're thinking that @dayName is an alias for a column called 'Day4':

Update GUC_RTN_AbilityEquipment SET Day4 = @value Where Day4 = 1

But SQL Server is using @dayName as a variable (same as @value), and your statement will fail.

Dynamic SQL will work for this:

Declare @dayName nvarchar (50)

Declare @value int

set @value= 2

Set @dayName='Day4'

DECLARE cSQL VARCHAR(500)

SET cSQL = 'Update GUC_RTN_AbilityEquipment SET '+@dayName+' = ' + CAST(@value AS CHAR(1)) + 'Where '+@dayName+' = 1'

PRINT cSQL -- sanity check

EXECUTE(cSQL) --or sp_executesql

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 2

Change your structure. Make the days rows instead of columns. It's going to be MUCH easier to work with in the long run.

You really don't want to have to deal with rules like "is the equipment available on Feb 31"? Rows from a calendar table will handle that correctly and easily. Columns will make it much more difficult to deal with.

If the final presentation of the data requires columns, make the front-end app pivot the data. That's easy.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Robero Londono Grasshopper Points: 12 More actions · Answer 3

Hey Chris

I am very happy it work very good.

Thanks some much.

Roberto

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi Robero

Thanks for the feedback. I would however read very carefully the wise words of GSquared, he's absolutely right. If you are in a position to alter your schema at this stage, the effort would be well worth it in the long run.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden