Page 1 of 2
Fill in missing date if no record on particular date
Posted: Thu Oct 15, 2009 11:33 pm
by little_girl
I'm working on how to create a proper daily report – one that doesn’t have any gaps when data is missing. I want to show all the date for each month in the report. The problem is when there is no record on particular day, it wouldn't show it in my report. So I would like to know how to fill in the missing date into my report with 0 value.
For example:
My report is showing the total of users joined for each category in daily basis by month.
Table "Magazine"
DateJoined | UserID | CategoryID
2009-01-01 | 123 | 1
2009-01-01 | 124 | 1
2009-01-02 | 125 | 3
2009-01-05 | 126 | 2
My Query Result in table format:
DateJoined | Category 1 | Category 2 | Category 3 | Total
2009-01-01 | 2 | 0 | 0 | 2
2009-01-02 | 0 | 0 | 1 | 1
2009-01-05 | 0 | 1 | 0 | 1
Hope someone could give me some idea on how to solve this problem in php level. Thanks in advance.
Re: Fill in missing date if no record on particular date
Posted: Fri Oct 16, 2009 3:32 am
by litarena
Is this a php problem or a general programming problem? You've stored all your values in your database. Now you want to extract and print them. That's regular enough. But you also want to print values when you have no data. In that case you'll need to break the problem into chunks.
a. for each individual day of each month, extract the data & test whether or not any data was returned.
b. if data was returned for that day print it.
c. if no data was returned for that day print that day's date and print zeroes in the appropriate data-fields [that's done by checking the value of your database extraction query with ==0. If that's true then no data was returned.]
You'll need to have a couple of things in your program. I'll need to know the lengths of the months in days, and loop through each day of each month with the steps listed above.
(I don't know what this program you're writing is for. If it's for a real live business application you'll need to take account of leap year adjustments too.)
Re: Fill in missing date if no record on particular date
Posted: Fri Oct 16, 2009 4:02 am
by Mark Baker
You can use a subquery something like:
Code: Select all
SELECT date_add('2009-1-1',CONCAT('INTERVAL ',rownumber,' DAY'))
FROM <TABLE>
WHERE rownumber < 365
which should return a series of 365 dates from the defined start data
(as long as the <table> you select contains that many rows)
You can then join your main query to this query
Re: Fill in missing date if no record on particular date
Posted: Fri Oct 16, 2009 4:10 am
by little_girl
Hi Mark Baker:
For example, the data return from my query result only have 3 records on 2009-01-01, 2009-01-02, 2009-01-05. I want to show the rest of the day in that month as well with value 0. So how to print out the rest of the date eventhough in the database table don't have those record?
what should I do to show the start date If the month is based on the user selection?
Thanks.
Hi litarena:
This is php problem.
What do you mean by "take account of leap year adjustments too" and what should I do if want to implement in my application? Can you give me some idea. Thanks.
Re: Fill in missing date if no record on particular date
Posted: Fri Oct 16, 2009 4:14 am
by litarena
What are you writing it for? If it's just an exercise don't worry about it. But if it's for real use, February has a different length if it's a leap year.
Re: Fill in missing date if no record on particular date
Posted: Fri Oct 16, 2009 4:22 am
by little_girl
Is the leap year can be checked as what in this link below?
http://de.php.net/manual/de/function.date.php
Re: Fill in missing date if no record on particular date
Posted: Fri Oct 16, 2009 5:03 am
by Mark Baker
Sorry it took me so long to get back. I tried to test my query, and found that MySQL had died on me, so I needed to reinstall it.
Actual query should be
Code: Select all
SELECT adddate('2009-01-01',(@rownum:=@rownum+1)-1) date
FROM (SELECT @rownum:=0) r, COLUMNS
WHERE @rownum < 365
You must already have a query that is returning your existing report data from the magazine table. You need to join that query to the one that I've posted above, joining the two queries on the date
what should I do to show the start date If the month is based on the user selection?
You'd replace the start date in my query ('2009-01-01') with the actual start of the month selected by the user... you must be doing something similar in your existing query.
You'd probably also want to adjust the 365 days that I set in the WHERE clause.
Without knowing your existing select statement, and how you're inserting the user specified date into that, I can't do much more to help
Re: Fill in missing date if no record on particular date
Posted: Sun Oct 18, 2009 7:30 pm
by little_girl
Hi Mark Baker:
This is my existing select statement:
SELECT DATE(DateJoined) AS jDate,
SUM(IF(catID=1,1,0)) AS Cat1,
SUM(IF(catID=2,1,0)) AS Cat2,
SUM(IF(catID=3,1,0)) AS Cat3,
COUNT(*) AS Total
FROM Magazine
WHERE YEAR(DateJoined)=YEAR(CURDATE()) AND MONTH(DateJoined)=$mth
GROUP BY DATE(DateJoined)
So where should I join the subquery as you suggested?
Re: Fill in missing date if no record on particular date
Posted: Mon Oct 19, 2009 4:28 am
by Mark Baker
Should look something like:
Code: Select all
SELECT d.date AS jDate,
SUM(IF(m.catID=1,1,0)) AS Cat1,
SUM(IF(m.catID=2,1,0)) AS Cat2,
SUM(IF(m.catID=3,1,0)) AS Cat3,
COUNT DISTINCT(m.UserID) AS Total
FROM ( SELECT adddate( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) date
FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
WHERE @rownum <31
) d
LEFT JOIN `magazine` m ON m.DateJoined = d.date
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth
GROUP BY DATE(d.date)
Re: Fill in missing date if no record on particular date
Posted: Mon Oct 19, 2009 10:18 pm
by little_girl
Code: Select all
SELECT d.ddate AS jDate,
SUM(IF(m.CategoryID=1,1,0)) AS NST,
SUM(IF(m.CategoryID=2,1,0)) AS BH,
SUM(IF(m.CategoryID=3,1,0)) AS HM,
COUNT (*) AS Total
FROM ( SELECT ADDDATE( CONCAT(YEAR( CURDATE( ) ), '-',1,'-01'), (@rownum := @rownum +1) -1 ) ddate
FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
WHERE @rownum <31
) d
LEFT JOIN `magazine` m ON m.DateJoined = d.ddate
WHERE YEAR(d.ddate)=YEAR(CURDATE()) AND MONTH(d.ddate)=1
In my code above,
I have replaced "COUNT (m.UserID) AS Total" with "COUNT(*) AS Total" and $mth in the second select statement I have replaced with "1". But there are an error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS Total
FROM ( SELECT adddate( CONCAT(YEAR( CURDATE( ) ), '-',1,'-01'), (@r' at line 5
Re: Fill in missing date if no record on particular date
Posted: Tue Oct 20, 2009 3:39 am
by Mark Baker
The problem is actually with Total
Code: Select all
SELECT d.date AS jDate,
SUM(IF(m.CategoryID=1,1,0)) AS Cat1,
SUM(IF(m.CategoryID=2,1,0)) AS Cat2,
SUM(IF(m.CategoryID=3,1,0)) AS Cat3,
IF(m.CategoryID=NULL,0,COUNT(m.CategoryID)) AS Total
FROM ( SELECT adddate( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) date
FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
WHERE @rownum <31
) d
LEFT JOIN `magazine` m ON m.DateJoined = d.date
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth
GROUP BY DATE(d.date)
Re: Fill in missing date if no record on particular date
Posted: Tue Oct 20, 2009 8:20 pm
by little_girl
Hi,
I 4got one condition that I need to put in the WHERE clause where it need to filter the record according to the magazine choose by the user as well beside the month.
My Code:
Code: Select all
SELECT d.date AS jDate,
SUM(IF(m.CategoryID=1,1,0)) AS Cat1,
SUM(IF(m.CategoryID=2,1,0)) AS Cat2,
SUM(IF(m.CategoryID=3,1,0)) AS Cat3,
IF(m.CategoryID=NULL,0,COUNT(m.CategoryID)) AS Total
FROM ( SELECT ADDDATE( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) DATE
FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
WHERE @rownum <31
) d
LEFT JOIN `magazine` m ON m.DateJoined= d.date
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth AND MagzineID=$mg
GROUP BY DATE(d.date)
After I added the "MagazineID=$mg" into the "WHERE" clause, it returned empty recordset. Although without this new condition, it also me the wrong result as all the records returned with the value "0".
Re: Fill in missing date if no record on particular date
Posted: Wed Oct 21, 2009 3:09 am
by Mark Baker
The primary return for this query is the date (d), and the WHERE clause is all selecting from there.
It is the secondary query (the LEFT JOIN) that is against magazine (m), and your additional criteria is against the magazine.
So your MagzineID=$mg should be against the ON clause for the LEFT JOIN, and not against the WHERE clause
Re: Fill in missing date if no record on particular date
Posted: Wed Oct 21, 2009 8:05 pm
by little_girl
I have modified my code as below:
Code: Select all
SELECT d.date AS jDate,
SUM(IF(m.CategoryID=1,1,0)) AS Cat1,
SUM(IF(m.CategoryID=2,1,0)) AS Cat2,
SUM(IF(m.CategoryID=3,1,0)) AS Cat3,
IF(m.CategoryID=NULL,0,COUNT(m.CategoryID)) AS Total
FROM ( SELECT ADDDATE( CONCAT(YEAR( CURDATE( ) ), '-',$mth,'-01'), (@rownum := @rownum +1) -1 ) DATE
FROM ( SELECT @rownum :=0 )r, information_schema.COLUMNS
WHERE @rownum <31
) d
LEFT JOIN `magazine` m ON m.DateJoined= d.date AND MagzineID=$mg
WHERE YEAR(d.date)=YEAR(CURDATE()) AND MONTH(d.date)=$mth
GROUP BY DATE(d.date)
But the result shows all the records return with the value "0" from the beginning until the end of month. Something is wrong in my code, but couldn't find out the problem.
Re: Fill in missing date if no record on particular date
Posted: Thu Oct 22, 2009 3:04 am
by Mark Baker
I have no idea what is wrong with your code, but there is absolutely nothing wrong with the database query.
Here's the results when I run it myself
Have you tried running it from phpmyadmin to see the result.
Have you tried sumping out the query to see that the values for $mth and $mg are being set correctly