user130268 user130268 - 4 months ago 6
SQL Question

How to repeat all rows in a table for all the groups in another table?

I have the following example data:

IF OBJECT_ID(N'users') IS NOT NULL
DROP TABLE users;
GO

CREATE TABLE users
(
id INT PRIMARY KEY, -- user id
name NVARCHAR(10), -- user name
created_at DATETIME, -- when users were created
dept NVARCHAR(10) -- which department this user belongs to
);

IF OBJECT_ID(N'dates') IS NOT NULL
DROP TABLE dates;
GO

CREATE TABLE dates
(
id INT PRIMARY KEY,
month_start DATETIME,
next_month_start DATETIME
);


INSERT INTO dates
VALUES (1, '2016-08-01 00:00:00.000', '2016-09-01 00:00:00.000');
INSERT INTO dates
VALUES (2, '2016-07-01 00:00:00.000', '2016-08-01 00:00:00.000');
INSERT INTO dates
VALUES (3, '2016-06-01 00:00:00.000', '2016-07-01 00:00:00.000');
INSERT INTO dates
VALUES (4, '2016-05-01 00:00:00.000', '2016-06-01 00:00:00.000');
INSERT INTO dates
VALUES (5, '2016-04-01 00:00:00.000', '2016-05-01 00:00:00.000');
INSERT INTO dates
VALUES (6, '2016-03-01 00:00:00.000', '2016-04-01 00:00:00.000');
INSERT INTO dates
VALUES (7, '2016-02-01 00:00:00.000', '2016-03-01 00:00:00.000');

INSERT INTO users
VALUES (1, 'user1', '20160307', 'sales');
INSERT INTO users
VALUES (2, 'user2', '20160306', 'sales');
INSERT INTO users
VALUES (3, 'user3', '20160407', 'sales');
INSERT INTO users
VALUES (4, 'user4', '20160406', 'hr');
INSERT INTO users
VALUES (5, 'user5', '20160403', 'hr');
INSERT INTO users
VALUES (6, 'user6', '20160607', 'hr');
INSERT INTO users
VALUES (7, 'user7', '20160607', 'hr');

SELECT *
FROM dbo.users;
SELECT *
FROM dbo.dates;


It's just two simple tables. What I want is the following result (the record order is not relevant). So essentially I want to list how many users were created for each month in the
dates
table. If there are no users created for a specific month, list 0 as
users_created
.

/*
+----------+-------------------------+-------------------------+---------------+-------+
| dates_id | dates_start | dates_next_month_start | users_created | dept |
+----------+-------------------------+-------------------------+---------------+-------+
| 1 | 2016-08-01 00:00:00.000 | 2016-09-01 00:00:00.000 | 0 | sales |
| 2 | 2016-07-01 00:00:00.000 | 2016-08-01 00:00:00.000 | 0 | sales |
| 3 | 2016-06-01 00:00:00.000 | 2016-07-01 00:00:00.000 | 0 | sales |
| 4 | 2016-05-01 00:00:00.000 | 2016-06-01 00:00:00.000 | 0 | sales |
| 5 | 2016-04-01 00:00:00.000 | 2016-05-01 00:00:00.000 | 1 | sales |
| 6 | 2016-03-01 00:00:00.000 | 2016-04-01 00:00:00.000 | 2 | sales |
| 7 | 2016-02-01 00:00:00.000 | 2016-03-01 00:00:00.000 | 0 | sales |
| 1 | 2016-08-01 00:00:00.000 | 2016-09-01 00:00:00.000 | 0 | hr |
| 2 | 2016-07-01 00:00:00.000 | 2016-08-01 00:00:00.000 | 0 | hr |
| 3 | 2016-06-01 00:00:00.000 | 2016-07-01 00:00:00.000 | 0 | hr |
| 4 | 2016-05-01 00:00:00.000 | 2016-06-01 00:00:00.000 | 2 | hr |
| 5 | 2016-04-01 00:00:00.000 | 2016-05-01 00:00:00.000 | 0 | hr |
| 6 | 2016-03-01 00:00:00.000 | 2016-04-01 00:00:00.000 | 2 | hr |
| 7 | 2016-02-01 00:00:00.000 | 2016-03-01 00:00:00.000 | 0 | hr |
+----------+-------------------------+-------------------------+---------------+-------+

*/


Hope someone can point me the right direction. Thank you.

Answer

try this,

SELECT d.*, dp.dept, u.users_created
FROM dbo.dates d
CROSS APPLY ( SELECT DISTINCT dept FROM dbo.users ) as dp
OUTER APPLY ( SELECT COUNT(*) users_created 
              FROM dbo.users u 
              WHERE u.dept = dp.dept
              AND u.created_at >= d.month_start 
              AND u.created_at < d.next_month_start
            ) as u