Post reply

TSQL Query

  • June 21, 2012 at 4:55 am

    #262753

    Hi all

    I am struggling to understand the following code

    SELECTCOUNT(*) AS UserCount, LogDate, LogTime ,dbo.fnBaanLogTrueDate(bl.LogDate, bl.LogTime) as LogDateTime

    INTOdbo.AX5loginhist

    FROMdbo.baanloginshist bl

    --GROUPBY UserCount, LogDate, LogTime

    --ORDER BY LOGDATETIME

    LEFT OUTER JOINAXV5_SYSUSERLOG on bl.LogDateTime --dbo.fnBaanLogTrueDate(LogDate, LogTime)

    BETWEENCREATEDDATETIME

    ANDLOGOUTDATETIME

    WHEREdbo.fnBaanLogTrueDate(LogDate, LogTime) > /*' 26/08/2009 '*/ (SELECT TOP 1 LogDateTime FROM AX5loginhist ORDER BY (LogDateTime) DESC)

    ANDUSERID not in ('DAXAO','xx01','Axapt')

    GROUPBY UserCount, LogDate, LogTime

    In particular I don't understand what the filter is doing in the ON predicate. How does the BETWEEN dates bit work?

    Thanks

  • June 21, 2012 at 5:18 am

    #1503869

    BETWEEN is another way of doing >= and <=

    So its instead of

    ON

    bl.LogDateTime >= CREATEDDATETIME

    AND

    bl.LogDateTime <= LOGOUTDATETIME

    http://msdn.microsoft.com/en-us/library/ms187922.aspx

  • June 21, 2012 at 5:21 am

    #1503871

    Hi - thanks for the reply

    What I don't understand though is what values is it applying as in what dates is it looking between?

  • June 21, 2012 at 5:31 am

    #1503873

    It will be the data from the table where ever the two columns live, more than likely AXV5_SYSUSERLOG or baanloginshist if you are looping the join back to itself.

    If you post sample data and DDL we would be able to help you out a bit further.

  • June 21, 2012 at 6:11 am

    #1503880

    Hi - Thanks I think I understand now, its doing the join when the bl.LogDateTime is between the dates in the SYSUSERLOG table.

    Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

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