June 25, 2013 at 1:31 pm
I need to build a report from a table that holds seat assignments and capacity that looks like the below, however they are looking for the report to contain the cube number and blanks if capacity is not met in a cube so for cube 333 there should be 2 entries and for cube 701 there would be 3 entries but only 1 of them would have a value for ID.
Looking for the best possible way to do this?
Thanks!
ID Cube Capacity
-------------------------
1 701 3
9 333 2
3 333 2
June 25, 2013 at 1:52 pm
There's not enough information to answer this. Please give more details, DDL, sample data and expected results based on it. For more information, read the article linked in my signature.
June 25, 2013 at 2:04 pm
you still will need occupied/empty records at the individual seat level; you might organize them in cubes, but your level of detail is deeper;
i'd also like to see the DDL for the example, this is interesting, kind of like assigning stadium seating (ie a single seat is related to a row and also a section, and you need to find contiguous seats together)
Lowell
June 25, 2013 at 2:18 pm
Thank you, all sample ddl is below
--will create the table
CREATE TABLE [dbo].[Seating](
[id] [int] NULL,
[cube] [int] NULL,
[capacity] [int] NULL
) ON [PRIMARY]
GO
--will insert 3 rows into the table
INSERT INTO dbo.Seating
( id, [cube], capacity )
VALUES ( 1, -- id - int
701, -- cube - int
3 -- capacity - int
)
GO
INSERT INTO dbo.Seating
( id, [cube], capacity )
VALUES ( 9, -- id - int
333, -- cube - int
2 -- capacity - int
)
GO
INSERT INTO dbo.Seating
( id, [cube], capacity )
VALUES ( 3, -- id - int
333, -- cube - int
2 -- capacity - int
)
GO
--will select the data in the table
SELECT [id]
,[cube]
,[capacity]
FROM [dbo].[Seating]
Result set expected would be 5 rows with the last two =
NULL7013
June 25, 2013 at 2:38 pm
There are probably some other ways to do this but this should work.
with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
Tally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
MyCubes as
(
select [cube], capacity
from Seating
group by [cube], capacity
)
, AllSeats as
(
select *
from MyCubes c
join Tally t on t.N <= c.capacity
)
, SeatsInOrder as
(
select ID, [cube], ROW_NUMBER() over(partition by [cube] order by id) as RowNum
from Seating
)
select s.ID, a.[cube], a.capacity
from AllSeats a
left join SeatsInOrder s on a.N = s.RowNum and a.[cube] = s.[cube]
order by a.[cube], isnull(s.RowNum, 9999)
You can read more about the tally table here. http://www.sqlservercentral.com/articles/62867/[/url]
There are probably some better ways to put this together. If nobody else posts another solution I will try to look at this again later tonight.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 9:12 am
That worked for me. Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply