Solved

SQL Date Difference

Posted on 2013-11-26
18
168 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

23 Experts available now in Live!

Get 1:1 Help Now