Hi,I have a table with alot of company names and their IDs, and another one linked by that ID with alot of their activities. So the same company usually can have 100+ activities and this list keep growing everyday.I need to get a report containing all the companies and data and the amount of activities separated by completed/succesful/etc. This is what I did:SELECT c.CompanyID, c.CompanyData,SUM(CASE WHEN a.ActivityStatus = 1 THEN 1 ELSE 0 END) AS Ongoing,SUM(CASE WHEN a.ActivityStatus = 2 THEN 1 ELSE 0 END) AS Sucessful,SUM(CASE WHEN a.ActivityStatus = 3 THEN 1 ELSE 0 END) AS Unsucessful,COUNT(a.ActivityStatus) AS TotalActivitiesFROM tbl_Company cLEFT JOIN tbl_Activities a ON a.CompanyID = c.CompanyIDGROUP BY c.CompanyID, c.CompanyDataORDER BY c.CompanyDataCompanyID CompanyData Ongoing Sucessful Unsuccessful Total----------------------------------------------------------------1 CompanyA 102 50 10 1622 CompanyB 210 80 15 305
However, the viewer (as always unsatisfied) now wants to see the results separated by weeks according to the time period he/she choose. Like this: ---------- Week 1 ---------- ---------- Week 2 ---------- CompanyID CompanyData Ongoing Sucessful Unsuccessful Ongoing Sucessful Unsuccessful Total-------------------------------------------------------------------------------------------------1 CompanyA 52 20 5 30 50 5 1622 CompanyB 110 45 15 100 30 5 305
I've been struggling for quite some time and can't find any solution to this, please help!
Help with an annoying SELECT
Posted - 2010-03-18 : 16:35:22
