RE: FN-FORUM: SQL help

date posted 11th January 2007 19:42

Paul Cooper wrote:
> I want to run a SQL statement so that I can see how many scores have
> been generated between specific dates and by whom. So essentially my
> form will just allow a user to input a start date and end date. The
> query will then display a list of all Licensees that have scores
> generated between those dates (I don't want to display Licensees in
> this list who have not generated scores in the period). Alongside
> the list of each Licensee displayed will be the total number of
> scores they've generated.
>
> TIA
> Paul

Paul,

SELECT l.LicenseeID,l.Company,count(*) AS NumScores FROM tblScores AS s
INNER JOIN tblGroups AS g ON s.GroupID = g.GroupID INNER JOIN tblLicensees
AS l ON g.LicenseeID = l.LicenseeID WHERE s.generatedDate > {start date
here} AND s.generatedDate < {end date here} GROUP BY l.LicenseeID,l.Company
ORDER BY l.Company

Should do it I think (untested)?

Regards,

Dai