Get value from next record. I think this is pretty basic but searching has not helped 1

[keun]

I have simple data in tblLog; three fields - Username, DateTime, Action

What I want is to return the "next" action for each user based on date. The notes I jotted for how I think this needs to be structured is:

Select Action for Min DateTime
where username = username and DateTime > DateTime

I know there is some alias and self-join to do here but I cannot figure it out. Can anyone help?

I joined this forum in 2005. I am still a hack.

 

[TamarGranor]

Try this, substituting appropriately. I'm assuming you have the specified UserName and DateTime in variables.

SELECT Action
  FROM tblLog
  WHERE UserName = @UserName
    AND DateTime = 
      (SELECT MIN(DateTime)
         FROM tblLog
         WHERE UserName = @UserName
           AND DateTime > @DateTime)

Tamar

 

[keun]

Thanks Tamar, I will try this (probably not until tomorrow). I am moving to MSSQL from Access so I am not familiar with "in variables" and the "@" symbol but I will do some reading!

I joined this forum in 2005. I am still a hack.

 

[gmmastros]

If you know the username and date, then you can simplify it to this...

Select Top 1 *
From   tblLog
Where  UserName = @UserName
       And DateTime > @DateTime
Order BY DateTime

Without checking, I suspect this query will return the same output as Tamar's, but this is probably execute a super tiny little bit faster.

Performance aside, I think this query will be a bit simpler to understand. Basically this will return exactly 1 row from the table where the username is "this" and the dateTime > "That"




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[keun]

Ahh. I see. I want this to run for every record. I want to know which order all of my users perform all actions. Then I'm going to perform some analysis around the behaviors.

I joined this forum in 2005. I am still a hack.

 

[keun]

This is what works in Access, any way to do it in MSSQL?

SELECT Action
[indent][/indent]FROM tblLog N
[indent][/indent]WHERE N.username=tblLog.username
AND N.DateTime=(SELECT Min(D.DateTime) FROM tblLog D WHERE D.username=tblLog.username AND D.DateTime>tblLog.DateTime))
AS Nexttype

I joined this forum in 2005. I am still a hack.

 

[gmmastros]

Try this:

; With Data As
(
  SELECT  Action, UserName, DateTime,
          Row_Number() Over (Partition By UserName Order By DateTime) As RowId
  FROM    tblLog N
  WHERE   N.username=tblLog.username
)
Select  A.UserName,
        A.Action As ThisAction,
        A.DateTime As ThisDateTime,
        B.Action As NextAction,
        B.DateTime As NextDateTime
From    Data As A
        Inner Join Data As B
		  On A.UserName = B.UserName
		  And A.RowId = B.RowId - 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[keun]

The way I reference my tables, instead of tblLog, is [customer].[dbo].[tblLog] - I do this because I do not know another way.

With that said, I get the following error:

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "customer.dbo.tblLog.username" could not be bound.

I have no idea what this means!

I joined this forum in 2005. I am still a hack.

 

[Olaf Doschke]

If you8 changed "FROM tblLog N" to "From [customer].[dbo].[tblLog] N" the alias name to reference [customer].[dbo].[tblLog] in all the rest of the code still is N, so of course [customer].[dbo].[tblLog].[customer] is unknown then.

You can tak ethe code as is, if the current database is customer the dbo is the default schema and so tblLog is enough to reference the table. You should only change code given to you, if it needs to be adapted, not before testing it. I'm sure George's code will work ok.

The thing to know is FROM database.dbo.TABLENAME Alias is short for FROM database.dbo.Tablename AS Alias and is renaming the table for the current query, so temporarily this table now is called Alias. This helps in case you do self joins (join a table with itself) and still need to reference fields of the table on the left or right side of the join. More often this feature is used to simply shorten the query. George used a single letter N, because that's of course very short.

Bye, Olaf.

 

[gmmastros]

Actually... I didn't even notice the N until now. It was part of the code that works in Access.

Try this slight variation.

; With Data As
(
  SELECT  Action, UserName, DateTime,
          Row_Number() Over (Partition By UserName Order By DateTime) As RowId
  FROM    [customer].[dbo].[tblLog]
)
Select  A.UserName,
        A.Action As ThisAction,
        A.DateTime As ThisDateTime,
        B.Action As NextAction,
        B.DateTime As NextDateTime
From    Data As A
        Inner Join Data As B
		  On A.UserName = B.UserName
		  And A.RowId = B.RowId - 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

And actually I notice now the N isn't used in the rest of the CTE definition.

Anyway, now it's already adapted to your fully qualified (long) name of the table including schema dbo and database customer.

Bye, Olaf.

 

[gbaughma]

George is on the right track (IMHO); using the RowID has worked for me in the past.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[yelworcm]

What version of SQL Server are you using? If you are using SQL 2012 (I want to say enterprise edition, but I can't be sure) you can use the LEAD and LAG functions similar to the ROW_NUMBER function.

SELECT  Action, UserName, DateTime,
          LEAD(Action, 1) Over (Partition By UserName Order By DateTime) As "NEXT ACTION"
  FROM    [customer].[dbo].[tblLog]

I don't have a server handy, so this will need some testing.

 

[keun]

I am running 2008, so Lead and Lag are not invented yet!

I joined this forum in 2005. I am still a hack.