Hi David,
Still Stuck on the SQl. Let me explain, I have table fields called doctor d,clinic c,pmr pmr,patient p,course cor
Patient contains patientID,which is a primary key and it appears as foreign key in course.
Each patient is tied to the clinic and each clinic is tied to a PMR(its is a rep).
Course is a table containing patient is in which course and the date the form was received for registeration.
So basically what i need to do is
Is to get in a week starting from the begining of the year to end of the year.
Lets say this year starts on Jan1-7.
So what i need to do is , Lets say i have 3 form received on 01/01/07,03/01/07 and on 05/04/07 for the Rep called Bruce Tham so on the week of Jan 1-7 for the Rep called Bruce Tham the number should be displayed as 3.
This is my current SQL:-
SELECT
pmr.pmrname,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) BETWEEN '1/1/2007' AND '7/1/2007' THEN 1 ELSE 0 END) AS Jan1To7,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) BETWEEN '8/1/2007' AND '14/1/2007' THEN 1 ELSE 0 END) AS Jan8To14,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) BETWEEN '15/1/2007' AND '21/1/2007' THEN 1 ELSE 0 END) AS Jan15To21,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) BETWEEN '22/1/2007' AND '28/1/2007' THEN 1 ELSE 0 END) AS Jan22To28,
SUM(CASE WHEN CONVERT(char(10),cor.formreceiveddate, 103) BETWEEN '29/1/2007' AND '4/2/2007' THEN 1 ELSE 0 END) AS Jan29ToFeb4
FROM
doctor d,
clinic c,
pmr pmr,
patient p,
course cor
WHERE
d.clinicid=c.clinicid
AND
c.pmrid=pmr.pmrid
AND
pmrname='Bruce Tham'
AND
d.doctorid=p.doctorid
AND
cor.patientid=p.patientid
AND
CONVERT(char(10),formreceiveddate, 103) <> '01/01/1900'
GROUP BY pmr.pmrname
What Im doing is writing the weeks manually throughout 2007 will make the query long and troublesome as there is 52 weeks in a year. What i want is to dynamically generate the weeks and loop it so it will generate the weeks starting on mon-sun each week..e.g..Jan 1-7,Jan 8-14,Jan 15-21 and so on of 2007 is the first of 2007 and it starts on Mon(1) and end in sun(7).
For next year i will need to do the same but the form received date must be for the year 2008 so i will have to change the sql abit to only select the date which is in 2008 but the weeks must be for example Jan 1-6 because Mon was on 31 Dec 2007. So I can say that my report must start on 01/01 of every year and end on the first week.
Can this be done.
I have already created two tables, as your previous post. one table called FormReceivedYear with a field called FormReceivedYear containing the year as you mentioned (2007) and the other is the offset table with a field name Offset which I set as 0.
But I dont know how to incoporate that to this sql to generate the result.
The sql i use now roughly gives me the result i want but it is not a smart way of doing it As for next year i'll have to rewrite the weeks all over again.
I have attached an image of the SQL result.
Can You help please.
Thanks A lot.
Unsolved 
