Sql-server – Merge and formatting of data from two tables

sql-server-2008

I have a table called Slot as follows with default data:

1st Table

================================
|  Day  | Time  | Venue | Free |
================================
|   1   | 0830  | RM 1  |  10  |
|   1   | 0830  | RM 2  |  10  |
|   1   | 1030  | RM 1  |  20  |
|   1   | 1030  | RM 2  |  20  |
|   2   | 0830  | RM 1  |  10  |
|   2   | 0830  | RM 2  |  10  |
|   2   | 1030  | RM 1  |  30  |
|   2   | 1030  | RM 2  |  30  |
================================

There is another table Booking with data that might come and go anytime but the column header is fixed:

2nd Table

===================================
|  Day  | Time  | Venue |  User   |
===================================
|   1   | 0830  | RM 1  |  Jill   |
|   1   | 0830  | RM 2  |  Jill   |
|   1   | 0830  | RM 1  |  Jack   |
|   1   | 0830  | RM 1  |  Mary   |
|   1   | 0830  | RM 2  |  Mary   |
|   1   | 0830  | RM 2  |  Jill   |
|   2   | 1030  | RM 1  |  Ken    |
|   2   | 1030  | RM 1  |  Ken    |
====================================

Based on the example data in the table Booking, how can I derive the following table?

3rd Table (This is what I wanted) 

=======================================
|  Day  | Time  | Venue | Free | Used |
=======================================
|   1   | 0830  | RM 1  |  10  |  3   |
|   1   | 0830  | RM 2  |  10  |  3   |
|   1   | 1030  | RM 1  |  20  |  0   |
|   1   | 1030  | RM 2  |  20  |  0   |
|   2   | 0830  | RM 1  |  10  |  0   |
|   2   | 0830  | RM 2  |  10  |  0   |
|   2   | 1030  | RM 1  |  30  |  2   |
|   2   | 1030  | RM 2  |  30  |  0   |
=======================================

I am able to retrieve the following table

4th Table

================================
|  Day  | Time  | Venue | Used |
================================
|   1   | 0830  | RM 1  |  3   |
|   1   | 0830  | RM 2  |  3   |
|   2   | 1030  | RM 1  |  2   |
================================

by using the following command

select 
    day, time, venue, COUNT(*) as Used
from 
    booking 
group by 
    day, time, venue 
order by 
    day asc, time asc, time asc

but find it hard to merge and get the 3rd table that I wanted.

Best Answer

Table creation and sample data script:

CREATE TABLE #Slot
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    Free    smallint NOT NULL CHECK (Free > 0),

    PRIMARY KEY ([Day], [Time], Venue)
);

INSERT #Slot
    ([Day], [Time], Venue, Free)
VALUES
    (1, '08:30', 'RM 1', 10),
    (1, '08:30', 'RM 2', 10),
    (1, '10:30', 'RM 1', 20),
    (1, '10:30', 'RM 2', 20),
    (2, '08:30', 'RM 1', 10),
    (2, '08:30', 'RM 2', 10),
    (2, '10:30', 'RM 1', 30),
    (2, '10:30', 'RM 2', 10);

CREATE TABLE #Booking
(
    [Day]   tinyint NOT NULL,
    [Time]  time(0) NOT NULL,
    Venue   char(4) NOT NULL,
    [User]  varchar(10) NOT NULL
);

INSERT #Booking
    ([Day], [Time], Venue, [User])
VALUES
    (1, '08:30', 'RM 1', 'Jill'),
    (1, '08:30', 'RM 2', 'Jill'),
    (1, '08:30', 'RM 1', 'Jack'),
    (1, '08:30', 'RM 1', 'Mary'),
    (1, '08:30', 'RM 2', 'Mary'),
    (1, '08:30', 'RM 2', 'Jill'),
    (2, '10:30', 'RM 1', 'Ken'),
    (2, '10:30', 'RM 1', 'Ken');

Query:

SELECT
    s.[Day],
    s.[Time],
    s.Venue,
    s.Free,
    Used =
    (
        SELECT COUNT_BIG(*)
        FROM #Booking AS b
        WHERE
            b.[Day] = s.[Day]
            AND b.[Time] = s.[Time]
            AND b.Venue = s.Venue
    )
FROM #Slot AS s
ORDER BY
    s.[Day],
    s.[Time],
    s.Venue;

Output:

╔═════╦══════════╦═══════╦══════╦══════╗
║ Day ║   Time   ║ Venue ║ Free ║ Used ║
╠═════╬══════════╬═══════╬══════╬══════╣
║   1 ║ 08:30:00 ║ RM 1  ║   10 ║    3 ║
║   1 ║ 08:30:00 ║ RM 2  ║   10 ║    3 ║
║   1 ║ 10:30:00 ║ RM 1  ║   20 ║    0 ║
║   1 ║ 10:30:00 ║ RM 2  ║   20 ║    0 ║
║   2 ║ 08:30:00 ║ RM 1  ║   10 ║    0 ║
║   2 ║ 08:30:00 ║ RM 2  ║   10 ║    0 ║
║   2 ║ 10:30:00 ║ RM 1  ║   30 ║    2 ║
║   2 ║ 10:30:00 ║ RM 2  ║   10 ║    0 ║
╚═════╩══════════╩═══════╩══════╩══════╝
Related Question