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
Jen RAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

0
Pawan KumarDatabase ExpertCommented:
Tested with old data.. it is working fine..

                        
;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 * , ROW_NUMBER() OVER (PARTITION BY Program ORDER BY DateRun desc) rnk
	from cte
)
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


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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jen RAuthor Commented:
Thank you very much Pawan:)...It is working great!
0
Pawan KumarDatabase ExpertCommented:
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

0
Jen RAuthor Commented:
Ok...Thank U
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.