Solved

SQL Date Difference

Posted on 2013-11-26
18
176 Views
Last Modified: 2013-12-23
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
Comment
Question by:RecipeDan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39679160
SELECT UserName, UserID, LoginDate FROM table
WHERE DATEDIFF(year, LoginDate, '2013-11-01') >= 2
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39679190
Something like this should work...

SELECT * FROM UserTable WHERE LoginDate <  DATEADD(year, -2, GETDATE()) or loginDate is null
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 39679426
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39679618
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
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39679644
Is it always October?  And I assume you don't want static dates as above?
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 39679758
Correct it will not always be October and I do not want static dates.
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39679782
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39681205
You are better off using NOT EXISTS instead of NOT IN, as I showed you earlier on.
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39681222
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39681241
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
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39681270
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39681369
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39681520
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
 
LVL 1

Author Comment

by:RecipeDan
ID: 39681541
Thank you I will try the suggestions.
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 39682201
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
 
LVL 12

Expert Comment

by:jmcmunn
ID: 39682265
And what is the result coming back?  How is it wrong, and what query did you try?
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 39682427
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39685324
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question