Hello All...
This post is a bit long because I tried to make everything as clear as possible. Please read on...
Report requirements
I have a system that allows users to create surveys and have web surfers take them. I have been asked to produce a report that shows how many people took a given survey vs how many people just viewd it, but didn't take it. The report will be a chart, showing that data on a hourly basis for a specifict time frame. Make sense? For example, the report will show for say, the month of June, from Midnight to 11:00 PM (hour by hour) the number of views vs the number of takes of a given survey.
Database setup
Three (3) tables come into play here:
1 - Survey_Respondent
2 - Survey_ViewCount
3 - FakeTable
The first 2 tables are properly related and they store the respondents, and the number of views respectively. Every time someone takes the survey, a record is inserted in the Survey_Respondent table. Survey_ViewCount has a datetime field and a integer counter field. Every time a user views the survey (even if he doesn't take it), a datetime value (precise to the hour as in 2007-06-19 13:00:00) is entered and the counter for that date and hour is incremented.
Clear so far? Ok, let's go on...
FakeTable is not related to anything. It surves only 1 purpose: Create missing records for Crystal reports though joins. The report I'm working on requies at least 24 records (because I have to display a line in a chart for each hour of the day and Crystal Reports won't show a line unless there is a record for it). Faketable has 2 fields, fakefield1 and fakefield2. There are 120 records in the table. fakefield1 has the value 0 for the first 60 records and 1 for the last 60 records. fakefield2 has the values 0 to 59 for the first 60 records and then 0 to 59 again for the last 60. By using a left join from fake to any other table, I can retrieve a fixed number of records (See Query below).
The Query
Here's the query I am trying to run to generate my report:
[tt]SELECT f.fakeField2 AS entryHour, count(r.entryDate) AS responses, IFNULL(sum(vc.viewCount),0) AS viewCount
FROM (faketable f
LEFT JOIN survey_respondent r on r.surveyId = 11
AND r.entryDate BETWEEN '2007-01-01 00:00:00' AND '2007-12-31 23:59:59'
AND f.fakefield1=0 AND f.fakefield2=hour(r.entryDate))
LEFT JOIN survey_viewcount vc ON fakefield2 = hour(vc.viewDate)
AND vc.viewDate >= '2007-01-01 00:00:00' AND vc.viewDate <= '2007-12-31 23:59:59'
AND vc.surveyId = 11
WHERE f.fakefield1=0 AND f.fakefield2<24
GROUP BY entryHour[/tt]
The query is quite simple: Just look at the WHERE clause. It says "WHERE f.fakefield1=0 AND f.fakefield2<24". Because we're doing a left join and faketable is the table on the left, this WHERE clause will retrieve exactly 24 rows (that's my 24 hours of the day). Now, for each of those rows, I will get from the survey, survey_responent, and survey_viewcount tables whatever matches the criteria of their JOINS. For the survey_respondent, for instance, I have asked for a specific Id, and date range. I have also requrested that fakefield2 = hour(surveyDate). This will link the respondent's hour to fakefield2 which represents each hour of the day.
The Problem
The query above works beautifully on MySql. For each hour, I get the number of responses, and the number of views. If a given hour has no activity, I get NULL. However, when I run it in Crystal, I get the number of responses correctly, but I always get 0 for the viewCount. I first thought the NULLs were causing the problem and so I tried using IFNULL() to no avail. I also changed the report options on Crystal to convert database null values to default. That did not fix it either.
Can anyone shed some light?
My Envrironment
OS: Windows 2000 Server SP4
Database: MySql Server
Crystal Version: XI
Latest ODBC Drivers and .NET Connectors installed for MySql.
Thanks.
JC
_________________________________
I think, therefore I am. [Rene Descartes]
This post is a bit long because I tried to make everything as clear as possible. Please read on...
Report requirements
I have a system that allows users to create surveys and have web surfers take them. I have been asked to produce a report that shows how many people took a given survey vs how many people just viewd it, but didn't take it. The report will be a chart, showing that data on a hourly basis for a specifict time frame. Make sense? For example, the report will show for say, the month of June, from Midnight to 11:00 PM (hour by hour) the number of views vs the number of takes of a given survey.
Database setup
Three (3) tables come into play here:
1 - Survey_Respondent
2 - Survey_ViewCount
3 - FakeTable
The first 2 tables are properly related and they store the respondents, and the number of views respectively. Every time someone takes the survey, a record is inserted in the Survey_Respondent table. Survey_ViewCount has a datetime field and a integer counter field. Every time a user views the survey (even if he doesn't take it), a datetime value (precise to the hour as in 2007-06-19 13:00:00) is entered and the counter for that date and hour is incremented.
Clear so far? Ok, let's go on...
FakeTable is not related to anything. It surves only 1 purpose: Create missing records for Crystal reports though joins. The report I'm working on requies at least 24 records (because I have to display a line in a chart for each hour of the day and Crystal Reports won't show a line unless there is a record for it). Faketable has 2 fields, fakefield1 and fakefield2. There are 120 records in the table. fakefield1 has the value 0 for the first 60 records and 1 for the last 60 records. fakefield2 has the values 0 to 59 for the first 60 records and then 0 to 59 again for the last 60. By using a left join from fake to any other table, I can retrieve a fixed number of records (See Query below).
The Query
Here's the query I am trying to run to generate my report:
[tt]SELECT f.fakeField2 AS entryHour, count(r.entryDate) AS responses, IFNULL(sum(vc.viewCount),0) AS viewCount
FROM (faketable f
LEFT JOIN survey_respondent r on r.surveyId = 11
AND r.entryDate BETWEEN '2007-01-01 00:00:00' AND '2007-12-31 23:59:59'
AND f.fakefield1=0 AND f.fakefield2=hour(r.entryDate))
LEFT JOIN survey_viewcount vc ON fakefield2 = hour(vc.viewDate)
AND vc.viewDate >= '2007-01-01 00:00:00' AND vc.viewDate <= '2007-12-31 23:59:59'
AND vc.surveyId = 11
WHERE f.fakefield1=0 AND f.fakefield2<24
GROUP BY entryHour[/tt]
The query is quite simple: Just look at the WHERE clause. It says "WHERE f.fakefield1=0 AND f.fakefield2<24". Because we're doing a left join and faketable is the table on the left, this WHERE clause will retrieve exactly 24 rows (that's my 24 hours of the day). Now, for each of those rows, I will get from the survey, survey_responent, and survey_viewcount tables whatever matches the criteria of their JOINS. For the survey_respondent, for instance, I have asked for a specific Id, and date range. I have also requrested that fakefield2 = hour(surveyDate). This will link the respondent's hour to fakefield2 which represents each hour of the day.
The Problem
The query above works beautifully on MySql. For each hour, I get the number of responses, and the number of views. If a given hour has no activity, I get NULL. However, when I run it in Crystal, I get the number of responses correctly, but I always get 0 for the viewCount. I first thought the NULLs were causing the problem and so I tried using IFNULL() to no avail. I also changed the report options on Crystal to convert database null values to default. That did not fix it either.
Can anyone shed some light?
My Envrironment
OS: Windows 2000 Server SP4
Database: MySql Server
Crystal Version: XI
Latest ODBC Drivers and .NET Connectors installed for MySql.
Thanks.
JC
_________________________________
I think, therefore I am. [Rene Descartes]