Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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 ║
╚═════╩══════════╩═══════╩══════╩══════╝
share|improve this answer
CREATE TABLE #slot (
    Day TINYINT,
    Time CHAR(4),
    Venue VARCHAR(10),
    Free INT
)
INSERT INTO #slot (Day, Time, Venue, Free) VALUES
(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)


CREATE TABLE #booking (
    Day TINYINT,
    Time CHAR(4),
    Venue VARCHAR(10),
    [User] VARCHAR(30)
)

INSERT INTO #booking (Day, Time, Venue, [User]) VALUES
(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')

SELECT
    s.Day,
    s.Time,
    s.Venue,
    s.Free,
    ISNULL(g.Used, 0) AS Used
FROM #slot s

    LEFT JOIN (
        SELECT
            Day,
            Time,
            Venue,
            COUNT(*) AS Used
        FROM #booking 
        GROUP BY
            Day,
            Time,
            Venue
    ) g
        ON g.Day = s.day
        AND g.Time = s.Time
        AND g.Venue = s.Venue

DROP TABLE #slot
DROP TABLE #booking
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.