asked on

Need to get the last 3 rundates and users from the log table

I currently have the resultset as below:

Program Rundate Userid
---------------------------------------------------------------------
P:\Application2.rpt 2017-11-10 john
P:\Application2.rpt 2017-11-11 sam
P:\Application2.rpt 2017-11-13 sam

P:\Application4.rpt 2017-11-12 john
P:\Application4.rpt 2017-11-14 sam
P:\Application4.rpt 2017-11-15 luna

P:\Application1.rpt 2017-11-14 john
P:\Application1.rpt 2017-11-15 luna
P:\Application1.rpt 2017-11-16 sam

I need to get the resultset as shown below:

Program LastDateRun1 LastRunUser1 LastDateRun2 LastRunUser2 LastDateRun3 LastRunUser3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

P:\Application2.rpt 2017-11-10 john 2017-11-11 sam 2017-11-13 sam
P:\Application4.rpt 2017-11-12 john 2017-11-14 sam 2017-11-15 luna
P:\Application1.rpt 2017-11-14 john 2017-11-15 luna 2017-11-16 sam

Pawan Kumar

Please use this -

;WITH CTE1 AS
(
	select * , ROW_NUMBER() OVER (PARTITION BY Program ORDER BY DateRun desc) rnk
	from yourtableName
)
select Program
,MAX(CASE WHEN rnk = 1 THEN UserId END) LastRunUser1  
,MAX(CASE WHEN rnk = 1 THEN DateRun END) LastDateRun1 
,MAX(CASE WHEN rnk = 2 THEN UserId END) LastRunUser2  
,MAX(CASE WHEN rnk = 2 THEN DateRun END) LastDateRun2  
,MAX(CASE WHEN rnk = 3 THEN UserId END) LastRunUser3  
,MAX(CASE WHEN rnk = 3 THEN DateRun END) LastDateRun3
from CTE1
GROUP BY Program

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jen R

ASKER

Thank you very much Pawan:)...It is working great!

Pawan Kumar

Welcome , glad to help as always :)

We can also use PIVOT logic..

--
                        
;WITH CTE AS
(
	SELECT 154722 LogKey,'john' UserId     ,'P:\ApplicationReports2.rpt' Program ,'2017-11-10 16:01:11.000' DateRun UNION ALL
	SELECT 154723 ,'sam'     ,'P:\ApplicationReports2.rpt','2017-11-10 16:01:17.000' UNION ALL
	SELECT 154742 ,'sam'      ,'P:\ApplicationReports2.rpt','2017-11-13 09:00:44.000' UNION ALL
	SELECT 154721 ,'john'     ,'P:\ApplicationReports4.rpt','2017-11-10 16:01:11.000' UNION ALL
	SELECT 154726 ,'sam'      ,'P:\ApplicationReports4.rpt','2017-11-10 16:01:17.000' UNION ALL
	SELECT 154743 ,'sam'      ,'P:\ApplicationReports4.rpt','2017-11-13 09:00:44.000' UNION ALL
	SELECT 154728 ,'john'     ,'P:\ApplicationReports1.rpt','2017-11-10 16:01:11.000' UNION ALL
	SELECT 154727 ,'sam'      ,'P:\ApplicationReports1.rpt','2017-11-10 16:01:17.000' UNION ALL
	SELECT 154744 ,'sam'      ,'P:\ApplicationReports1.rpt','2017-11-13 09:00:44.000' 
)
,CTE1 AS
(
	select * , CONCAT('LastRunUser',ROW_NUMBER() OVER (PARTITION BY Program ORDER BY DateRun desc)) rnk
			 , CONCAT('LastDateRun',ROW_NUMBER() OVER (PARTITION BY Program ORDER BY DateRun desc)) rnk1
	from cte
)
SELECT Program,MAX([LastRunUser1]) [LastRunUser1] 
,MAX([LastDateRun1]) [LastDateRun1]
,MAX([LastRunUser2]) [LastRunUser2]
,MAX([LastDateRun2]) [LastDateRun2]
,MAX([LastRunUser3]) [LastRunUser3]
,MAX([LastDateRun3])  [LastDateRun3]
from CTE1
PIVOT
(	
	MAX(UserId) for rnk in ([LastRunUser1],[LastRunUser2],[LastRunUser3])
)g
PIVOT
(	
	MAX(DateRun) for rnk1 in ([LastDateRun1],[LastDateRun2],[LastDateRun3])
)g1
GROUP By Program

Open in new window

OUTPUT

/*------------------------
OUTPUT
------------------------*/
Program                    LastRunUser1 LastDateRun1            LastRunUser2 LastDateRun2            LastRunUser3 LastDateRun3
-------------------------- ------------ ----------------------- ------------ ----------------------- ------------ -----------------------
P:\ApplicationReports1.rpt sam          2017-11-13 09:00:44.000 sam          2017-11-10 16:01:17.000 john         2017-11-10 16:01:11.000
P:\ApplicationReports2.rpt sam          2017-11-13 09:00:44.000 sam          2017-11-10 16:01:17.000 john         2017-11-10 16:01:11.000
P:\ApplicationReports4.rpt sam          2017-11-13 09:00:44.000 sam          2017-11-10 16:01:17.000 john         2017-11-10 16:01:11.000


(3 row(s) affected)

Open in new window

Jen R

ASKER

Ok...Thank U