Grouping records issue

Question

Post reply

Grouping records issue

jalldridge 99781

Grasshopper

Points: 14
More actions
August 24, 2010 at 7:28 am

#141553

Hi Guys,
Been trying to rack my brains on how to do something in SQL that involved counts.
We have a table in a database that has columns of information:
[Attendance date] [attendance count] [site id] [identifier] ...
01 july 2010 2 1 persona
03 jul 2010 1 3 person b
23 july 2010 3 1 person a
We need to be able to do grouping on this data i.e. we need a *weekly* count of the attendance number going back the last 6 months. In some cases we know that a week will not have any entries in the database so the weekly count will be zero. If the date 6 months ago falls in the middle of the week then the monday at the start of that week should be included in the count. Final output along these lines (week 0 is the current week)
[week number] [count] [site id]
0 3 1
1 20 1
2 0 1
3 4 1
0 3 2
1 10 2
2 15 2
3 12 2
I have come up with the following but do not know how to do the final manipulation:
select [Attendance date], [site id], count(*) as num_att_on_day, datepart(wk, [Attendance date], ) as weeknum from tblattendance where [Attendance date], > DATEADD(wk, DATEDIFF(wk, 0, dateadd([Attendance date], ,-6, getdate())), 0)
group by [Attendance date], [site id],
order by [site id], asc, weeknum desc
Any assistance greatly received 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please will you provide table DDL and sample data in the form of insert statements. This wuill help us to help you.

Thanks

John

pankushmehta Ten Centuries Points: 1217 More actions · Answer 2

would this help?

declare @tblattendance table ([Week Number] tinyint identity(0,1), num_att_on_day int, [Site ID] smallint)

insert into @tblattendance

select count(*) as num_att_on_day, [site id]

from tblattendance

where [Attendance date], > DATEADD(wk, DATEDIFF(wk, 0, dateadd([Attendance date], ,-6, getdate())), 0)

group by [Attendance date], [site id], datepart(wk, [Attendance date] )

order by [site id], asc, weeknum desc

select [Week Number], num_att_on_day, [Site ID] from @tblattendance

jalldridge 99781 Grasshopper Points: 14 More actions · Answer 3

Hi Guys,

I've been pulled off on to something else so afraid I'll be unable to get back to this for a while - I'll update when I can. Many thanks to those who have responded to date.