Convert SQL script to MySQl script

Question

I am new to MYSQL and I have a script that I have crated in SQL and need to convert that script into a MySQL script:

;WITH cte (id, ApplicationName, ActivityDate, username) AS 
(
    SELECT
          id
        , ApplicationName
        , ActivityDate
        , ROW_NUMBER() OVER (PARTITION BY ApplicationName ORDER BY ActivityDate) username
    FROM #temp
    WHERE ApplicationName IN ('Login', 'Logout')
)
SELECT * 
FROM #temp O 
INNER JOIN (
    SELECT 
          A1.id [IID]
        , A2.id [OID]
        , A2.ActivityDate 
    FROM cte A1 
    INNER JOIN cte A2 ON A1.username = A2.username 
        AND A1.ApplicationName = 'Login' 
        AND A2.ApplicationName = 'logout'
) Logout ON Logout.[IID] < O.id AND Logout.[OID] > o.id
WHERE NOT O.ApplicationName IN ('Login', 'Logout')
ORDER BY O.id

Code of the #temp table:

CREATE TABLE #temp (
      id INT IDENTITY (1, 1)
    , ActivityName VARCHAR(MAX)
    , ApplicationName VARCHAR(100)
    , ActivityDate DATETIME
    , UserName VARCHAR(20)
)

INSERT INTO #temp
    VALUES ('Successfully Login into Dynamic Portal Application', 'Login', '2013-05-20 13:22:21', 'kk'), 
    ('Login Success into Liferay SAMl Application', 'Liferay SAMl Application', '2013-05-21 15:28:08', 'kk'), 
    ('Login Success into Zoho SAMl Application', 'Zoho SAMl Application', '2013-05-21 15:28:08', 'kk'),
    ('Login Success into Intranet Non SAMl Application', 'Intranet Non SAMl Application', '2013-05-23 13:04:22', 'kk'), 
    ('Logout Successfully from application', 'Logout', '2013-05-21 18:20:07', 'kk'), 
    ('Successfully Login into Dynamic Portal Application', 'Login', '2013-05-20 14:22:21', 'kk'), 
    ('Login Success into Liferay SAMl Application', 'Liferay SAMl Application', '2013-05-21 16:28:08', 'kk'),
    ('Login Success into Zoho SAMl Application', 'Zoho SAMl Application', '2013-05-21 16:29:08', 'kk'), 
    ('Login Success into Intranet Non SAMl Application', 'Intranet Non SAMl Application', '2013-05-21 15:04:22', 'kk'), 
    ('Logout Successfully from application', 'Logout', '2013-05-21 19:20:07', 'kk')

Desired Output:

ActivityName                  LoginTime            LogOutTime
Liferay SAMl Application      2013-05-21 15:28:08  2013-05-21 18:20:07
Zoho SAMl Application         2013-05-21 15:28:08  2013-05-21 18:20:07
Intranet Non SAMl Application 2013-05-23 13:04:22  2013-05-21 18:20:07
Liferay SAMl Application      2013-05-21 16:28:08  2013-05-21 19:20:07
Zoho SAMl Application         2013-05-21 16:29:08  2013-05-21 19:20:07
Intranet Non SAMl Application 2013-05-21 15:04:22  2013-05-21 19:20:07

LogOut time of the Activities will be the logout time of application....

I's it just me or does anyone else want to know what "Zoho SAMl" is? — Rohan Büchner
– Rohan Büchner, Commented May 28, 2013 at 6:24

Devart · Accepted Answer · 2013-05-28 12:14:06Z

3

Try this one -

Query:

CREATE TABLE temp (
  ID int AUTO_INCREMENT PRIMARY KEY,
  ActivityName varchar(500),
  ApplicationName varchar(100),
  ActivityDate datetime,
  UserName varchar(20)
);

INSERT INTO temp (ActivityName, ApplicationName, ActivityDate, UserName)
  VALUES ('Successfully Login into Dynamic Portal Application', 'Login', '2013-05-20 13:22:21', 'kk'),
  ('Login Success into Liferay SAMl Application', 'Liferay SAMl Application', '2013-05-21 15:28:08', 'kk'),
  ('Login Success into Zoho SAMl Application', 'Zoho SAMl Application', '2013-05-21 15:28:08', 'kk'),
  ('Login Success into Intranet Non SAMl Application', 'Intranet Non SAMl Application', '2013-05-23 13:04:22', 'kk'),
  ('Logout Successfully from application', 'Logout', '2013-05-21 18:20:07', 'kk'),
  ('Successfully Login into Dynamic Portal Application', 'Login', '2013-05-20 14:22:21', 'kk10'),
  ('Login Success into Liferay SAMl Application', 'Liferay SAMl Application', '2013-05-21 16:28:08', 'kk10'),
  ('Login Success into Zoho SAMl Application', 'Zoho SAMl Application', '2013-05-21 16:29:08', 'kk10'),
  ('Login Success into Intranet Non SAMl Application', 'Intranet Non SAMl Application', '2013-05-21 15:04:22', 'kk10'),
  ('Logout Successfully from application', 'Logout', '2013-05-21 19:20:07', 'kk10');

SELECT t_.ApplicationName, t_.ActivityDate, t2_.ActivityDate
FROM temp t_
JOIN (
    SELECT 
          MAX(CASE WHEN t.ApplicationName = 'Login' THEN t.ID END) AS IID
        , MAX(CASE WHEN t.ApplicationName = 'Logout' THEN t.ID END) AS OID
        , MAX(t.ActivityDate) AS ActivityDate
    FROM temp t
    WHERE t.ApplicationName IN ('Login', 'Logout')
    GROUP BY t.UserName
) t2_ ON t2_.IID < t_.ID AND t2_.OID > t_.ID
WHERE t_.ApplicationName NOT IN ('Login', 'Logout')
ORDER BY t_.ID

Output:

ApplicationName                 ActivityDate            ActivityDate
------------------------------- ----------------------- -----------------------
Liferay SAMl Application        2013-05-21 15:28:08.000 2013-05-21 18:20:07.000
Zoho SAMl Application           2013-05-21 15:28:08.000 2013-05-21 18:20:07.000
Intranet Non SAMl Application   2013-05-23 13:04:22.000 2013-05-21 18:20:07.000
Liferay SAMl Application        2013-05-21 16:28:08.000 2013-05-21 19:20:07.000
Zoho SAMl Application           2013-05-21 16:29:08.000 2013-05-21 19:20:07.000
Intranet Non SAMl Application   2013-05-21 15:04:22.000 2013-05-21 19:20:07.000

Update:

SELECT t_.ApplicationName, t_.ActivityDate, t2_.ActivityDate
FROM temp t_
JOIN (
    SELECT 
          d1.ID AS IID
        , d2.ID AS OID
        , d2.ActivityDate
        , d2.UserName 
    FROM ( 
        SELECT  
              id
            , l.UserName
            , @curRow := @curRow + 1 AS row_number
        FROM temp l
        JOIN (SELECT @curRow := 0) r
        WHERE ApplicationName = 'Login'
    ) d1
    JOIN (
        SELECT  
              id
            , ActivityDate
            , l.UserName
            , @curRow2 := @curRow2 + 1 AS row_number
        FROM temp l
        JOIN (SELECT @curRow2 := 0) r
        WHERE ApplicationName = 'Logout'
    ) d2 ON d1.row_number = d2.row_number AND d1.UserName = d2.UserName
) t2_ ON t2_.IID < t_.ID AND t2_.OID > t_.ID AND t_.UserName = t2_.UserName
WHERE t_.ApplicationName NOT IN ('Login', 'Logout')
ORDER BY t_.ID

edited May 28, 2013 at 12:14

answered May 28, 2013 at 6:33

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kapil Singh Over a year ago

Devart this script is not working properly as if a same user gets login more than once then it shows only a single entry for different different applications as you have used max in your query...

Devart Over a year ago

@Kapil Singh, for the data you provided as an example, I wrote an optimal query. Please change the input data and correct your question. And on future, please, respect people's time.

Kapil Singh Over a year ago

I apologize for that I forget to mention that scenario..I have edit the values that will be inserted into the table

Devart Over a year ago

Hurray! :) I glad to assist you.

Devart Over a year ago

If this solution fully suit you, please approve it.

Collectives™ on Stack Overflow

Convert SQL script to MySQl script

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related