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

LystraDid you mean to GROUP BY DATEPART(year, logtime) or ORDER BY DATEPART(year,logtime)?|||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:SELECT DatePart(yy, Logtime)
FROM IISLOG
GROUP BY DatePart(yy, Logtime)-PatP|||I still think the poster ment ORDER BY, because GROUP BY would not make any sense.|||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|||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|||You are off by a second?

I suspect that what you want is: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)
GOThis 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: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|||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|||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|||It works thanks

Lystra|||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|||The simplest answer would be: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)
GOThe 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: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|||Thanks, I was able to put a minus 1(-1) so I can only get the data from 8/31

Thanks again.

Lystra