Counting down in a single table

Question

Post reply

Counting down in a single table

pnr8uk

SSCommitted

Points: 1828
More actions
May 23, 2011 at 6:17 am

#251482

I have a single table with the following columns
EventId TicketsAvailable TicketCost
1010010.00
What I am trying to do in T-SQL is to have a user input how many tickets are required.
eg TicketsRequired 10
The SQL should check the TicketsAvailable and if there are enough tickets available begin
generating the tickets and finally update the table to the new total.
The new total would be 90.
The code I have written so far does this just fine.
However what I really need to do is when the Tickets available count reaches 5 and tickets
required is 6 I want the SQL to update the table with 5, effectively setting tickets
available to 0 but the big part is then to write the sixth ticket into a new table
which could be a demandfor table.
Does anyone have any ideas on how I can achieve this in T-SQL?
Many thanks in anticipation of your help.
Paul

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Where are yo not clear going about this? This is really straight forward especially with the clear steps.

What have you tried, where are your road blocks?

pnr8uk SSCommitted Points: 1828 More actions · Answer 2

Here's what I've done so far (it's all a bid Noddy) right now.

The second table has just two columns. This all works fine until I have say 2 tickets remaining in table one and the user wants to buy 4, what I want to do is allocate the 2 to the first table and the two extra to the second.

Thanks for all your help - I'm sure I'll sort it eventually.

SET NOCOUNT ON;

DECLARE @TReq INT; -- Tickets wanted

DECLARE @TTotalAvail INT; -- Total available (seats in venue)

DECLARE @TRem INT; -- Amount currently availabele read from table

DECLARE @TOver INT; -- Amount of tickets over (to be used to determine second date or not)

SET @TReq = 10 -- Input from concert goer

SET @TRem = (select TRem from Test1) -- check only

IF @TRem >= @TReq -- Can we even allocate this demand?

BEGIN

PRINT CONVERT(varchar(255),@TReq) + ' tickets being printed'

update Test1 set TRem=TRem-@TReq

END

ELSE

IF @TRem <= 0

BEGIN

SET @TOver = @TReq

PRINT 'oh no all gone!'

insert into DemandFor

(EventId,TOver)

values ((select EventId from Test1),@TOver)

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

pnr8uk (5/23/2011)
SET @TRem = (select TRem from Test1) -- check only

What happens if there's more than one row in Test1?

Is this a homework exercise?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

There seems to be a lot missing from this logic and even a few requirements. No offense but are you sure this is not some sort of homework?

The degree of difficulty to get this working with the current requirement is pretty easy for someone with a little experience in sql.

... and the actual requirement to code a ticketing system is actually way out of reach of any beginner (no offense to anybody intented).

pnr8uk SSCommitted Points: 1828 More actions · Answer 5

no offence taken in anyway shape or form.

To answer both questions I haven't included all of the sp as I did think it was only the final insert into which was the problem nothing more.

Obviously there are checks for more than one row... based on which event the tickets are for etc., etc., I re-wrote only this section in a couple of minutes to indicate the area where I'm stuck. The only problem I have with the big picture is when there is a less than available number and I want to allocate only a portion of the amount required.

There's a whole bunch of stuff going on elsewhere behind all of this to get to this simple point it's more my maths than my t-sql.

Thanks guys and don't worry any more about it I thank you for your time and assistances. I'll post the full solution and sp today.

Don

WayneS SSC Guru Points: 95460 More actions · Answer 6

The things that I see:

1. The update statement for Test1 is only firing with Rem >= Req. It needs to always fire, and to be smart enough to know when it's zero.

2. The DemandFor needs to have entered into it the difference between @Req and @Rem.

So, the resulting code would be:

PRINT CONVERT(varchar(10),@TReq) + ' tickets being printed'

-- always update, decrement TRem by @TReq if enough tickets available

update Test1 set TRem=CASE WHEN TRem >= @TReq THEN TRem-@TReq

-- otherwise set to zero

ELSE 0

END;

IF @TReq > @TRem

BEGIN

SET @TOver = @TReq - @TRem;

PRINT 'oh no all gone!'

insert into DemandFor

(EventId,TOver)

values ((select EventId from Test1),@TOver)

END

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

pnr8uk SSCommitted Points: 1828 More actions · Answer 7

Oh thanks Wayne - that's perfect...

Thanks for your help it's very much appreciated and will be noted.

WayneS SSC Guru Points: 95460 More actions · Answer 8

pnr8uk (5/23/2011)
Oh thanks Wayne - that's perfect...
Thanks for your help it's very much appreciated

You're welcome.

I was just looking at this again, and I think that the first part should be changed to check for @TRem > 0, so that if it is zero is isn't updated unnecessarily (it would work properly without it, this just avoids updating a value already at zero with zero):

IF @TRem > 0