Results 1 to 10 of 10

Thread: SQL Problem

  1. #1
    Large Member
    Join Date
    Apr 2004
    Posts
    3,720
    Thanks
    47
    Thanked
    99 times in 64 posts

    SQL Problem

    I'm trying to figure out a tricky select using ms sql, does anyone have any tips.

    I have a table (tblTimeslot) which contains a series of periods, and a value indicating how many are available i.e.

    Code:
    |   timslotID   |   periods   |    time   |
             1             6             4:30
             2             3            13:30
    Another table (tUsers) containg the FK timeslotID.

    Now I want to select all of the timeslots in tblTimeslot, which have a corresponding timeslotID in tUsers, plus rows for each unused timeslot up to the number specified in the column periods. I want to do this without using a cursor.

    Any suggestions?

    Code:
    SELECT t.timeID, t.periods- (SELECT COUNT(*) FROM tTimeSlot INNER JOIN tUsers ON tUsers.timeID = tTimeSlot.timeID WHERE tTimeSlot.timeID = t.timeID) AS remaining
    FROM tTimeslot AS t
    LEFT JOIN tUsers AS r ON t.timeID = r.timeID
    The above returns all rows from tTimeslot, + a count of the remaining periods for each slot. How can I convert the 'remaining' field to rows?
    Last edited by yamangman; 03-09-2007 at 08:23 PM.
    To err is human. To really foul things up ... you need a computer.

  2. #2
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: SQL Problem

    How about temp tables.

    I like these because they make debugging the sql really easy.

    Simply grab any un-used timeslots upto time, into temp table.

    then dump all used by the user time slots into temp table.

    return all rows from temp table.
    throw new ArgumentException (String, String, Exception)

  3. #3
    Large Member
    Join Date
    Apr 2004
    Posts
    3,720
    Thanks
    47
    Thanked
    99 times in 64 posts

    Re: SQL Problem

    The problem is tTimeslot does not hold a record for each timeslot. It holds a series of records for differing timeslots, and a number value indicating the available total. I need somthing that will return records based upon that value, minus a count of the number 'taken' in tUsers.

    Oh I think I see what you're getting at....
    Last edited by yamangman; 04-09-2007 at 09:05 AM.
    To err is human. To really foul things up ... you need a computer.

  4. #4
    Large Member
    Join Date
    Apr 2004
    Posts
    3,720
    Thanks
    47
    Thanked
    99 times in 64 posts

    Re: SQL Problem

    Well I came up with this, if anyone can suggest a proper way of doing this be my guest.

    Code:
    --WIP
    DECLARE @id int
    DECLARE @counter int
    DECLARE @count int
    SET @count = 0
    
    SELECT @id = t.timeID, @counter = t.slots - (SELECT COUNT(*) FROM tTimeSlot INNER JOIN tRegistration ON tRegistration.timeID = tTimeSlot.timeID WHERE tTimeSlot.timeID = t.timeID)
    FROM tTimeslot AS t
    WHERE t.timeID = 1
    
    CREATE TABLE #ttEmpty (
    	timeID int, 
    	name varchar(100),
    	remaining int
    )
    
    INSERT INTO #ttEmpty (timeID, name, remaining)
    SELECT t.timeID, r.name, t.slots - (SELECT COUNT(*) FROM tTimeSlot INNER JOIN tRegistration ON tRegistration.timeID = tTimeSlot.timeID WHERE tTimeSlot.timeID = t.timeID) AS remaining
    FROM tTimeslot AS t
    INNER JOIN tRegistration AS r ON t.timeID = r.timeID
    WHERE t.timeID = 1
    
    WHILE @count < @counter
    BEGIN
    	SET @count = @count + 1
    	INSERT INTO #ttEmpty (timeID, name, remaining)
    	VALUES (@ID, '', 0)
    END
    
    SELECT * FROM #ttEmpty
    DROP TABLE #ttEmpty
    Last edited by yamangman; 04-09-2007 at 10:11 AM.
    To err is human. To really foul things up ... you need a computer.

  5. #5
    TiG
    TiG is offline
    Walk a mile in other peoples shoes...
    Join Date
    Jul 2003
    Location
    Questioning it all
    Posts
    6,213
    Thanks
    45
    Thanked
    48 times in 43 posts

    Re: SQL Problem

    At first glance i think you need to be looking at a crosstab query.

    But i'm rushed off my feet and won't have time to properly read this till later....

    TiG
    -- Hexus Meets Rock! --

  6. #6
    Senior Member
    Join Date
    Jul 2007
    Location
    127.0.0.1
    Posts
    236
    Thanks
    19
    Thanked
    17 times in 14 posts

    Re: SQL Problem

    I only use Oracle, so please excuse my ignorance of MS SQL, but wouldn't an outer join work here?

    This might help:

    http://www.databasejournal.com/featu...le.php/3623916

    Sorry, just re-read your request and it won't satisfy it fully will it? As you want to display a number of rows equal to the value in the periods column for all unmatched rows don't you?
    Last edited by med2003; 04-09-2007 at 11:39 AM.

  7. #7
    www.dougmcdonald.co.uk
    Join Date
    May 2007
    Location
    Bath
    Posts
    523
    Thanks
    5
    Thanked
    20 times in 20 posts
    • DougMcDonald's system
      • Motherboard:
      • Asus P5B Deluxe
      • CPU:
      • Inter Core 2 Duo E6600
      • Memory:
      • 2 x 2GB - Geil Black Dragon PC6400
      • Storage:
      • 2 x 400GB Samsung Spinpoints (Running in Matrix array) 100GB @ RAID0 + 300GB @ RAID1
      • Graphics card(s):
      • BFG nVidia 8800GTS 320MB OC2
      • PSU:
      • Corsair HX520W modular
      • Case:
      • Lian-Li PC7 II Plus
      • Monitor(s):
      • LG 17" Flat Thingy
      • Internet:
      • Crappy BT 1MB Unreliable wank :s

    Re: SQL Problem

    A little confused by the requirement here, can you explain this sentence in a little more detail please?

    Now I want to select all of the timeslots in tblTimeslot, which have a corresponding timeslotID in tUsers, plus rows for each unused timeslot up to the number specified in the column periods.
    An example of the tUsers table would be helpful, and the general output you are trying to achieve, as in, what are you doing with it afterwards, where are the results going?

  8. #8
    HEXUS.net Webmaster
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3,108
    Thanks
    1
    Thanked
    0 times in 0 posts

    Re: SQL Problem

    sounds like a UNION unless I've misunderstood the question i.e. run the query for all the timeslots that have the corresponding entry in tUsers and then union the results of that with all the unused slots. The problem is you haven't indicated how big a slot is (30 mins ?) or where they are held.

    As Doug has said, put the requirement in plain English first and then give the technical background, it'll be easier for us to assist

  9. #9
    Large Member
    Join Date
    Apr 2004
    Posts
    3,720
    Thanks
    47
    Thanked
    99 times in 64 posts

    Re: SQL Problem

    No worries i've done it now.

    I thought I had explained it clearly but after working on somthing for a few a hours you tend not to i guess.

    Suffice to say a union wouldn't of worked in this instance, as I wanted to return a timeslot row multiple times up to the count specified in periods. Thanks for the help though.
    To err is human. To really foul things up ... you need a computer.

  10. #10
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: SQL Problem

    from my understanding you wanted some form of logic thats hard to express functionally?

    hence why an itterative loop was the easyest solution.

    Also the overhead isn't much as effectivly your returning a 'missing between' slots. So the table query is only run once, the rest are all inserts into a temp table.

    I hate sql.
    throw new ArgumentException (String, String, Exception)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Problem with new build ...
    By nickp8 in forum PC Hardware and Components
    Replies: 3
    Last Post: 04-08-2007, 09:29 PM
  2. Replies: 17
    Last Post: 15-07-2007, 03:59 PM
  3. IE7 problem - please help?
    By SkyNetworks in forum Help! Quick Relief From Tech Headaches
    Replies: 5
    Last Post: 20-06-2007, 09:36 PM
  4. ECS 910 / Advent 7080 White Screen and no VGA problem Notebook Laptop
    By David R in forum PC Hardware and Components
    Replies: 0
    Last Post: 18-06-2007, 05:52 AM
  5. Fatality FP-IN9 resume from S3 problem
    By $kunk in forum abit.care@HEXUS
    Replies: 0
    Last Post: 22-05-2007, 04:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •