Results 1 to 13 of 13

Thread: Help on a query

  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Help on a query

    Which is:

    SELECT DATEPART(m, Logtime), DATEPART(d, Logtime), DATEPART(yy, logtime)
    From IISLOG

    Then I need it to group by year:

    GROUP BY (year, logtime)

    Then I get this error:

    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ')'.

    Can someone help.

    Thanks

    Lystra

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Did you mean to GROUP BY DATEPART(year, logtime) or ORDER BY DATEPART(year,logtime)?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm, I hate to be a fuddy-duddy, but if you are grouping by year it seems tough to figure out what to display for month and day... Once you start grouping in a result set, that grouping level sort of defines the "bargain basement" for detail.

    If you group by year, then you could include the year in the select list, but not the month or the day. If that was what you wanted, your query would become something like:
    Code:
    SELECT DatePart(yy, Logtime)
       FROM IISLOG
       GROUP BY DatePart(yy, Logtime)
    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I still think the poster ment ORDER BY, because GROUP BY would not make any sense.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't have a clear understanding of what Lystra wanted, but maybe tomorrow she'll explain in more detail and then we'll all know. I just took my best guess at what I thought she wanted.

    -PatP

  6. #6
    Join Date
    Jul 2004
    Posts
    191
    I have a table that logs traffic in from the Intranet. There is a job that is supose to archive the any data from the previous day, come to find out the job is not working. because there is data from 2003 and 04 about 6million records.

    I was using group by because I wanted to see the total records for the 2 years. Group by doesn't work and Order by is not what I need.

    So I used:

    SELECT *,
    DATEPART(m, Logtime), DATEPART(d, Logtime), DATEPART(yy, Logtime)
    FROM IISLOG
    Where DATEPART(yy, Logtime)= 2003

    Which gave me the total record for that year.

    Now I am trying to move that data from the current table and archive it with this script:

    INSERT INTO IISLOG
    (id,
    ClientHost,
    Username,
    Logtime,
    Service,
    Machine,
    ServerIP,
    Processingtime,
    Bytesrecvd,
    BytesSent,
    ServiceStatus,
    Win32status,
    Operation,
    Target,
    Parameters,
    Department)
    SELECT id,
    ClientHost,
    Username,
    Logtime,
    Service,
    Machine,
    ServerIP,
    Processingtime,
    Bytesrecvd,
    BytesSent,
    ServiceStatus,
    Win32status,
    Operation,
    Target,
    Parameters,
    Department
    FROM IISLOG.DBO.IISLOG
    Where Logtime = 10/13/2003
    GO

    When I just try the select statement alone the results are zero.

    What I am I doing wrong?

    In the table the data for Logtime is '2003-10-13 00:00:01.000'

    Would I need to break this date down?

    Then I need to delete the data in the current database once it is archive.

    Thanks

    Lystra

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are off by a second?

    I suspect that what you want is:
    Code:
    INSERT INTO IISLOG
    (  id, ClientHost, Username
    ,  Logtime, Service, Machine
    ,  ServerIP, Processingtime, Bytesrecvd
    ,  BytesSent, ServiceStatus, Win32status
    ,  Operation, Target, Parameters
    ,  Department) SELECT
       id, ClientHost, Username
    ,  Logtime,  Service, Machine
    ,  ServerIP, Processingtime, Bytesrecvd
    ,  BytesSent, ServiceStatus, Win32status
    ,  Operation, Target, Parameters
    ,  Department 
       FROM IISLOG.DBO.IISLOG 
       WHERE  '2003-10-13' = Convert(CHAR(10), Logtime, 121)
    GO
    This should copy all the rows for a given date, you could get really fancy and use a CHAR(7) to get all of the rows for a given month.

    Test this first (using BEGIN TRANSACTION and ROLLBACK TRANSACTION), but then you could get really tricky and use something like:
    Code:
    DELETE FROM a
       FROM IISLOG.dbo.IISLOG AS a
       JOIN IISLOG AS b
          ON (b.Logtime = a.Logtime)
    to remove the rows from the production table.

    -PatP

  8. #8
    Join Date
    Jul 2004
    Posts
    191
    Thanks Pat.

    I ran the code and receive this message:

    Server: Msg 9002, Level 17, State 6, Line 1
    The log file for database 'IISLOG_ARCHIVE2003' is full. Back up the transaction log for the database to free up some log space.

    Once I have backup the log what would I need to do to free up the space?

    Should I use Truncate_Only

    Backup Log IISLOG
    With Truncate_Only

    I went out today and bought a book MS SQL Server 2000 Unleashed.

    Lystra

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There could be a whole host of things causing that error message. As long as you don't do transaction log dumps, you should be able to use TRUNCATE_ONLY safely.

    If that doesn't work, check to see if there are limits on the size of either the data file or the log file for IISLOG_ARCHIVE2003. If there are limits, find out why there are limits, and if you can safely increase or remove them.

    -PatP

  10. #10
    Join Date
    Jul 2004
    Posts
    191
    It works thanks

    Lystra

  11. #11
    Join Date
    Jul 2004
    Posts
    191
    If I take that same code:

    INSERT INTO IISLOG
    ( id, ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department) SELECT
    id, ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department
    FROM IISLOG.DBO.IISLOG
    WHERE '2003-10-13' = Convert(CHAR(10), Logtime, 121)
    GO

    I now want to copy over the data for 2004, I don't have a set date.

    I want all of the months for 2004 how would I add it to the code. I tried '2004' and that didn't work.

    Lystra

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The simplest answer would be:
    Code:
    INSERT INTO IISLOG
    ( id, ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department) SELECT
    id, ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department 
    FROM IISLOG.DBO.IISLOG 
    WHERE 2004 = Year(Logtime)
    GO
    The down-side to the simple answer is that if you have an index on Logtime (which you probably should), the function call prevents the optimizer from being allowed to use the index. If you have the index, a much more efficient answer would be to use:
    Code:
    INSERT INTO IISLOG
    ( id, ClientHost, Username
    , Logtime, Service, Machine
    , ServerIP, Processingtime, Bytesrecvd
    , BytesSent, ServiceStatus, Win32status
    , Operation, Target, Parameters
    , Department) SELECT
    id, 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()
    GO
    -PatP

  13. #13
    Join Date
    Jul 2004
    Posts
    191
    Thanks, I was able to put a minus 1(-1) so I can only get the data from 8/31

    Thanks again.

    Lystra

Posting Permissions

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