SQL 2000 - SP

I need a SP to create a report. The SP have to do the following for each record in the Clinics Table: THE EASY PART: 1. Count Total number of visits (ClientsAttendance Table). Name this column "Visit" 2. Count total number of de treatments (TreatmentAttendance Table).Name this column "Acupt" 3. Count Total number of groups (GroupAttendance Table).Name this column "Group" 4. Filtered by IsOpen, where Isopen = 1 (Clinics Table). 5. Sorted by Team (Clinics Table). 6. Sorted by Lname (Clinics Table). 7. Sorted by Fname (ClinicsTable). THE BRAIN PART: 7. Give each client one point for each day a client has negative toxicology result (ToxicologyAttendance).Name this column "Negative Toxicology". For example: if a particular client at a specific day has three negative results and one posive, he or her will get cero points. On the other hand, if the client has no positive results that day, he or she will get one point. It does not matter how many negative results the client has a particular day, he or she will get only one point. In conclusion, the maximun a client can get is six points (six days, all negative), this number decrease by one each day the client have a positive result. 8. Count totals (Visit, Acupt, Group, and Negative Toxicology) for each record. Name this column "Points" 9. Divide the total numbers of "Points" by 5 (Points/5). Name this column "# of Selections" VERY IMPORTANT!! 10. All calculations must be one week late (Monday - Saturday). It means that If I run the SP the third week of the month, the SP should give me the results belonging to the second week of the month. I CANNOT PROVIDE SAMPLE DATA. INCLUDED IS THE FULL DATABASE SCRIPT. UPON REQUEST, I WILL PROVIDE AN EMPTY FULL DATABASE BACKUP. ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform All Windows Platforms Broadcast message from 5/17: "I made a mistake, correct name is Toxicology results. Some tables might have have a column named TodayDate that is to be ignored from all calculations."