Results 1 to 3 of 3

Thread: Help with query

  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Help with query

    I want to take this code and insert data on a monthly basis. For example all the data for month of August.


    INSERT INTO IISLOG
    ( ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department) SELECT
    ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department
    FROM IISLOG.DBO.IISLOG
    WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1
    GO

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    184
    Change your where clause

    WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1

    To this

    WHERE MONTH(LogTime)=8
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can ride an index, I'd use:
    Code:
    INSERT INTO IISLOG
    (  ClientHost, Username
    ,  Logtime, Service, Machine
    ,  ServerIP, Processingtime, Bytesrecvd
    ,  BytesSent, ServiceStatus, Win32status
    ,  Operation, Target, Parameters
    ,  Department) SELECT
       ClientHost, Username
    ,  Logtime, Service, Machine
    ,  ServerIP, Processingtime, Bytesrecvd
    ,  BytesSent, ServiceStatus, Win32status
    ,  Operation, Target, Parameters
    ,  Department 
       FROM IISLOG.DBO.IISLOG 
       WHERE  '2004-08-01' <= LogTime
          AND LogTime < '2004-09-01'
    GO
    This lets you ride an index if one exists on LogTime, which can improve your performance by literally orders of magnitude (things can take much less than one tenth as long as not riding the index).

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •