• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 84
  • Last Modified:

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
0
Jen R
Asked:
Jen R
  • 3
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now