Page 1 of 1

mySQL query, need help to get the correct data for a report

Posted: Tue May 10, 2005 6:27 pm
by litago
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):

Code: Select all

proid 
prospect (value 1= registered, value 0 = not registered.) 
prospectdate

Code: Select all

proid 
sale (value 1= sold, value 0 = not sold.) 
saledate

Code: Select all

proid 
cancel (value 1= cancelled, value 0 = not cancelled.) 
canceldate
I want to be able to create a report displayed like below:

Code: 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, week
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?

Posted: Tue May 10, 2005 7:40 pm
by infolock
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.
sorry, i'm not quite following you... i mean, i'm almost where you are as far as eye to eye goes, but unless you can post an example of what it looks like when it comes out wrong versus what it should look like when it comes out correctly, i'm not gonna be able to help you.

Posted: Wed May 11, 2005 3:54 pm
by litago
I do not have a result on this report at all.
The point is that I want to create a report that gives me a resultset: year, weeknumber, amount of prospects for that week, amount of sales for that week and the amount of cancellations that week.

What I want is in the code for "What I want".

What I want:

Code: 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
The I would have five rows in the tblprospectliste_prospect table, where two of them are with the prospectdate of week 3 and three of them are with a prospectdate of week 2.

At the same time I would have 10 records in the tblprospectliste_sale table, where the saledate would be within week 4,3 and 2.

And the tblprospectliste_cancel table would have two records. Where one is in week 5 and the last one in week 2.

The example I gave would give out just the first three columns Year, Week and Prospects. But I need to have the sale and cancels in the same outbut. And that is my problem, I do not know how go make this happend.

Posted: Fri May 13, 2005 3:27 pm
by litago
Can anyone help me with this report. How should the query look like to get the sum of prospects, sales and cancellations from the tables?

Any hel is valuable.
Thanks.

Posted: Tue May 17, 2005 6:35 pm
by Xavier
Something along these lines should work (untested):

Code: Select all

SELECT year, week, SUM(prospectcount), SUM(salecount), SUM(cancelcount) FROM (
SELECT YEAR(prospectdate) AS year, WEEK(prospectdate) AS week, COUNT(*) as prospectcount, 0 as salecount, 0 as cancelcount FROM tblprospectliste_prospect WHERE prospect = 1 GROUP BY year, week
UNION
SELECT YEAR(saledate) AS year, WEEK(saledate) AS week, 0 as prospectcount, COUNT(*) as salecount, 0 as cancelcount FROM tblprospectliste_sale WHERE sale = 1 GROUP BY year, week
UNION
SELECT YEAR(canceldate) AS year, WEEK(canceldate) AS week, 0 as prospectcount, 0 as salecount, COUNT(*) as cancelcount WHERE registered = 1 GROUP BY year, week
) reportsub GROUP BY year, week ORDER BY year, week
This will *not* include weeks that do not have any sales, prospects, or cancels. That would add a new level of complexity. If you are using MySQL, this query requires at least v4.1 (I think)

- Xavier