MSSQL - Need to query multiple records into one record / set of results - based on Id and Date

Question

**Updated title..

First and foremost: I know it sounds like a repetitive question, but I've been scouring the web since yesterday afternoon for a solution.

I have 2 tables, this is my test stuff:

create table #UserMaster
(
    UserId int identity(1,1),
    FirstName varchar(50),
    LastName varchar(50),
    Department smallint,
    JobCode varchar(8),
);

create table #UserUpdateInfoMain
(
    Id int identity(1,1),
    UserId int,
    DateRequested smalldatetime,
    SubmittedBy varchar(20),
    RequestedBy varchar(20),
    NameChange varchar(50),
    TransferDept smallint,
    TransferJob varchar(8),
    TerminationDate date
);

Our HR department will submit requests for employee name changes, and transfer requests (for jobs or departments), and Termination requests. The idea behind this table (UserUpdatedInfoMain) is to house all such requests, and link them up as appropriate with the primary User information (in UserMaster). So, when we search for the user in the database, the idea is to be able to see their current last name, and current Department / Job assignment, and or if they've been terminated - also being able to see a trail of all such changes is a plus.

One request will come through for a name-change for Bob. A week later Bob may change positions, so a department transfer request will come in. I am trying to do a query to see: Bob -> NewName -> NewDept (one record, not multiple)

The trick of it is that there might be multiple name changes, and transfers, so I need to get the most recent of each one to show up in the query. I've found a few things that are somewhat similar that I can't seem to get to work (including using min/max - works great if it's just looking for one result), and I managed to get the results once along with every other row showing up at one point, just not able to discard all of the unnecessary results.

Here is the sample data that I'm playing with:

INSERT INTO #UserMaster (FirstName,LastName,Department,JobCode) 
values ('John','Smith',1212,'A1234'), --1 
('James','Todd',1232,'B2345'), --2 
('Steph','Williams',1212,'A1234'), --3 
('Casey','Bates',1212,'C2342'), --4 
('Rob','Johnson',3434,'A1234'), --5
('Cindy','Lou',2314,'A1234'), 
('Jesse','Dates',2323,'D3422'), 
('Apple','Cider',1342,'B2312'), 
('Pepsi','Cola',1432,'A1234'), 
('Random','Bob',1342,'C3421'), 
('Hulk','Hogan',3422,'C3221'), 
('John','Cena',3432,'B2231'), 
('Sasha','Banks',2321,'B2312'), 
('Jimmy','Iovine',3432,'A1234'),
('Dwayne','Johnson',1325,'C2342'), 
('Sydney','Pierce',1241,'A1234'), 
('Expo','Marks',4321,'B2312'), 
('Pat','Swizzle',2521,'C2342'), 
('Monkey','Bones',1212,'D3422'), 
('Happy','Gilmore',4545,'D3422'); 

INSERT INTO #UserUpdateInfoMain (UserId, DateRequested, SubmittedBy, RequestedBy, NameChange, TransferDept, TransferJob, TerminationDate)
values (1, cast(getdate()-5 as smalldatetime), 'rob', 'bob', 'Waters1', NULL, NULL, NULL),
(2, cast(getdate()-4 as smalldatetime), 'rob', 'bob', NULL, 7878, 'J7098', NULL),
(3, cast(getdate()-3 as smalldatetime), 'rob', 'bob', 'Cider1', NULL, NULL, NULL),
(4, cast(getdate()-4 as smalldatetime), 'rob', 'bob', NULL, NULL, NULL, cast(getdate()-3 as date)),
(5, cast(getdate()-2 as smalldatetime), 'rob', 'bob', NULL, 9898, NULL, NULL),
(1, cast(getdate()-3 as smalldatetime), 'jim', 'bob', 'Lakely2', NULL, NULL, NULL),
(1, cast(getdate() as smalldatetime), 'sue', 'bob', 'Salsa3', NULL, NULL, NULL),
(1, cast(getdate() as smalldatetime), 'sue', 'bob', NULL, 9648, 'K9487', NULL),
(2, cast(getdate()-1 as smalldatetime),'rosco', 'bob', 'Mordor1', NULL, NULL, NULL),
(2, cast(getdate() as smalldatetime), 'rosco', 'bob', 'Elves2', NULL, NULL, NULL);

Two examples that should show up ultimately are shown below...naturally, The other 3 should show up as well

(#UserUpdateInfoMain)

UserId    NameChange   TransferDept    TransferJob   TerminationDate
  1         Salsa3       9648           K9487         NULL 
  2         Elves2       9343           H8898         NULL

Apologies for the lengthy post, I just wanted to make sure I explained things decently and had a working example of what I'm playing with. I also apologize for not providing a link / example for everything I've looked out and done.

I tried doing a CTE for max date on each column and joining it with #UserUpdatedInfoMain table, but that didn't exactly work out; I'm not able to find the right combination to get them to work properly.

EXAMPLE:

theNameChanged_cte (UserId, Req_Date)
as
(
select
    i2.UserId,
    max(i2.DateRequested) as Req_Date
from #UserUpdateInfoMain i2
group by i2.UserId
)

...or...

theNameChanged_cte (UserId, Req_Date, NameChange)
as
(
select
    i2.UserId,
    max(i2.DateRequested) as Req_Date,
    i2.NameChange
from #UserUpdateInfoMain i2
group by i2.UserId, i2.NameChange
having i2.NameChange is not null
)

The first CTE legit just looks for the most recent date for the given UserId - okay cool...that's partway there. The second one pulls ALL of the NameChange requests, so some of the UserId's show up twice. If I could use MAX with a where statement, I think I could get this licked. I think my brain is just fried at this point unfortunately.

Any and all help, advice, and suggestions - even to "look at this type of function" would be appreciated (an actual solution would be baller! but I'm not above taking suggestions and working with them). I hope I got all the necessary information on this post to receive assistance.

score 0 · Accepted Answer · answered Aug 07 '20 at 23:56

0

Here's a SQL hack for you:

DECLARE
    @UserId INT, @NameChange VARCHAR(50), @TransferDept smallint, @TransferJob VARCHAR(8), @TerminationDate DATE

SET @UserId = 1;
SELECT
    @NameChange = CASE WHEN @NameChange IS NULL THEN NameChange ELSE @NameChange END,
    @TransferDept = CASE WHEN @TransferDept IS NULL THEN TransferDept ELSE @TransferDept END, 
    @TransferJob = CASE WHEN @TransferJob IS NULL THEN TransferJob ELSE @TransferJob END,
    @TerminationDate = CASE WHEN @TerminationDate IS NULL THEN TerminationDate ELSE @TerminationDate END
FROM #UserUpdateInfoMain ui
WHERE
    ui.UserId = @UserId
ORDER BY
    ui.Id DESC;

SELECT
    @UserId AS UserId,
    @NameChange AS NameChange,
    @TransferDept AS TransferDept,
    @TransferJob AS TransferJob,
    @TerminationDate AS TerminationDate;

Returns

+--------+------------+--------------+-------------+-----------------+
| UserId | NameChange | TransferDept | TransferJob | TerminationDate |
+--------+------------+--------------+-------------+-----------------+
|      1 | Salsa3     |         9648 | K9487       | NULL            |
+--------+------------+--------------+-------------+-----------------+

answered Aug 07 '20 at 23:56

critical_error

6,306
3
14
16

That is perfect!! Thank you so much! The next thing I get to do is stare at it and analyze it until I understand it (I'm okay with that) hahaha ...I mean, I get the gist, but it seems so backwards and twisty - I like it. Again, thank you so much for the quick resolution, and showing me something new to use and learn - You have no idea how much I appreciate this. – Robert Froese Aug 08 '20 at 13:49
Okay I understand the key piece - sort by Id of the table as the higher the value the more recent. Followed up by "when the variable is null, leave it and move on, but if not, then assign the variable the value". The second Select statement is the one that prints the values. I can research this, but if you have an answer I'm all ears. The first select statement. I'm struggling how it loops through the records - I mean, the only time the variable seems to get an assignment is when the variable is null, otherwise I can't even see the field being called...is it because of the variable name? – Robert Froese Aug 08 '20 at 14:00
It's because the field name is IN the case/when logic assignment - that's my understanding from these two posts: 1) [Microsoft Doc](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15) 2) [StackOverflow](https://stackoverflow.com/questions/10206325/query-with-variables) -- very last solution on this link. – Robert Froese Aug 08 '20 at 14:40
This technique is an undocumented "feature" of SQL server, in that you can assign a variable (e.g., @NameChange) over an entire rowset of data. By sorting descending on the Id column, you can ensure the most current values are presented first and combining that with the checking of NULL, once a variable captures the most recent value it will no longer set a new value. It's a feature that has been under threat of deprecation for a long time, but it still exists and it's saved me some hassle in the past, just like yours here. – critical_error Aug 09 '20 at 03:01

MSSQL - Need to query multiple records into one record / set of results - based on Id and Date

1 Answers1