Counting Content Files for a specific Date Range

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

General SQL Server Forums

New to SQL Server Programming

Counting Content Files for a specific Date Range

Author

Topic

reaperslogic
Starting Member

2 Posts

Posted - 2008-04-10 : 13:36:50

I'm trying to find a way to count the number of times a ContentFile, appears in a ContentLog, for a specified period of time.

SELECT DISTINCT ContentFile FROM ContentLog

Shows a list the list of content files.

I'm not sure though how to count for a specific ContentFile, or how to count for on a specific date range.

Thanks for any help you can give.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 14:07:01

select ContentFile, count(*)
from ContentLog
where MyDate between @start and @end
group by ContentFile

elsasoft.org

reaperslogic
Starting Member

2 Posts

Posted - 2008-04-11 : 13:55:24

Still doesn't work, here's what I was thinking.

SELECT DISTINCT COUNT(ContentFile) AS "specific file name here" FROM ContentLog WHERE StartTime > '2008-03-01' AND StartTime < '2008-03-30'

The above query shows the file name and 0 as the total.

Database Schema:

CREATE TABLE [ContentLog]
( [DeviceName] [nvarchar](50) NULL,
[DeviceAddress] [nvarchar](20) NULL,
[StartTime] [nvarchar](25) NULL,
[EndTime] [nvarchar](25) NULL,
[Running] [nvarchar](25) NULL,
[DSP] [nvarchar](50) NULL,
[ContentOrZone] [nvarchar](10) NULL,
[ZoneName] [nvarchar](50) NULL,
[ContentFile] [nvarchar](256) NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 14:08:55

Could you please enlighten us why you have decided Jezemines query not to work?

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-11 : 15:03:19

probably because StartTime and EndTime are nvarchar...

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 15:56:52

O M G...
When will people learn to use proper data types to store data?

select ContentFile, count(*) 
from ContentLog
where CAST(MyDate AS DATETIME) between @start and @end
group by ContentFile

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-13 : 01:05:54

quote:
Originally posted by Peso

O M G...
When will people learn to use proper data types to store data?

as soon as computers learn to understand intent. they need to stop taking everything we tell them so literally!

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources