Link to home
Start Free TrialLog in
Avatar of RecipeDan
RecipeDan

asked on

SQL Date Difference

Hello:

I have these three fields:

UserName
UserID
LoginDate

What am I trying to do is get a list of users in October who have never logged into the system for the past 2 years.

I am stuck on how to write something like this...

Dan
Avatar of Dan Craciun
Dan Craciun
Flag of Romania image

SELECT UserName, UserID, LoginDate FROM table
WHERE DATEDIFF(year, LoginDate, '2013-11-01') >= 2
Something like this should work...

SELECT * FROM UserTable WHERE LoginDate <  DATEADD(year, -2, GETDATE()) or loginDate is null
Avatar of RecipeDan
RecipeDan

ASKER

I need by month...in this case October.

For example

I have 5 people who logged into the system in October. I want to know how many of those 5 people never logged into the system during the past 2 years from the October date.
Something like this perhaps:
SELECT  *
FROM    YourTable t1
WHERE   t1.LoginDate BETWEEN '20131001' AND '20131031'
        AND NOT EXISTS ( SELECT 1
                         FROM   YourTable t2
                         WHERE  t1.UserID = t2.UserID
                                AND t2.LoginDate BETWEEN '20111001' AND '20130930' )

Open in new window

Is it always October?  And I assume you don't want static dates as above?
Correct it will not always be October and I do not want static dates.
Are you using a stored procedure where you are going to pass in a month or a start/end date for the period of "October"?  It comes down to something like this, with appropriate date values set for the start and the end...

SELECT  *
FROM    UserTable
WHERE LoginDate BETWEEN @StartDate AND @EndDate
        AND UserName not in
(SELECT UserName FROM UserTable WHERE LoginDate >= DATEADD(year, -2, @StartDate) AND LoginDate < @Startdate)
You are better off using NOT EXISTS instead of NOT IN, as I showed you earlier on.
Fair enough, performance won't be any worse than the NOT IN.  For this case, I do not believe there will be much of a performance impact either way.  I suppose I should say that the NOT EXISTS may be more performant depending on if the columns allow nulls or not.  Take your preference.
Of course you are right, as I suspect the table is small, less than say 100K  But for large tables you are in general better off using EXISTS than IN and the whole point of EE is that we are here to teach best practices and not just provide solution that "work".

So we should also be using a column that is indexed when comparing.  So if UserID is an integer and has an index that would be more appropriate than UserName.

And I should have added that fortunately the T-SQL optimizer is smart enough to treat IN() just like an EXISTS()
Agreed.  Unfortunately, the worst performance in this query will likely be the DateAdd, but I don't know of a cleaner way to do it offhand without a lot of other ugly (and probably less performant) T-sql and date manipulation.
You can set a local variable to the value as in:
DECLARE @OldestDate datetime = DATEADD(year, -2, @StartDate)

And then in the WHERE clause use:
WHERE LoginDate >= @OldestDate AND LoginDate < @Startdate)

But it really should not make a lot of difference if any.
Given that we're currently in November, I assume "October now but not always" means "previous month".

I strongly urge you to use >= and < when dealing with date and datetime data types to avoid missing rows and/or adding rows that should not match, especially if data type is changed later, for example, from datetime to datetime2.


SELECT *
FROM dbo.tablename t1
WHERE
    LoginDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND
    LoginDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
    NOT EXISTS(
        SELECT 1
        FROM dbo.tablename t2
        WHERE
            t2.UserID = t1.UserID AND
            t2.LoginDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 25, 0) AND
            t2.LoginDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
    )
Thank you I will try the suggestions.
It is not producing the same numbers as I calculated by hand.

 Here is an example data table

UserName UserID      LoginDate
Dan      D456      10/11/2013
Tom      T821      10/23/2013
Tom      T821      9/5/2013
Joe      J702      10/7/2013
Tom      T821      7/30/2013
Joe      J702      5/21/2009


In the end result should be:
UserName UserID      LoginDate
Dan      D456      10/11/2013
Joe      J702      10/7/2013
And what is the result coming back?  How is it wrong, and what query did you try?
I tried all of them and they came back with different results. I have a total of 8623 entries for October and the list should show 4591.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial