May 23, 2011 at 6:17 am
I have a single table with the following columns
EventId TicketsAvailable TicketCost
10 100 10.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
May 23, 2011 at 6:37 am
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?
May 23, 2011 at 8:02 am
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
May 23, 2011 at 8:11 am
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
May 23, 2011 at 8:14 am
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).
May 23, 2011 at 8:26 am
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
May 23, 2011 at 8:55 am
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
May 23, 2011 at 9:03 am
Oh thanks Wayne - that's perfect...
Thanks for your help it's very much appreciated and will be noted.
May 23, 2011 at 10:42 am
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
BEGIN
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;
END;
and will be noted.
Feel free to send PayPal payments to me!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply