question

SourabhAgrawal-4229 avatar image
0 Votes"
SourabhAgrawal-4229 asked EchoLiu-msft edited

Select distinct record using max and create report

Hi All,
I have below table.91805-2021-04-27-10-54-03.jpg


I would like to create a report similar to below
91773-image.png




Please advise, thanks in advance.

sql-server-transact-sql
image.png (8.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 commented
 ;With cteType1 As
 (Select employeeID, dt, notes,
   Row_Number() Over(Partition By employeeID Order By dt Desc) As rn
 From YourTableName
 Where type = 1),
    
 cteType2 As
 (Select employeeID, dt, notes,
   Row_Number() Over(Partition By employeeID Order By dt Desc) As rn
 From YourTableName
 Where type = 2)
    
 Select IsNull(t1.employeeID, t2.employeeID) As employeeID,
    t1.dt As Type1Date, t1.notes,
    t2.dt As Type2Date, t2.notes
 From cteType1 t1
 Full Outer Join cteType2 t2 On t1.employeeID = t2.employeeID
   And t1.rn = 1 And t2.rn = 1
 Where IsNull(t1.rn, t2.rn) = 1;

Tom

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried this but it is not pulling correct data, it is pulling multiple emplyeeID, there should be only one row per employeeID.

0 Votes 0 ·

I corrected the query

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @SourabhAgrawal-4229,

Welcome to the microsoft TSQL Q&A forum!

Please refer to:

 CREATE TABLE #yourtable (id int,[employ id] int,dt date,[type] int,notes char(15))
 INSERT INTO #yourtable  VALUES
 (3000,24,'4/13/2021',1,'this is test'),(3001,24,'4/14/2021',2,'this is test2')
 ,(3002,24,'4/15/2021',1,'some date'),(3003,30,'4/16/2021',1,'some date2')
 ,(3004,24,'4/17/2021',1,'some date3'),(3005,24,'4/18/2021',2,'some date4')
 ,(3006,30,'4/19/2021',2,'some date5'),(3007,30,'4/20/2021',2,'some date6')

 ;WITH cte
 as(SELECT *,
    MAX(dt) OVER(PARTITION BY [employ id] ORDER BY [type]) [(MAX)Date of type]
    FROM #yourtable)
 ,cte1 as
   (SELECT * FROM cte
    WHERE dt=[(MAX)Date of type])
    
 SELECT c1.[employ id] [cust ID],c1.[(MAX)Date of type] [(MAX)Date of type1],c1.notes 
 ,c2.[(MAX)Date of type] [(MAX)Date of type2],c2.notes 
 FROM cte1 c1 JOIN cte1 c2
 ON c1.[employ id]=c2.[employ id] AND c1.[type]=1 AND c2.[type]=2

Or:

 ;WITH cte
 as(SELECT *,
    MAX(dt) OVER(PARTITION BY [employ id] ORDER BY [type]) [(MAX)Date of type]
    FROM #yourtable)
    
 SELECT c1.[employ id] [cust ID],c1.[(MAX)Date of type] [(MAX)Date of type1],c1.notes 
 ,c2.[(MAX)Date of type] [(MAX)Date of type2],c2.notes 
 FROM cte c1 JOIN cte c2
 ON c1.[employ id]=c2.[employ id] AND c1.[type]=1 AND c2.[type]=2
 WHERE c1.dt=c1.[(MAX)Date of type] AND c2.dt=c2.[(MAX)Date of type]

Output:
91817-image.png


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (5.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.