Hi All,
I have below table.
I would like to create a report similar to below 
Please advise, thanks in advance.
Hi All,
I have below table.
I would like to create a report similar to below 
Please advise, thanks in advance.
;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
I tried this but it is not pulling correct data, it is pulling multiple emplyeeID, there should be only one row per employeeID.
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:
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.
11 people are following this question.