December 19, 2017 at 12:17 pm
Hi,
From a table dbo.logtable with
id,
logindate,
action
, I’m trying to get only the most recent dates for a given id, I’ve been playing with it for hours, and I’m closer. But this is still returning several rows for a given id.
;WITH cte AS (
SELECT lg.id,
lg.logindate,
max( lg.logindate ) OVER ( PARTITION BY lg.id ) AS MostRecentDate,
lg.action
FROM dbo.logtable AS lg
)
SELECT t1.id,
t1.logindate,
t1.MostRecentDate,
t1.action,
t2.action AS e
FROM cte AS t1
JOIN cte t2 ON t1.id=t2.id AND t1.MostRecentDate=t2.logindate
WHERE t1.action = 'Login'
;
This is actually just a subquery, and I’m going to compare it to another subquery from table dbo.login. This subquery contains all of the login creations for a given id, from over a month ago.
I need to find anyone who has a login created over a month ago, but hasn’t logged in yet.
I already have the most recent logins from dbo.login (I think) for more than a month ago, but can’t seem to get this dbo.log part going.
What am I doing wrong?
December 19, 2017 at 12:21 pm
Please post the DDL (CREATE TABLE statement) for the table(s), sample data (as INSERT INTO statements) for the table(s) and the expected results based on the sample data. Test your scripts in an empty database to ensure that they work before posting. Once we have that I am sure someone will be able to provide you with tested code.
December 19, 2017 at 1:31 pm
Lynn Pettis - Tuesday, December 19, 2017 12:21 PMPlease post the DDL (CREATE TABLE statement) for the table(s), sample data (as INSERT INTO statements) for the table(s) and the expected results based on the sample data. Test your scripts in an empty database to ensure that they work before posting. Once we have that I am sure someone will be able to provide you with tested code.
Ok. I'll have to create one from scratch. The actual table I'm using has more fields than the simplified example I'm using above.
I've never had to do this, before.
December 19, 2017 at 2:07 pm
SqlServerCampbell - Tuesday, December 19, 2017 12:17 PMHi,From a table dbo.logtable with
id,
logindate,
action, I’m trying to get only the most recent dates for a given id, I’ve been playing with it for hours, and I’m closer. But this is still returning several rows for a given id.
;WITH cte AS (
SELECT lg.id,
lg.logindate,
max( lg.logindate ) OVER ( PARTITION BY lg.id ) AS MostRecentDate,
lg.action
FROM dbo.logtable AS lg
)
SELECT t1.id,
t1.logindate,
t1.MostRecentDate,
t1.action,
t2.action AS e
FROM cte AS t1
JOIN cte t2 ON t1.id=t2.id AND t1.MostRecentDate=t2.logindate
WHERE t1.action = 'Login'
;This is actually just a subquery, and I’m going to compare it to another subquery from table dbo.login. This subquery contains all of the login creations for a given id, from over a month ago.
I need to find anyone who has a login created over a month ago, but hasn’t logged in yet.
I already have the most recent logins from dbo.login (I think) for more than a month ago, but can’t seem to get this dbo.log part going.
What am I doing wrong?
Got it. I was making it more complicated than it needed to be. The answer is
SELECT
*
FROM
dbo.log AS T
WHERE
logindate =
(
SELECT
MAX(logindate)
FROM
dbo.og
WHERE
id = T.id
and
action = 'Login'
)
ORDER BY T.id
;
December 19, 2017 at 2:12 pm
SqlServerCampbell - Tuesday, December 19, 2017 2:07 PMSqlServerCampbell - Tuesday, December 19, 2017 12:17 PMHi,From a table dbo.logtable with
id,
logindate,
action, I’m trying to get only the most recent dates for a given id, I’ve been playing with it for hours, and I’m closer. But this is still returning several rows for a given id.
;WITH cte AS (
SELECT lg.id,
lg.logindate,
max( lg.logindate ) OVER ( PARTITION BY lg.id ) AS MostRecentDate,
lg.action
FROM dbo.logtable AS lg
)
SELECT t1.id,
t1.logindate,
t1.MostRecentDate,
t1.action,
t2.action AS e
FROM cte AS t1
JOIN cte t2 ON t1.id=t2.id AND t1.MostRecentDate=t2.logindate
WHERE t1.action = 'Login'
;This is actually just a subquery, and I’m going to compare it to another subquery from table dbo.login. This subquery contains all of the login creations for a given id, from over a month ago.
I need to find anyone who has a login created over a month ago, but hasn’t logged in yet.
I already have the most recent logins from dbo.login (I think) for more than a month ago, but can’t seem to get this dbo.log part going.
What am I doing wrong?
Got it. I was making it more complicated than it needed to be. The answer is
SELECT
*
FROM
dbo.log AS T
WHERE
logindate =
(
SELECT
MAX(logindate)
FROM
dbo.og
WHERE
id = T.id
and
action = 'Login'
)
ORDER BY T.id
;
I have often seen people solve their problem while setting up the sample data and expected results for others to work. It actually helps focus what you are trying to accomplish.
December 19, 2017 at 2:28 pm
SqlServerCampbell - Tuesday, December 19, 2017 2:07 PMGot it. I was making it more complicated than it needed to be. The answer is
SELECT
*
FROM
dbo.log AS T
WHERE
logindate =
(
SELECT
MAX(logindate)
FROM
dbo.og
WHERE
id = T.id
and
action = 'Login'
)
ORDER BY T.id
;
This might perform better, because it requires fewer scans of the base table.
WITH logs AS
(
SELECT *, MAX(CASE WHEN action = 'Login' THEN logindate END) OVER(PARTITION BY id) AS lastlogindate
FROM dbo.log
)
SELECT *
FROM logs
WHERE logindate = lastlogindate
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply