Need help running a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help running a query

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2005-06-08 : 06:00:17

Hey guys and girls, have a table which records the activity of downloads from a website. The idea is that you can download the same thing with in the same day and only be charged once. But i'm trying to do a usage report and the problem is that when i run the query it brings back when ever a user has had two downloads in the same day it brings back a result, what i want to try and do is only bring back results per day per doument.

Regards Lee

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-08 : 06:09:44

We need a bit more info to help you properly without trying to 2nd guess what you require. See the link below

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Andy

Beauty is in the eyes of the beerholder

lemondash
Posting Yak Master

159 Posts

Posted - 2005-06-08 : 06:36:50

This is the table i'm query.
CREATE TABLE [PIActivityLog] (
[lDocumentId] [int] NOT NULL ,[sDocumentPath] [varchar] (100) AS NOT NULL ,
[dtDateLogged] [datetime] NOT NULL ,[curPrice] [money] NOT NULL ,
[lUserId] [int] NOT NULL ,[lCompanyid] [int] NOT NULL ,
[sCostCode] [varchar] (255) AS NOT NULL ,[lRequestId] [int] IDENTITY (1, 1) NOT NULL ,
[lDocumentTypeId] [int] NULL ,[lIPAddress] [varchar] (15) AS NULL ,
[sDescription] [varchar] (255) AS NULL ,[lNumPages] [int] NULL ,
[bPDF] [bit] NOT NULL ,[sMMUserName] [varchar] (100) (null),
[lSearchID] [int] NULL ,[lDocFormatID] [int] NULL ,
[lChannelPartnerID] [int] NULL ),[lUsageSourceId] [int] NULL
) ON [PRIMARY]
GO

And every time a client downloads an item it inserts a record in to this table but if they downlaod the same item twice in the same day it will put another insert in. but i want to bring back all the date from this table but only return the items that have been download once that day .

jhermiz

3564 Posts

Posted - 2005-06-08 : 08:09:58

Do you want the latest date of the download or the first? Look at aggregate functions such as MAX and use group by.

BOL has more info on both.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jhermiz

3564 Posts

Posted - 2005-06-08 : 08:22:12

SELECT DocumentID, MAX(dtDateLogged) as TheDate FROM PIActivityLog
GROUP BY DocumentID ORDER BY TheDate DESC

Shows latest document per day

if you need it for a specific day use GetDate() and compare with your dtDateLogged field, you may have to trim the time portion out of the date since it can vary. Here's an example:

SELECT DocumentID, MAX(dtDateLogged) as TheDate
FROM PIActivityLog
WHERE (DATEADD(d, DATEDIFF(d, 0, dtDateLogged), 0) = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
GROUP BY DocumentID ORDER BY TheDate DESC

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources