Post reply

Calculating date

  • September 6, 2012 at 5:02 pm

    #267364

    I have a table as follows

    Logtable

    ID Date User action

    1 8/6/2012 12:10:36 A Something

    2 8/6/2012 12:09:34 A Something

    3 8/6/2012 12:09:33 A Something

    how can i select 8/6/2012 12:10:33 in this table instead of the other two dates for the user A with action something and not with the ID i need to compare the date and get the first time logged into the table

  • September 6, 2012 at 7:03 pm

    #1534809

    First item the time you say you need to pick is

    8/6/2012 12:10:33

    that time does NOT exist in the data you have listed. That said is this what you require ?

    CREATE TABLE #T(ID INT, D DATETIME,U VARCHAR(5),Ac VARCHAR(20))

    INSERT INTO #T

    SELECT 1, '8/6/2012 12:10:36','A','Something' UNION ALL

    SELECT 2, '8/6/2012 12:09:34','A','Something' UNION ALL

    SELECT 3, '8/6/2012 12:09:33','A','Something'

    SELECT ID,D,U,Ac FROM #T WHERE D IN (SELECT MIN(D) FROM #t)

    Result:

    IDD U Ac

    32012-08-06 12:09:33.000ASomething

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 6, 2012 at 7:03 pm

    #1534810

    SQLTestUser (9/6/2012)

    I have a table as follows

    Logtable

    ID Date User action

    1 8/6/2012 12:10:36 A Something

    2 8/6/2012 12:09:34 A Something

    3 8/6/2012 12:09:33 A Something

    how can i select 8/6/2012 12:10:33 in this table instead of the other two dates for the user A with action something and not with the ID i need to compare the date and get the first time logged into the table

    Normally the forum requests that you provide DDL and sample data in a consumable format like this:

    DECLARE @t TABLE (ID INT, Date DATETIME, [User] VARCHAR(10), Action VARCHAR(10))

    INSERT INTO @t

    SELECT 1, '8/6/2012 12:10:36', 'A', 'Something'

    UNION ALL SELECT 2, '8/6/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 3, '8/6/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 4, '8/6/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 5, '8/6/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 6, '8/6/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 7, '8/7/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 8, '8/7/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 9, '8/7/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 10, '8/7/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 11, '8/7/2012 12:09:33', 'B', 'Something'

    This then politely allows our volunteer members to focus on a solution:

    -- First login by user

    ;WITH FirstLogin AS (

    SELECT ID, Date, [User], Action

    ,rn=ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY Date)

    FROM @t)

    SELECT ID, Date, [User], Action

    FROM FirstLogin

    WHERE rn=1

    -- First login by user by date

    ;WITH FirstLogin AS (

    SELECT ID, Date, [User], Action

    ,rn=ROW_NUMBER() OVER (

    PARTITION BY [User], DATEADD(d, DATEDIFF(d, 0, Date), 0) ORDER BY Date)

    FROM @t)

    SELECT ID, Date, [User], Action

    FROM FirstLogin

    WHERE rn=1

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • September 6, 2012 at 7:34 pm

    #1534816

    thanks for the reply i also have to make sure that the date is between last two days or 10 days as

    ID Date User action

    1 8/4/2012 12:10:36 A Something

    2 8/4/2012 12:09:34 A Something

    3 8/4/2012 12:09:33 A Something

    SELECT *

    FROM Log

    WHERE (time IN

    (SELECT MIN(Time)

    FROM Log AS T))

    AND (DATEDIFF(d, T.Time, GETDATE()) = 2)

    and aciton = 'something' but this doesnot result in anything

    so that the result is displayed for everything that is the min date for a particular action

  • September 6, 2012 at 8:42 pm

    #1534824

    SQLTestUser (9/6/2012)

    thanks for the reply i also have to make sure that the date is between last two days or 10 days as

    ID Date User action

    1 8/4/2012 12:10:36 A Something

    2 8/4/2012 12:09:34 A Something

    3 8/4/2012 12:09:33 A Something

    SELECT *

    FROM Log

    WHERE (time IN

    (SELECT MIN(Time)

    FROM Log AS T))

    AND (DATEDIFF(d, T.Time, GETDATE()) = 2)

    and aciton = 'something' but this doesnot result in anything

    so that the result is displayed for everything that is the min date for a particular action

    BWAAA-HAAA!!! Do you really expect 8/4/2012 to be just 2 days ago with today being 9/6/2012? 😀

    Shifting gears, folks really do like to prove their code before posting it and, as a result, you get a better answer quicker. To help us help you more quickly, we need your data, as simple as it is, in a readily consumable format. Please read the first link in my signature line below for an easy way to do that in the future.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 7, 2012 at 5:58 am

    #1535019

    You can try the same as Mr. Ten Centuries suggested with some modification

    create TABLE t1 (ID INT, Date DATETIME, [User] VARCHAR(10), Action VARCHAR(10))

    INSERT INTO t1

    SELECT 22, '8/16/2012 12:10:36', 'A', 'Something'

    UNION ALL SELECT 2, '8/6/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 3, '8/6/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 4, '8/6/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 5, '8/6/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 6, '8/6/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 7, '8/7/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 8, '8/7/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 9, '8/7/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 10, '8/7/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 11, '8/7/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 12, '9/7/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 13, '9/5/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 14, '9/5/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 15, '9/3/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 16, '9/3/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 17, '9/3/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 18, '9/3/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 19, '8/17/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 20, '8/17/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 21, '8/17/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 22, '8/16/2012 12:10:36', 'A', 'Something'

    -- First login by user by date

    ;WITH FirstLogin AS (

    SELECT ID, Date, [User], Action

    ,rn=ROW_NUMBER() OVER (

    PARTITION BY [User], DATEADD(d, DATEDIFF(d, 0, Date), 0) ORDER BY Date)

    FROM t1)

    SELECT ID, Date, [User], Action

    FROM FirstLogin

    WHERE rn=1 and Date > DATEADD(DD,-2,GETDATE()) order by Date desc -- FOR 2 Days

    --WHERE rn=1 and Date > DATEADD(DD,-10,GETDATE()) order by Date desc -- FOR 10 Days

    --WHERE rn=1 and Date > DATEADD(DD,-20,GETDATE()) order by Date desc -- FOR 20 Days

    You can go ahead with the changing the number days at your concern here I considered as 2.

    Sumit Rastogi;-)

  • September 8, 2012 at 7:42 pm

    #1535442

    I don't know if i am missing something. :hehe:

    But we should be able to achieve desired result(Last 10 Days) via below

    Select user,min(action)

    from logtable

    where action >=convert(varchar(11,getdate()-10,106)

    group by User

    Or if you want to get first log in time for each day in last 10 days

    Select

    user,cast(convert(varchar(11),Action,106) as datetime) as [Date],min(Action) as [Action Time] from logtable where Action>=convert(varchar(11,getdate()-10,106)

    group by cast(convert(varchar(11,Action,106) as datetime), User

  • September 14, 2012 at 6:47 am

    #1537785

    This query will not give correct output in this case

    create TABLE t1 (ID INT, Date DATETIME, [User] VARCHAR(10), Action VARCHAR(10))

    INSERT INTO t1

    SELECT 22, '8/16/2012 12:10:36', 'A', 'Something'

    UNION ALL SELECT 2, '8/6/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 3, '8/6/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 4, '8/6/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 5, '8/6/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 6, '8/6/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 7, '8/7/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 8, '8/7/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 9, '8/7/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 10, '8/7/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 11, '8/7/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 12, '9/14/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 13, '9/6/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 14, '9/5/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 15, '9/3/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 16, '9/9/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 17, '9/3/2012 12:09:34', 'A', 'Something'

    UNION ALL SELECT 18, '9/3/2012 12:09:33', 'A', 'Something'

    UNION ALL SELECT 19, '8/17/2012 12:10:36', 'B', 'Something'

    UNION ALL SELECT 20, '9/13/2012 12:09:34', 'B', 'Something'

    UNION ALL SELECT 21, '9/14/2012 12:09:33', 'B', 'Something'

    UNION ALL SELECT 22, '8/16/2012 12:10:36', 'A', 'Something'

    Try this one:

    ;WITH FirstLogin AS (

    SELECT ID, Date, [User], Action,DATEADD(d, DATEDIFF(d, 0, Date), 0) [D]

    ,rn=ROW_NUMBER() OVER (

    PARTITION BY [User] ORDER BY Date)

    FROM t1 WHERE Date > DATEADD(DD,-2,GETDATE())

    )

    SELECT ID, Date, [User], Action

    FROM FirstLogin

    WHERE rn=1 order by Date desc

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply