Puzzle-2017-03-14-FindUsers

/*

The following script create to table variables you can then use in your queries.

If you’re not familiar with table variables, they persist as long as your query window is open. Once it closes the table variables are gone.

At the bottom of this script you’ll seen an example where I use them to select rows from the @user table.

*/

-- User Table (variable)

DECLARE @User TABLE

(

userID int,

name Varchar(40),

phoneNumber Varchar(40)

)

-- UserHistory Table (variable)

DECLARE @UserHistory TABLE

(

userID int,

actionDate Date,

action Varchar(10)

)

-- Insert Users

INSERT INTO @User VALUES (10, 'Bob', '999-432-0987');

INSERT INTO @User VALUES (20, 'Jane', '989-555-1212');

INSERT INTO @User VALUES (30, 'Jenny', '999-867-5309');

INSERT INTO @User VALUES (40, 'Mark', '248-555-1223');

-- Insert UserHistory

INSERT INTO @UserHistory VALUES (10, '2017-01-30','Logged On');

INSERT INTO @UserHistory VALUES (10, '2016-12-30','Logged Off');

INSERT INTO @UserHistory VALUES (10, '2017-02-28','Audit');

INSERT INTO @UserHistory VALUES (10, '2017-03-01','Logged On');

INSERT INTO @UserHistory VALUES (10, '2017-03-02','Logged On');

INSERT INTO @UserHistory VALUES (20, '2017-01-30','Logged Off');

INSERT INTO @UserHistory VALUES (20, '2016-12-30','Logged Off');

INSERT INTO @UserHistory VALUES (20, '2017-01-29','Logged Off');

INSERT INTO @UserHistory VALUES (20, '2017-03-01','Audit');

INSERT INTO @UserHistory VALUES (30, '2016-01-30','Logged On');

INSERT INTO @UserHistory VALUES (30, '2016-12-30','Logged Off');

INSERT INTO @UserHistory VALUES (30, '2016-01-28','Audit');

INSERT INTO @UserHistory VALUES (30, '2016-03-01','Logged On');

INSERT INTO @UserHistory VALUES (40, '2017-01-30','Logged On');

INSERT INTO @UserHistory VALUES (40, '2016-12-30','Logged Off');

INSERT INTO @UserHistory VALUES (40, '2017-02-19','Audit');

INSERT INTO @UserHistory VALUES (40, '2017-03-01','Logged On');

INSERT INTO @UserHistory VALUES (40, '2017-03-03','Logged On');

INSERT INTO @UserHistory VALUES (40, '2017-03-04','Logged On');

-- Example using @user
 SELECT userID, 
        name, 
        phoneNumber
 FROM   @user;