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

Help needed on simple SUM query

I tried this query:
SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
FROM `QuoteItem`
GROUP BY `QuoteId`
HAVING (`QuoteId` LIKE "250033")
to get the values of each QuoteItemId.
There are 7 different items ion this quote, but this query is summing
them ALL together.
I want results like:
250033, 1, 50
250033, 2, 38
250033, 3, 75
250033, 4, 100
etc...
Please advise where I'm getting it wrong.
I googled the topic and I must just have some killer blinders on.
I did try a few other things, but no go.
I know it's going to be simple, but I'm just not seeing it.
Thanks-a-bunch,
MoUse square brackets as name delimiters - not single quotes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mo" <Mehile.Orloff@.gmail.com> wrote in message
news:35683345-c1af-4eac-b8fb-58e7bd73205d@.e10g2000prf.googlegroups.com...
I tried this query:
SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
FROM `QuoteItem`
GROUP BY `QuoteId`
HAVING (`QuoteId` LIKE "250033")
to get the values of each QuoteItemId.
There are 7 different items ion this quote, but this query is summing
them ALL together.
I want results like:
250033, 1, 50
250033, 2, 38
250033, 3, 75
250033, 4, 100
etc...
Please advise where I'm getting it wrong.
I googled the topic and I must just have some killer blinders on.
I did try a few other things, but no go.
I know it's going to be simple, but I'm just not seeing it.
Thanks-a-bunch,
Mo|||As Tom noted you need to remove the single quotes. I see your grouping is
incorrect too. Try this:
SELECT QuoteId, QuoteItemId, SUM(QtyQuoted * Price)
FROM QuoteItem
GROUP BY QuoteId, QuoteItemId
HAVING QuoteId = '250033'
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||On Apr 2, 10:33=A0am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use square brackets as name delimiters - not single quotes.
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "Mo" <Mehile.Orl...@.gmail.com> wrote in message
> news:35683345-c1af-4eac-b8fb-58e7bd73205d@.e10g2000prf.googlegroups.com...
> I tried this query:
> SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
> FROM `QuoteItem`
> GROUP BY `QuoteId`
> HAVING (`QuoteId` LIKE "250033")
> to get the values of each QuoteItemId.
> There are 7 different items ion this quote, but this query is summing
> them ALL together.
> I want results like:
> 250033, 1, 50
> 250033, 2, 38
> 250033, 3, 75
> 250033, 4, 100
> etc...
> Please advise where I'm getting it wrong.
> I googled the topic and I must just have some killer blinders on.
> I did try a few other things, but no go.
> I know it's going to be simple, but I'm just not seeing it.
> Thanks-a-bunch,
> Mo
Thanks.
I've figured out how to do what I need by replacing the HAVING
statement with a WHERE statement between the FROM and GROUP BY.
Thanks.
-Mo|||On Apr 2, 10:55=A0am, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> As Tom noted you need to remove the single quotes. I see your grouping is
> incorrect too. Try this:
> SELECT QuoteId, QuoteItemId, SUM(QtyQuoted * Price)
> FROM QuoteItem
> GROUP BY QuoteId, QuoteItemId
> HAVING QuoteId =3D '250033'
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks, I'll put your advise into operation.
Regards.|||>>
I've figured out how to do what I need by replacing the HAVING
statement with a WHERE statement between the FROM and GROUP BY.
I don't think that was the (only) problem.
:-)

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

Help Me with This Query!

Hi All,

I have this query and I want to show single entry on the every records, I already use Group By, but it doesn't solve the problem.

Please help to find the problem.

=============================================================================================

SELECT EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal,SUM(TotalOrder)as TotalOrderFROM

(SELECT

e.OfficeCode,

(SELECTDISTINCT OfficeFROM OfficeCode ocWHERE oc.Code= e.OfficeCode)as Office,

e.OfficeCode+'-'+ e.EmployeeCodeas EmployeeCode,

(SELECT FirstName+' '+ LastNameFROM Person pWHERE p.PersonID= e.PersonID)as EmployeeName,

InvoiceTotal=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENSUM(o.OrderFee)ELSE 0END,

TotalOrder=CASEWHEN o.InvoiceDTBETWEEN @.StartDateAND @.EndDateTHENCount(o.OrderID)ELSE 0END

FROM Employee e

LEFTJOIN oms.[OrderAssignment] oaON e.EmployeeID= oa.EmployeeID

LEFTJOIN oms.[Order] oON o.OrderID= oa.OrderID

LEFTJOIN OfficeCode ocON oc.Code= e.OfficeCode

WHERE e.OfficeCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.OfficeCode,','))AND

e.IsManager='True'AND

e.DivisionCodeIN(SELECT ItemFROM dbo.SPLITPARAMETER(@.DivisionCode,','))

GROUPBY e.OfficeCode, e.EmployeeCode, e.PersonID, o.InvoiceDT, o.OrderFee, o.OrderID)as tmp

GROUPBY EmployeeCode, Office, OfficeCode, EmployeeName, InvoiceTotal, TotalOrder

=================================================================================================

Thanks in advance.

Anyone please...|||I got it. The problem is I put Group By TotalOrder. If I take out the group by totalOrder, it will works just fine. Thanks.

help me gauge the effort in my first db setup

Hello there... Happy New Year!

A client of mine, a group of 4 doctors, are interested in implementing a small office network in which they will switch to a digital filing system. I have recommended a Microsoft Server 2003 system, therefore the patient information will be stored in the SQL server.

I have never administered, administrated?? (see, I really need help here!) a database, but have built small databases from scratch in C and with Access, so I feel confident that I could set up a system for them. I need to have some kind of estimate, both a fee and implementation schedule. Can you help me get a handle on the scope of this?

Many thanks,
BrynYou Access experience will give you a leg up on the learning curve for developing a SQL Server database, but you will still have a lot to learn about administration. Security (which I think would be important for a doctors office) is much different, but is also much better and much simpler in SQL server than in Access. You will also need to write your SQL queries, so I hope that you are somewhat fluent in that and have occasionally looked at the SQL code that Access creates. Be aware that there are some subtle differences between SQL Server SQL and MS Access SQL.

Your best for a small application like this is to create it as an MS Access ADP project. This will allow you to use Access as an efficient interface to SQL Server. This is not the best solution for large applications, but for what you are describing it should be fine. If they want to scale it up in the future, they can drop the Access interface and still retain the SQL Server database with a new interface.

I can't, of course, give you an estimate on how long it will take you to complete it. Personally, since you are learning on the job, I think you should quote them based on how long it would take to develop the database in MS Access and consider any additional time as training and career development.

blindman|||You have many client tools in sql server that you need to research:

Enterprise Manager - This is a gui interface to manage your sql server instances.
Query Analyzer - Interface to run/test transact-sql statements.
BOL - Books Online - This is the online reference guide to sql server - You will use this frequently as you work with sql server. (Almost) Any question you have, go here first.
Profiler - captures events from sql server - allowing you to debug/analyze a problem/performance.

For books, look for books by Ken Henderson, Mark Spenik and Staneks pocket admin.