|
| |
|
|
DesiMcK
Posts: 407
Joined: 4/26/2004
From: Essex, UK
Status: offline
 |
COUNT problem - 5/6/2008 15:59:18
Hi all,
this is my current SQL which works fine:
SELECT tblPupil.TutorGroup, Count(tblBehaviour.ID) AS total FROM tblPupil INNER JOIN tblBehaviour ON tblPupil.PupilID = tblBehaviour.PupilID WHERE ([Level] = 'Ac' AND tblPupil.TutorGroup LIKE '7%') GROUP BY tblPupil.TutorGroup ORDER BY TutorGroup ASC
this is then used to produce a table with two columns: Tutor Group | Number of Aces
However I want to add a new column - number of aces this week. How can I achieve this. If I add the date constraint into the WHERE clause I can't get the total number.
Thanks,
Desi
|
|
|
|
rdouglass
Posts: 9137
From: Biddeford, ME USA
Status: offline
|
RE: COUNT problem - 5/6/2008 16:27:03
How about trying 'datepart'? If you're constraining by week:
http://doc.ddart.net/mssql/sql70/da-db_8.htm
Just a quick guess but I'd be trying something like this:
SELECT tblPupil.TutorGroup, Count(tblBehaviour.ID) AS total,DatePart(ww,dateFieldName) As WeekNumber FROM tblPupil INNER JOIN tblBehaviour ON tblPupil.PupilID = tblBehaviour.PupilID WHERE ([Level] = 'Ac' AND tblPupil.TutorGroup LIKE '7%') GROUP BY tblPupil.TutorGroup,DatePart(ww,dateFieldName) ORDER BY TutorGroup ASC
Then a "WHERE" or a "HAVING" in there somewhere to pick the week you want. Haven't checked any of it but that would be where I'd go.
Does that help any or am I reading the Q wrong?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DesiMcK
Posts: 407
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: COUNT problem - 5/6/2008 17:44:02
This is what I want in the end:
Class 1 - 15 aces in the past 7 days - 103 overall this year
If I add DateField > Date() - 7 to the WHERE clause, I get the count for the week, but I can't show the otherall count.
Am I missing something obvious?
Desi
| |
|
|
|
rdouglass
Posts: 9137
From: Biddeford, ME USA
Status: offline
|
RE: COUNT problem - 5/7/2008 9:01:46
So it looks like you want a grand total and then a total for a week?
By it's very nature you have 2 different criteria there (but you knew that already.) You could probably use Derrived tables and a stored procedure or two but wouldn't it be easier to use 2 queries?
Or is this a report that you'd have a new query for each week of the report?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DesiMcK
Posts: 407
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: COUNT problem - 5/7/2008 14:36:09
I can change my original idea if it's easier to have a two way table.
Basically if I could achieve a table such as this then I would be very happy.
|Wk1 | Wk2 | Wk 3 | etc... | Total
Pupil 1 |
Pupil 2 |
Pupil 3 |
Does his make more sense?
Thanks for looking at this,
Desi
| |
|
|
|
DesiMcK
Posts: 407
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: COUNT problem - 5/12/2008 10:05:37
I have now managed to do what I
want - not elegent but it works.
select pupilid,
sum(iif (datepart('ww',incidentdate) = 1,1,0)) as wk1,
sum(iif (datepart('ww',incidentdate) = 2,1,0)) as wk2,
sum(iif (datepart('ww',incidentdate) = 3,1,0)) as wk3,
sum(iif (datepart('ww',incidentdate) = 4,1,0)) as wk4,
...etc...
Count(PupilID) as myTotal FROM tblBehaviour WHERE [Level] = 'Ac'
GROUP BY pupilid
Then I looped through the months in a table:
<%for n = 1 to 52%>
<td align="center"><%=FP_FieldVal(fp_rs,"wk" & n)%></td>
<%next%>
Thanks for your help.
Desi
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
