Records with default values
-
Hi all I want to count records in a table respect to each date. select count(id), add_date from packages group by add_date this gives results only for dates which has exist. Say I want to find the count in a range of dates, and if records not found then the count as zero. Is it possible to do this with MySQL
I appreciate your help all the time... CodingLover :)
Hi, Perhaps the easiest way could be that you create a new table, for example
SingleDateand add all necessary dates to that table. After that, using outer join, join the data between the date table and your table. So something like:SELECT ...
FROM SingleDate d LEFT OUTER JOIN Packages p
ON d.Date = p.Add_Date
GROUP BY d.DateThe need to optimize rises from a bad design.My articles[^]
-
Hi all I want to count records in a table respect to each date. select count(id), add_date from packages group by add_date this gives results only for dates which has exist. Say I want to find the count in a range of dates, and if records not found then the count as zero. Is it possible to do this with MySQL
I appreciate your help all the time... CodingLover :)
-
Hi, Perhaps the easiest way could be that you create a new table, for example
SingleDateand add all necessary dates to that table. After that, using outer join, join the data between the date table and your table. So something like:SELECT ...
FROM SingleDate d LEFT OUTER JOIN Packages p
ON d.Date = p.Add_Date
GROUP BY d.DateThe need to optimize rises from a bad design.My articles[^]
Thanks for the comment. Should I do with a temporary table or create a table in the database permanently? I've tried the following.
CREATE TEMPORARY TABLE date_col (add_dates nvarchar(50));
INSERT INTO date_col (add_dates) VALUES('2011-04-06');
SELECT * FROM date_col;But stuck with two things. 1. How can I loop the date range and add all the dates into the table 2. I think to be in safe side I want to drop the table after the query required data, isn't it?
I appreciate your help all the time... CodingLover :)
-
Step 1: Create a table variable with a date column and fill the table with the date range that you need (probably the min and max dates from 'packages' table) Step 2: Use your query and do a union with the temp table created in step 1
Shameel wrote:
Step 1: Create a table variable with a date column and fill the table with the date range that you need (probably the min and max dates from 'packages' table)
So I can move with a temporary table or a permanent one. But I'm stuck with how to insert range of dates into it. Something like this ...
CREATE TEMPORARY TABLE date_col (add_dates nvarchar(50));
INSERT INTO date_col (add_dates) VALUES ('2011-04-06' through '2011-04-01'); /* how can I do that */I appreciate your help all the time... CodingLover :)
-
Thanks for the comment. Should I do with a temporary table or create a table in the database permanently? I've tried the following.
CREATE TEMPORARY TABLE date_col (add_dates nvarchar(50));
INSERT INTO date_col (add_dates) VALUES('2011-04-06');
SELECT * FROM date_col;But stuck with two things. 1. How can I loop the date range and add all the dates into the table 2. I think to be in safe side I want to drop the table after the query required data, isn't it?
I appreciate your help all the time... CodingLover :)
I would do it permanently since most likely this is not the only case when you need the dates. To fill the table. Why not create a stored procedure. If you need to fill for a specific period again then you can use the same procedure. Something like:
CREATE PROCEDURE DateFill(d1 DATE, d2 DATE)
BEGIN
SET @d3 = d1;
REPEAT
INSERT INTO DateTable (date_co) VALUES (@d3);
SET @d3 = DATE_ADD(@d3, INTERVAL 1 DAY);
UNTIL @d3 > d2
END;
//For dropping the table, I wouldn't since if the table is created and permanent you can continue to use it later. Just add a long enough date range to the table (say, 500 years :)) Also it might be a good idea to add an index to the table to speed up the usage: http://dev.mysql.com/doc/refman/5.1/en/create-index.html[^]
The need to optimize rises from a bad design.My articles[^]
-
I would do it permanently since most likely this is not the only case when you need the dates. To fill the table. Why not create a stored procedure. If you need to fill for a specific period again then you can use the same procedure. Something like:
CREATE PROCEDURE DateFill(d1 DATE, d2 DATE)
BEGIN
SET @d3 = d1;
REPEAT
INSERT INTO DateTable (date_co) VALUES (@d3);
SET @d3 = DATE_ADD(@d3, INTERVAL 1 DAY);
UNTIL @d3 > d2
END;
//For dropping the table, I wouldn't since if the table is created and permanent you can continue to use it later. Just add a long enough date range to the table (say, 500 years :)) Also it might be a good idea to add an index to the table to speed up the usage: http://dev.mysql.com/doc/refman/5.1/en/create-index.html[^]
The need to optimize rises from a bad design.My articles[^]
Actually I don't want to keep records for a long time. Only for an instance. The requirement is, for few sales rep I want to find the sales in a date range. That is for each sales rep I want to find the sales in a date range.
I appreciate your help all the time... CodingLover :)
-
Actually I don't want to keep records for a long time. Only for an instance. The requirement is, for few sales rep I want to find the sales in a date range. That is for each sales rep I want to find the sales in a date range.
I appreciate your help all the time... CodingLover :)
CodingLover wrote:
The requirement is, for few sales rep I want to find the sales in a date range
This is a different thing. What I'm saying is that consider populating the table with long enough date range. And when you use the table take only the relevant portion of dates from it using proper WHERE condition. For example:
SELECT ...
FROM DateTable dt LEFT OUTER JOIN MyTable mt
ON dt.DateColumn = mt.DateColumn
WHERE dt.DateColumn BETWEEN @startdate AND @enddateThis way you can reuse the same data every time you fetch data for a new date range without having to re-create the dates.
The need to optimize rises from a bad design.My articles[^]
-
CodingLover wrote:
The requirement is, for few sales rep I want to find the sales in a date range
This is a different thing. What I'm saying is that consider populating the table with long enough date range. And when you use the table take only the relevant portion of dates from it using proper WHERE condition. For example:
SELECT ...
FROM DateTable dt LEFT OUTER JOIN MyTable mt
ON dt.DateColumn = mt.DateColumn
WHERE dt.DateColumn BETWEEN @startdate AND @enddateThis way you can reuse the same data every time you fetch data for a new date range without having to re-create the dates.
The need to optimize rises from a bad design.My articles[^]
Oh, I think I got your point now. What you are saying is keep dates in a permanent table ( for a long dates, eg: 2000-01-01 to 2500-01-01) and use that table to join with the other table in the date range I want to.
I appreciate your help all the time... CodingLover :)
-
Oh, I think I got your point now. What you are saying is keep dates in a permanent table ( for a long dates, eg: 2000-01-01 to 2500-01-01) and use that table to join with the other table in the date range I want to.
I appreciate your help all the time... CodingLover :)
-
Exactly :)
The need to optimize rises from a bad design.My articles[^]
Thanks buddy. I'll give a try and let you know. So I'll keep this thread as it is. :)
I appreciate your help all the time... CodingLover :)
-
Hi all I want to count records in a table respect to each date. select count(id), add_date from packages group by add_date this gives results only for dates which has exist. Say I want to find the count in a range of dates, and if records not found then the count as zero. Is it possible to do this with MySQL
I appreciate your help all the time... CodingLover :)
I would suppose that there is some clever way to do this with an "integers" table. The following link shows an example used to create a range of dates. http://books.google.com/books?id=EoSNYVb5LAsC&pg=PA101&lpg=PA101&dq=%22integers+table%22+sql&source=bl&ots=-cOiNaw7DQ&sig=DKJMIdo4xUIIzepSw1icsnmCFkg&hl=en&ei=qrCcTez9DJOx0QGFv-nKAg&sa=X&oi=book_result&ct=result&resnum=6&ved=0CEEQ6AEwBQ#v=onepage&q=%22integers%20table%22%20sql&f=false[^]
-
Hi all I want to count records in a table respect to each date. select count(id), add_date from packages group by add_date this gives results only for dates which has exist. Say I want to find the count in a range of dates, and if records not found then the count as zero. Is it possible to do this with MySQL
I appreciate your help all the time... CodingLover :)