the week with the most records that fit certain criteria

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

SQL Server Development (2000)

the week with the most records that fit certain criteria

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-03 : 07:57:20

Bob writes "Hi,

I need to find the week (my table covers several years) that has the most records entered that fit a certain criteria. I've managed this with single dates and the count function. I thought it might be possible using Datepart but I cant figure out a way to do a count using weeks over several years, arrr!

Basically I need the week that had the most records (say, where the orders were for shoes ;-) and the date of the beginning of that week.

I'm using PHP, but I just want to know if it could be done using one SQL query...instead of blinkin loads of them in a loop (not really viable is it, for that many dates). Alas though I'm not an MSSQL expert (yet!! ;-) and need your help...

Cheers
Bob (the slob)"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-03 : 08:12:25

this will give you the week no and total records for that week.

select datename(week,timefield) as [week],count(timefield) as Total from tablename
group by datename(week,timefield)

you can add your where clause for criteria

--------------------
keeping it simple...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-03 : 20:17:12

A couple of things first...

Create a table that holds the periods of time that you are interested in and populate it.

CREATE TABLE Calendar (
WeekStart datetime primary key, -- e.g., Sunday 00:00:00.000AM
WeekEnd datetime -- e.g., Saturday 11:59:59.999PM
)

Next,

SELECT c.WeekStart, count(*)
FROM dbo.Calendar c
join dbo.MyData d
on d.MyDatetime between c.WeekStart, c.WeekEnd
where <Your filter criteria>
group by c.WeekStart
order by count(*) desc

This will give you data for each week but you should be able to limit it to only the range you're interested in.

HTH

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-03 : 20:41:18

Try this
select top 1 datepart(year,YTimeField),datepart(week,YTimeField),count(*) from YourTable
where (Your where condition)
group by datepart(year,YTimeField),datepart(week,YTimeField)
order by count(*) desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources