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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
litago
Forum Newbie
Posts: 3
Joined: Tue May 10, 2005 6:11 pm

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

Post 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?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.
litago
Forum Newbie
Posts: 3
Joined: Tue May 10, 2005 6:11 pm

Post 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.
litago
Forum Newbie
Posts: 3
Joined: Tue May 10, 2005 6:11 pm

Post 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.
Xavier
Forum Newbie
Posts: 1
Joined: Tue May 17, 2005 6:26 pm

Post 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
Post Reply