Solved

SQL Date Difference

Posted on 2013-11-26
18
172 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
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 34

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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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:ScottPletcher
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:
ScottPletcher 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now