GROUP BY The latest user but the latest date has no user

Question

I'm trying to do a report for my company, and I have to show who used every item of the list last. To simplify, my table looks like this:

Item Code | Usage Date | User Name 
00001     | 19/01/2020 | Person A
00001     | 05/01/2020 | Person A
00001     | 23/12/2019 | Person B
00002     | 09/01/2020 | Person C
etc

Now, my problem is the following: Sometimes, there is no user specified, making the table look more like this:

Item Code | Usage Date | User Name 
00001     | 19/01/2020 | 
00001     | 05/01/2020 | Person A
00001     | 23/12/2019 | Person B
00002     | 09/01/2020 | 
etc

And I have to GROUP my query BY Item Code with the latest date, but also the latest user that isn't empty, aka I want the final table to look like this:

Item Code | Usage Date | User Name 
00001     | 19/01/2020 | Person A
00002     | 09/01/2020 | 
etc

What I've tried:

A simple GROUP BY [User Name]
A temporary table with only the Item Code and the Usage Date, and JOINing the User Name afterwards
A nested SELECT statement to do the GROUP BY after the ORDER BY

May you please give me directions as to how I can solve this problem?

Kindly put query you have try so for. Give detail for required output. — Saket Yadav, Feb 05 '20 at 08:00
I wish it help you .[You can check group by with multiple columns](https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) — ienesciftci, Feb 05 '20 at 08:06

Cedersved · Answer 1 · 2020-02-05T08:40:01.077

0

How about this?

;WITH cte AS (
SELECT * FROM sourcetable
)

SELECT m_cte.[Item Code], s_cte.[UsageDate], s_cte.[User Name] FROM cte m_cte
CROSS APPLY (
    SELECT TOP 1 * FROM cte s_cte WHERE s_cte.[Item Code] = m_cte.[Item Code] ORDER BY [Usage Date] DESC
    ) s_cte
GROUP BY m_cte.[Item Code], s_cte.[UsageDate], s_cte.[User Name]

edited Feb 05 '20 at 08:40

answered Feb 05 '20 at 08:07

Cedersved

1,015
1
7
21

Thanks a lot, but there is missing data. Item 2, as it has no User specified at all, gets deleted and I don't get Item 1 with its latest Date, but rather with the latest Date linked to a User – PAT_The_Whale Feb 05 '20 at 08:20
Then just remove the WHERE clause line if you want that item for the empty user – Cedersved Feb 05 '20 at 08:24
Thanks but that doesn't solve my problem, as I now have multiple entries for the same Item. I've edited the question to show what output I need – PAT_The_Whale Feb 05 '20 at 08:28
Edited the answer, is that what you want? – Cedersved Feb 05 '20 at 08:40
It's closer, but not yet what I'm looking for. If we look at Item 1, we see that at Date 19/01/2020 (Let's call it Date1), there is no User and at Date 23/12/2019 (Let's call it Date2) there is a User (Person A). I need the User from Date2 to be listed as User for Date1 because Date1 has no User. If I'm unclear, please say so. And also, I am very grateful for your help! – PAT_The_Whale Feb 05 '20 at 08:48

score 0 · Accepted Answer · answered Feb 05 '20 at 08:48

With Row_Number and Partition by


Drop  table if exists #test

create table #test (
    ItemCode varchar(5),
    UsageDate date,
    UserName varchar(50)
);

INSERT INTO [#test] (ItemCode,UsageDate,UserName)
VALUES        
        ('00001','2020-01-19', null),
        ('00001','2020-01-05','Person A'),
        ('00001','2019-12-23','Person B'),
        ('00002','2020-01-09',NULL);
WITH qryDate AS (
select 
    *,
    ROW_NUMBER() OVER(Partition by ItemCode ORDER BY UsageDate desc) AS RnDate
from  #test 
),
qryUser AS (
select 
    *,
    ROW_NUMBER() OVER(Partition by ItemCode ORDER BY CASE WHEN NULLIF(UserName,'') IS NULL THEN 1 ELSE 0 END,UsageDate desc) AS RnUser
from  #test 
)
select qryDate.ItemCode,
       qryDate.UsageDate,
       qryUser.UserName 
from qryDate
inner join qryUser on qryDate.ItemCode = qryUser.ItemCode and qryDate.RnDate = 1 and qryUser.RnUser = 1

GROUP BY The latest user but the latest date has no user

2 Answers2