mySQL query, need help to get the correct data for a report
Posted: Tue May 10, 2005 6:27 pm
I have the following report I want to create:
I want to display a list of prospects, sales and cancellations on a report.
I have the following database schema (only parts):
I want to be able to create a report displayed like below:
Because it only displays the first three column of the "result I want".
It should only count the prospect, sale and cancel field for every week and year. But the problem I have is that the dates in the different date fields are not always on the same week.
I thought of creating a new table where I put all weeknumbers and years inside, and run a check for the three (prospect, sale and cancel) fields how many there are. But I was hoping I could save me that trouble, because then I have to add this for every year.
Does anyone of you have any ideas on how to attack this problem?
I want to display a list of prospects, sales and cancellations on a report.
I have the following database schema (only parts):
Code: Select all
proid
prospect (value 1= registered, value 0 = not registered.)
prospectdateCode: Select all
proid
sale (value 1= sold, value 0 = not sold.)
saledateCode: Select all
proid
cancel (value 1= cancelled, value 0 = not cancelled.)
canceldateCode: Select all
Year Week Prospect Sale Cancel
2005 5 0 0 1
2005 4 0 3 0
2005 3 2 3 0
2005 2 3 4 1
And so on...Code: Select all
SELECT YEAR(prospectdate) AS year, WEEK(prospectdate) AS week, COUNT(*) FROM tblprospectliste_prospect WHERE prospect = 1 GROUP BY year, week ORDER BY year, weekIt should only count the prospect, sale and cancel field for every week and year. But the problem I have is that the dates in the different date fields are not always on the same week.
I thought of creating a new table where I put all weeknumbers and years inside, and run a check for the three (prospect, sale and cancel) fields how many there are. But I was hoping I could save me that trouble, because then I have to add this for every year.
Does anyone of you have any ideas on how to attack this problem?