Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

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
0
RecipeDan
Asked:
RecipeDan
  • 6
  • 5
  • 4
  • +2
1 Solution
 
Dan CraciunIT ConsultantCommented:
SELECT UserName, UserID, LoginDate FROM table
WHERE DATEDIFF(year, LoginDate, '2013-11-01') >= 2
0
 
jmcmunnCommented:
Something like this should work...

SELECT * FROM UserTable WHERE LoginDate <  DATEADD(year, -2, GETDATE()) or loginDate is null
0
 
RecipeDanAuthor Commented:
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Anthony PerkinsCommented:
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

0
 
jmcmunnCommented:
Is it always October?  And I assume you don't want static dates as above?
0
 
RecipeDanAuthor Commented:
Correct it will not always be October and I do not want static dates.
0
 
jmcmunnCommented:
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)
0
 
Anthony PerkinsCommented:
You are better off using NOT EXISTS instead of NOT IN, as I showed you earlier on.
0
 
jmcmunnCommented:
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.
0
 
Anthony PerkinsCommented:
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()
0
 
jmcmunnCommented:
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.
0
 
Anthony PerkinsCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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)
    )
0
 
RecipeDanAuthor Commented:
Thank you I will try the suggestions.
0
 
RecipeDanAuthor Commented:
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
0
 
jmcmunnCommented:
And what is the result coming back?  How is it wrong, and what query did you try?
0
 
RecipeDanAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
I got exactly that result from my code/query with your sample data.  Not sure what other data issues you my be having:


if object_id('tempdb.dbo.#tablename') is not null
    drop table #tablename
create table #tablename (
    username varchar(30) null,
    userid varchar(30) null,
    logindate datetime null
    )
insert into #tablename values('Dan'     , 'D456',      '10/11/2013')
insert into #tablename values('Tom'    ,  'T821' ,     '10/23/2013')
insert into #tablename values('Tom'   ,   'T821'  ,    '9/5/2013')
insert into #tablename values('Joe'  ,    'J702'   ,   '10/7/2013')
insert into #tablename values('Tom' ,     'T821'    ,  '7/30/2013')
insert into #tablename values('Joe',      'J702'     , '5/21/2009')


SELECT *
FROM #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 #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)
    )


Result:
username      userid      logindate
Dan      D456      2013-10-11 00:00:00.000
Joe      J702      2013-10-07 00:00:00.000
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now