locked
Identifying blocks RRS feed

  • Question

  • Hi everybody,

    I have a relatively simple problem but the solution eludes me.

    I have the following query result:

     

    ShowID	SeatID	SeatNumber	LevelID	SectionID	Row	theFrom	theTo	Ranking
    1	27	1	1	2	A	1	3	30
    1	28	2	1	2	A	2	4	30
    1	29	3	1	2	A	3	5	30
    1	30	4	1	2	A	4	6	30
    1	31	5	1	2	A	5	7	30
    1	32	6	1	2	A	6	8	30
    1	33	7	1	2	A	7	9	30
    1	34	8	1	2	A	8	10	30
    1	35	9	1	2	A	9	11	30

    I want to assign a 'block' number to the N adjacent seats, say, 3 which are in the same showid, levelid, sectionID, Row and have min. rank. So, based on the sample above, Seat IDs 27,28,29 are Block1, Seats 30,31,32 are next block, etc. Since the ranking is the same, I guess the priority will be best of the closeness to the center of the row, so let's assume we also will have RowStart, RowEnd fields in the list of the fields. Can you please suggest an idea? Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, July 1, 2011 5:03 PM

Answers

  • I think problem is solved now, right?
    N 56°04'39.26"
    E 12°55'05.63"
    • Marked as answer by Naomi N Monday, July 11, 2011 3:11 PM
    Monday, July 11, 2011 3:04 PM

All replies

  • Naomi,

    I would suggest to post table schema, including constraints, sample data, and expected result. Try to include, in the sample data, any posible situation that is important to take in mind.

    For example, I am not following you regards the role of [Ranking] in this problem. Can we have consecutive seats but with different rank?

     

     


    AMB

    Some guidelines for posting questions...

    Friday, July 1, 2011 5:52 PM
  • Do you also have to worry about if all the seats in a block are available?
    JAM
    Friday, July 1, 2011 6:18 PM
  • I have a script to create all related tables and populate with the sample data (send by my colleague), but it's a bit involved and long, so if it's OK, I can e-mail it to you.

    The above is the result of the following query suggested by Peter:

     with AvailableSeats as (SELECT s.ShowID, 
        s.SeatID, 
        s.SeatNumber,
        s.LevelID,
        s.SectionID,
        s.[Row],
        f.theFrom,
        f.theTo,
        f.Ranking
    FROM   (
           SELECT ShowID,
              LevelID,
              SectionID,
              [Row],
              SeatNumber,
              SeatID 
           FROM dbo.rsSeats S
           WHERE ShowID = @ShowID
           -- exclude seats on hold
           AND NOT EXISTS (select 1 from rsHoldSeats HS 
           WHERE HS.SeatID = S.SeatID) 
           
         ) AS s
    CROSS APPLY (
           SELECT   SUM(Ranking),
               MIN(SeatNumber),
               MAX(SeatNumber)
           FROM   dbo.rsSeats AS w
           WHERE   w.ShowID = @ShowID
               AND w.LevelID = s.LevelID
               AND w.SectionID = s.SectionID
               AND w.[Row] = s.[Row]
               AND w.SeatNumber >= s.SeatNumber
               AND w.SeatNumber <= s.SeatNumber + @WantedSeats - 1
           HAVING   SUM(AvailCode) = 0
               AND COUNT(*) = @WantedSeats
         ) AS f(Ranking, theFrom, theTo)
    )
     SELECT * from AvailableSeats
    

     

    This is a great query, but I need to enhance it.

    1. I need to only return 3 blocks on the best seats (so, If I want 4 seats, I will return 12 rows back). I also need to mark each block (first block as 'Best' and two other as 'Choice 2', 'Choice3')

    2. There is another complication as I may want to get 3 seats for a series of shows - I'm not clear how to approach it and enhance the query above, so if I purchase a package of 3 shows and want to reserve 4 seats, I will need 3*4*3 records to be returned.

    One extra complication is also that I can optionally pass LevelID/SectionID - here I'm also contemplating if I need to implement it as dynamic query or use ISNULL(@LevelID, Level) approach.

    Looking at it a bit more and I think we did discuss it - can I put TOP (3) in the CROSS APPLY query? I'll try that first.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, July 1, 2011 6:56 PM
  • This might be close to what you want.  The problem is it assigns blocks starting in the center of the row, which is great for the people purchasing the tickets, but is a little ineffecient for the person selling the ticket.  Maybe it will help some.  Block numbers are null for left over seats.

    ;with t1
    as
    (
    select 
    	 showid
    	, levelid
    	, sectionid
    	, row
    	, seatid - (seatid % 3) block_start
    	, row_number() over (partition by showid, levelid, sectionid, row order by row) block_number
    from #seating
    group by showid
    	, levelid
    	, sectionid
    	, row
    	, seatid - (seatid % 3)
    having count(*) = 3	
    )
    	select t1.block_number, s.*
    	from T1
    		right join #seating s 
    			on t1.showid = s.showid
    				and t1.levelid = s.levelid
    				and t1.sectionid = s.sectionid
    				and t1.row = s.row
    				and t1.block_start = (s.seatid - (s.seatid % 3))

     


    JAM
    Friday, July 1, 2011 7:48 PM
  • Thanks. We discussed the problem and decided to go with the Rankings only as the Rank should already reflect the seat position. I already realized I do have block id as TheFrom/TheTo combination already identifies the block.

    Now, here is what I produced so far:

    -- Idea from Peter Larsson
     IF @ShowID IS NOT NULL -- get best seats for the show
      with AvailableSeats as (SELECT 
           
           s.ShowID,       
           s.LevelID,
           s.SectionID,
           s.[Row],
           f.theFrom,
           f.theTo,
           f.Ranking
    FROM      (
                 SELECT  TemplateID,
                    ShowID,
                    LevelID,
                    BlockID,
                    SectionID,
                    [Row],
                    SeatNumber,
                    SeatID 
                 FROM  dbo.rsSeats S
                 WHERE  ShowID = @ShowID
                 -- exclude seats on hold
                 AND NOT EXISTS (select 1 from rsHoldSeats HS 
                 WHERE HS.SeatID = S.SeatID) 
                 
              ) AS s
    CROSS APPLY (
                 SELECT SUM(Ranking) ,
                          MIN(SeatNumber),
                          MAX(SeatNumber)
                 FROM      dbo.rsSeats AS w
                 WHERE     w.ShowID = @ShowID
                          AND w.LevelID = s.LevelID
                          AND w.SectionID = s.SectionID
                          AND w.[Row] = s.[Row]
                          AND w.SeatNumber >= s.SeatNumber
                          AND w.SeatNumber <= s.SeatNumber + @WantedSeats - 1
                 HAVING     SUM(AvailCode) = 0
                          AND COUNT(*) = @WantedSeats
                 
              ) AS f(Ranking, theFrom, theTo)
    )
     SELECT top (@NumberOfBlocks) * from AvailableSeats ORDER BY Ranking, [Row] 
      
     END
    
    

     


    But the returned result is

     

    ShowID	LevelID	SectionID	Row	theFrom	theTo	Ranking
    1	1	2	A	1	3	30
    1	1	2	A	2	4	30
    1	1	2	A	3	5	30

    so I ended up with overlapping blocks. I presented it as a question to the group, but I think we want completely separate blocks. If that's the case, I will have to join with itself and the performance will degrade. Do you have suggestions along with the current query?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Friday, July 1, 2011 9:22 PM
    Friday, July 1, 2011 7:59 PM
  • I think I got the idea:

    SELECT top (@BlocksRet) Av.*, 
     DENSE_RANK() over (partition by TemplateID, ShowID, 
     LevelID, SectionID, Row ORDER BY Ranking, (theFrom-1)%@WantedSeats) as [Block_Rank]
      FROM AvailableSeats Av 
     
     ORDER BY Ranking, [Row], [Block_Rank]
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, July 1, 2011 10:48 PM
  • No, unfortunately not. And also, the DENSE_RANK() idea is wrong. With the code I got from Peter I can get list of different combinations, but I now need to filter these combinations based on the Ranking, Row and Centricity and also I need to avoid overlapping. So, I'm back to square one. I posted a new thread yesterday on this topic but I see that there are no takers :( Perhaps the problem is too complex.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 1:18 PM
  • I've tried to play with your idea, but I again can not get the desired result:

    ;with t1
    as
    (
    select top (3)
    	 showid
    	, levelid
    	, sectionid
    	, row
    	, seatNumber - (seatNumber % 3) block_start
    	, row_number() over (partition by showid, levelid, sectionid, row order by row) block_number,
    	MIN(SeatNumber) as mnNumber,
    	sum(Ranking) as rnkTotal,
    	SUM(Centricity) as cntTotal
    from dbo.rsSeats 
    group by showid
    	, levelid
    	, sectionid
    	, row
    	, seatNumber - (seatNumber % 3)
    having count(*) >= 3
    ORDER BY rnkTotal, Row, cntTotal	
    )
    
    select * from t1 
    

    I want to get 3 different blocks of best seats. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 1:35 PM
  • My knee-jerk reaction to this problem was to try to apply the nTile analytic; I will review what you have in a minute, but here is my first pass:

    ;with base_Set as

    ( select

        *,

        min(ranking) over

        ( partition by

            showId,

            levelId,

            sectionId,

            row

        ) as min_Ranking

      from @rsSeats

    )

    select

      showID,

      seatID,

      seatNumber,

      LevelID,

      SectionID,

      Row,

      theFrom,

      theTo,

      ranking,

      nTile(3) over

      ( partition by

            showId,

            levelId,

            sectionId,

            row,

            min_Ranking

        order by seatId

      ) as Block_Number

    from base_Set

     

    /* -------- Output: --------

    showID      seatID      seatNumber  LevelID     SectionID   Row  theFrom     theTo       ranking     Block_Number

    ----------- ----------- ----------- ----------- ----------- ---- ----------- ----------- ----------- --------------------

    1           27          1           1           2           A    1           3           30          1

    1           28          2           1           2           A    2           4           30          1

    1           29          3           1           2           A    3           5           30          1

    1           30          4           1           2           A    4           6           30          2

    1           31          5           1           2           A    5           7           30          2

    1           32          6           1           2           A    6           8           30          3

    1           33          7           1           2           A    7           9           30          3

     

    (7 row(s) affected)

    */

     EDIT:

    Sorry about the odd looking number of rows in the output; I intentially left out the last two rows of input to verify how nTile would handle "short blocks".

    EDIT:

    Where does "Centricity" come from?



    Thursday, July 7, 2011 1:50 PM
  • Thanks, Kent. ntile idea is very interesting, but a bit of complications that I need to select just 3 different blocks. I think when I start introducing TOP (N) and ORDER BY clause I got wrong results.

    BTW, here http://forum.lessthandot.com/viewtopic.php?f=17&t=14799 I attached the file that creates the tables and populates them with the sample data. The script may error out a bit in regards to Item_Id or Max4Sale, but it should create all relevant tables. I also provided a bit more info about the problem I'm trying to solve.

    I appreciate you giving me a hand, I'll try playing with the ntile idea. BTW, I suppose we can not use a variable in NTILE, e.g. NTILE (@WantedSeats) is not going to work? Although since I already create this SQL dynamically, it should not be a problem.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 2:02 PM
  • nTile works fine with a variable.  I will give your stuff an additional look.  I have a meeting shortly, so it might be a while before I can get back to you, but I will see what I can see.

     

    Thursday, July 7, 2011 2:07 PM
  • BTW, I never answered the above question - yes, the consecutive seats can have different ranking and they will of course have different centricity (and that's why my original idea about dense_rank() didn't work). In the sample data I had the ranking was the same and we introduced the centricity column later in the game.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 2:13 PM
  • We're almost there

     declare @ShowID int = 1, @WantedSeats smallint = 3, @BlocksRet tinyint = 3
     
    ;WITH AvailableSeats AS (SELECT 
                s.[SeatID],
    	          s.[TemplateID],
    	          s.[ShowID],
    						s.[LevelID],
    						s.[BlockID],
    						s.[SectionID],
    						s.[Row],
    						s.[SeatNumber],
    						s.[SeatLabel],
    						s.[Ranking],
    						s.[Centricity],
    						s.[IsHandicap],
    						s.[IsAisle],
    						s.[Obstructed],
    						s.[Note],	
    						s.[SeriesID],             
    						f.theFrom,
    						f.theTo,
    						f.TotalRanking,
    						f.TotalCentricity
    FROM           (
    				 SELECT  
                s.[SeatID],
    	          s.[TemplateID],
    	          s.[ShowID],
    						s.[LevelID],
    						s.[BlockID],
    						s.[SectionID],
    						s.[Row],
    						s.[SeatNumber],
    						s.[SeatLabel],
    						s.[Ranking],
    						s.[Centricity],
    						s.[IsHandicap],
    						s.[IsAisle],
    						s.[Obstructed],
    						s.[Note],	
    						s.[SeriesID] 
    				 FROM  dbo.rsSeats s
                 WHERE s.ShowID = @ShowID ) AS s
    CROSS APPLY (
                             SELECT SUM(Ranking), 
                                 SUM(Centricity),
                                  MIN(SeatNumber),
                                  MAX(SeatNumber)
                             FROM  dbo.rsSeats AS w
                             WHERE  w.ShowID = @ShowID
                                  AND w.LevelID = s.LevelID
                                  AND w.SectionID = s.SectionID
                                  AND w.[Row] = s.[Row]
                                  AND w.SeatNumber >= s.SeatNumber
                                  AND w.SeatNumber <= s.SeatNumber + @WantedSeats - 1
                             HAVING  SUM(AvailCode) = 0
                                  AND COUNT(*) = @WantedSeats
                             
                       ) AS f(TotalRanking, TotalCentricity, theFrom, theTo)
    )
     
     SELECT Av.*, 
                  NTILE(@WantedSeats) OVER (PARTITION BY TemplateID, ShowID, 
                  LevelID, SectionID, Row 
                  ORDER BY --(theFrom-1)%@WantedSeats,
                      TotalRanking, 
                      TotalCentricity
                      ) AS [Block_Rank]
      FROM AvailableSeats Av  
     ORDER BY [TotalRanking], [Row], [Block_Rank]
    

    Unfortunately, the seats are now not adjacent!


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 2:36 PM
  • Argghhh.  I've been over similar ground before.  I forgot about the adjacency issue.  I worked on one like this a few years ago with Uma in which nTile looked good up to this same adjacency point.

    I will do some more hunting.  (It's almost lunch break; I'll give it a go over lunch)


    Thursday, July 7, 2011 3:01 PM
  • Please try the below tsql-

    DECLARE @AdjSeatsCount AS INT = 3
    DECLARE @StartSeatNo AS INT = 1
    DECLARE @EndSeatNo AS INT = 100
    
    DECLARE @T TABLE (ShowID INT,	SeatID INT,	SeatNumber INT,	LevelID INT,	SectionID	INT, Row CHAR(1),	theFrom INT,	theTo	INT, Ranking INT)
    INSERT INTO @T
    SELECT 1,27,1,1,2,'A',1,3,30 UNION ALL
    SELECT 1,28,2,1,2,'A',2,4,30 UNION ALL
    SELECT 1,29,3,1,2,'A',3,5,30 UNION ALL
    SELECT 1,30,4,1,2,'A',4,6,30 UNION ALL
    SELECT 1,31,5,1,2,'A',5,7,30 UNION ALL
    SELECT 1,32,6,1,2,'A',6,8,30 UNION ALL
    SELECT 1,33,7,1,2,'A',7,9,30 UNION ALL
    SELECT 1,34,8,1,2,'A',8,10,30 UNION ALL
    SELECT 1,35,9,1,2,'A',9,11,30
    
    ;WITH CTE AS (
     SELECT @StartSeatNo FromSeatNo,@StartSeatNo + @AdjSeatsCount ToSeatNo
     UNION ALL
     SELECT FromSeatNo + @AdjSeatsCount, ToSeatNo + @AdjSeatsCount FROM CTE WHERE FromSeatNo < @EndSeatNo
    ), Block AS (
    SELECT 
     *,
     ROW_NUMBER() OVER(ORDER BY FromSeatNo) Rno 
    FROM 
     CTE
    )
    
    SELECT 
     T.*,
     'Block' + CONVERT(VARCHAR,B.Rno) Block 
    FROM 
     @T T
    INNER JOIN Block B
     ON B.FromSeatNo <= T.SeatNumber AND B.ToSeatNo > T.SeatNumber 
    
    

    This might give you an idea on how this could be done.
    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Thursday, July 7, 2011 3:19 PM
  • I have to tag "out" for now; I am just missing too much data and I cannot replicate the queries you are posting.

    Sorry

    :(

    Thursday, July 7, 2011 4:56 PM
  • What do you mean? I posted a script for the table in my other thread 'Complex Problem'. This is just the script to create that main table and I also posted there a script to populate just that table. For the problem we're only interested in this table - the supporting tables (Levels, Shows, Sections, Venues, etc. are not critical).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 7:15 PM
  • Hi Vinay,

    I don't only want to identify the blocks, I need to select 3 blocks of best seats. In your script I don't see any relation with the ranking and centricity.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 7, 2011 7:16 PM
  • Correct, I did not consider them, as the sample data was having same values for showid, levelid, sectionID, Row and Rank. Could you please come up with some kind of varied data that could help me in understanding the problem more.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, July 8, 2011 5:17 AM
  • Yes, I think so. I sent new scripts (including couple of simple SPs I had to re-write after last changes) to my colleagues, but haven't gotten any feedback yet.

    So, the whole morning I've been relaxing so far until working on couple of unrelated problems. We'll see how it goes and what changes I will need to introduce, but I think it's working fine now, thanks a lot for your help.

    BTW, I think it turned out to be a very interesting problem and your ideas were really ingenious. I think you may want to discuss this problem in a blog or introduce as a challenge.

    Also, I'm not 100% convinced that we want to use OR IS NULL approach over the dynamic SQL for creating #SeatCombos. I can always switch to dynamic - what do you think?

    The SP needs to work in SQL 2005 and up.

     

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, July 11, 2011 3:15 PM