April 10, 2012 at 11:08 am
I am having a table A with two columns RoomID and BedCount. The purpose of the application is to allot a bed to a patient from the room of his choice. When allotment is done, the bedCount is decremented by one.
Another table BedStatus is required to maintained for tracking each bed by giving an auto generated ID.
Before beginning allocation process all the beds have to be auto numbered by inserting to Table B. So If the bedCount=5, 5 rows have to be inserted. And when the allotment starts, Alloted column is updated to value 'Y'.
Sample data is shown below:
Table A
RoomID BedCount
101 5
102 2
105 3
Table B
BedAutoID is an Identity column
BedAutoID RoomNo Alloted
1 101 N
2 101 N
3 101 N
4 101 N
5 101 Y
6 102 N
7 102 N
8 105 N
9 105 N
10 105 Y
Is it possible to auto enumerate (insert) all beds records in Table B (BedCount times) by referring Table A using single query?
My approach is to write a stored procedure and looping to enumerate the records. Can any one provide a simpler method to this problem?
April 10, 2012 at 11:25 am
This?
DECLARE @TableA TABLE
(
RoomID INT,
BedCount INT
)
;
INSERT @TableA( RoomID ,BedCount)
SELECT 101, 5
UNION ALL SELECT 102, 2
UNION ALL SELECT 105, 3
; WITH Tens (N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
)
, Hundreds(N) AS
(
SELECT T1.N
FROM Tens T1
CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N
FROM Hundreds T1
CROSS JOIN Hundreds T2
)
, Millions(N) AS
(
SELECT T1.N
FROM Thousands T1
CROSS JOIN Hundreds T2
)
, Numbers(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM Millions
)
SELECT T.RoomID , T.BedCount
-- INTO TableB
FROM Numbers N
CROSS JOIN @TableA T
WHERE N.N <= T.BedCount
ORDER BY T.RoomID
April 10, 2012 at 11:25 am
it's not that difficult to do, but you need a Tally Table to do it.
try this and you'll see it's generating the values as requested, but you'll wnat to test it and see WHY it does it.
With TableA (RoomID,BedCount)
AS
(
SELECT 101,5 UNION ALL
SELECT 102,2 UNION ALL
SELECT 105,3
)
SELECT
row_number() over (order by RoomID) AS RW,
TableA.RoomID,
TableA.BedCount,
CASE
WHEN TableA.BedCount = MiniTally.N
THEN 'Y'
ELSE 'N'
END As ALLOTED,
*
FROM TableA
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM sys.columns) MiniTally
WHERE MiniTally.N between 1 and TableA.BedCount
order by
TableA.RoomID,MiniTally.N
Lowell
April 10, 2012 at 9:07 pm
Thanks to both Lowell and ColdCoffee. Both scripts looks promising. I will test and get back.:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply