MySQL Select all days in a month
master44
days.
i.e. Select id, date, count from table:
id date count
1 2/1/2010 10
2 2/3/2010 5
3 2/4/2010 4
4 2/5/2010 2
As you can see in my simplified example I do not have any data for
2/2/2010
What I'd like to do is join this query to something that selects all
the days for a date range so that I can make sure I account for every
day in a given month.
I'd like my data set to look like this:
id date count
1 2/1/2010 10
2/2/2010 0
2 2/3/2010 5
3 2/4/2010 4
4 2/5/2010 2
I know how to pull all dates in a month or date range within oracle,
but cannot figure out a way with mysql.. any ideas?
toby
Use a LEFT (outer) JOIN against a table of day numbers 1..31. See
also: LAST_DAY().
Lennart
One common way is to generate a calendar table and either use an outer
join or a union against that.
/Lennart
Erick T. Barkhuis
>On 5 Feb, 19:00, master44 <trp...@gmail.com> wrote:
>> What I'd like to do is join this query to something that selects
>>all the days for a date range so that I can make sure I account
>>for every day in a given month.
>One common way is to generate a calendar table and ...
This is such a common situation (a true FAQ), that I would expect a
database management system to have such table 'on board'. Isn't it
amazing, that merely every application in this World (using MySQL) has
to generate its own calendar table?
Am I the only one who thinks this is a shortcoming of MySQL (if such
table is indeed not built-in)?
--
Erick
J.O. Aho
Submit a patch and motivate why it should be added and if they think it's
worth to do then they add it.
--
//Aho
The Natural Philosopher
I'd far rather see a much more common situation addressed: namely many
to one correspondences implicitly connecting table fields and records.
So that when e.g. I find the same customer on the database in four
separate instances, I could easily combine them and all his orders would
magically now attach to the same person.
Erick T. Barkhuis
>Erick T. Barkhuis wrote:
>>Lennart:
>>
>>>On 5 Feb, 19:00, master44 <trp...@gmail.com> wrote:
>>
>>>>What I'd like to do is join this query to something that selects
>>>>all the days for a date range so that I can make sure I account
>>>>for every day in a given month.
>>
>>>One common way is to generate a calendar table and ...
>>
>>This is such a common situation (a true FAQ), that I would expect a
>>database management system to have such table 'on board'. Isn't it
>>amazing, that merely every application in this World (using MySQL)
>>has to generate its own calendar table?
>>
>>Am I the only one who thinks this is a shortcoming of MySQL (if such
>>table is indeed not built-in)?
>>
>I've got about 50 tables and I've never needed that one.
So, how do you show the number of orders for each calender day in the
system described below?
>I'd far rather see a much more common situation addressed: namely
>many to one correspondences implicitly connecting table fields and
>records.
You mean: foreign keys? Doesn't InnoDB address this?
>So that when e.g. I find the same customer on the database in four
>separate instances, I could easily combine them and all his orders
>would magically now attach to the same person.
I'm totally lost...how do you recognize "the same customer in four
instances" when not by unique ID? And if that's how you recognize them,
how can there be more than one of the same customer in the customer
table at all?
Sounds like you simply haven't normalized your database, or you allow
entry of "the same customer" multiple times in your application (which
would automatically mean, that two same curstomers are in fact separate
customers in that system).
--
Erick
Luuk
but customers can merge into one new customer
speyker + saab ==> speyker
there where 2, now there's only 1....
--
Luuk
Erick T. Barkhuis
>Op 6-2-2010 14:20, Erick T. Barkhuis schreef:
>> Sounds like you simply haven't normalized your database, or you
>>allow entry of "the same customer" multiple times in your
>>application (which would automatically mean, that two same
>>curstomers are in fact separate customers in that system).
>>
>>
>>
>
>but customers can merge into one new customer
>speyker + saab ==> speyker
>
>there where 2, now there's only 1....
Sounds like three different customers to me.
- Spijker (customer ID 12345)
- Saab (customer ID 32887)
- Spijker (customer ID 54321)
All you want to be able to is to transfer open orders from one customer
to another. But that's something that must be implemented in the
application anyway, in order to be able to correct data entry errors.
--
Erick
Beauregard T. Shagnasty
> but customers can merge into one new customer
>
> speyker + saab ==> speyker
>
> there where 2, now there's only 1....
If it were my database, I would not change the company ID in any
transactions that took place before the purchase/merger. In fact, I
would leave Saab in the company database (perhaps marked inactive so no
new transactions can occur), but history and auditing should demand that
Saab-only records remained unchanged and do not disappear.
--
-bts
-Four wheels carry the body; two wheels move the soul
Luuk
> Luuk wrote:
>
>> but customers can merge into one new customer
>>
>> speyker + saab ==> speyker
>>
>> there where 2, now there's only 1....
>
> If it were my database, I would not change the company ID in any
> transactions that took place before the purchase/merger. In fact, I
> would leave Saab in the company database (perhaps marked inactive so no
> new transactions can occur), but history and auditing should demand that
> Saab-only records remained unchanged and do not disappear.
>
exactly my opninion,
But what is youwant the historical data on this new customer?
i Think you also want to include information on the companies they
bought (so, including the saab-figures)
--
Luuk
Erick T. Barkhuis
>Op 6-2-2010 15:26, Beauregard T. Shagnasty schreef:
>> Luuk wrote:
>>
>>> but customers can merge into one new customer
>>>
>>> speyker + saab ==> speyker
>>>
>>> there where 2, now there's only 1....
>But what is youwant the historical data on this new customer?
>i Think you also want to include information on the companies they
>bought (so, including the saab-figures)
You could introduce a 1:n-relationship from Company to Company itself.
The implementation would be two fields:
- acquiredBy INT(5) [Foreign Key to CompanyID]
- acquiredDate DATE [Date acquired]
That way, you would know which Company bought which Company on which
date. It would enable you to get your information on each Company, or
on aggregate Companies.
--
Erick
Erick T. Barkhuis
>Luuk:
>>But what is youwant the historical data on this new customer?
>>i Think you also want to include information on the companies they
>>bought (so, including the saab-figures)
>
>
>You could introduce a 1:n-relationship from Company to Company itself.
>The implementation would be two fields:
>- acquiredBy INT(5) [Foreign Key to CompanyID]
>- acquiredDate DATE [Date acquired]
>
>That way, you would know which Company bought which Company on which
>date. It would enable you to get your information on each Company, or
>on aggregate Companies.
Next, you would want to enable your situation to register, when this
Company was sold to a third Company (as in: "On Feb 1st, Spijker buys
Saab. On May 1st, Spijker sells Saab to Opel. On Oct 1st, Opel sells
Saab to Volvo and buys Spijker.")
That would require a new entity "CompanyDeal", which is a relationship
entity between Company and Company. It would contain:
- dealID INT(3) auto increment PK
- dealDate DATE
- dealCompanyBuyer INT(5) FK to CompanyID
- dealCompanySeller INT(5) FK to CompanyID
This would theoretically enable you to keep track of all information
mentioned above, for each company and on aggregate.
Queries may become....errr....interesting, though. :-)
--
Erick
Beauregard T. Shagnasty
> Queries may become....errr....interesting, though. :-)
"I'll agree with that." :-)
(I am glad I am retired...)
strawberry
I like to keep a table of integers - ints(i INT) with values 0-9 - on
hand for just this sort of thing...
SELECT ADDDATE('2010-02-01',i2.i*10+i1.i) day FROM ints i1,ints i2
WHERE i2.i*10+i1.i < '2010-03-01';
+------------+
| day |
+------------+
| 2010-02-01 |
| 2010-02-02 |
| 2010-02-03 |
| 2010-02-04 |
| 2010-02-05 |
| 2010-02-06 |
| 2010-02-07 |
| 2010-02-08 |
| 2010-02-09 |
| 2010-02-10 |
| 2010-02-11 |
| 2010-02-12 |
| 2010-02-13 |
| 2010-02-14 |
| 2010-02-15 |
| 2010-02-16 |
| 2010-02-17 |
| 2010-02-18 |
| 2010-02-19 |
| 2010-02-20 |
| 2010-02-21 |
| 2010-02-22 |
| 2010-02-23 |
| 2010-02-24 |
| 2010-02-25 |
| 2010-02-26 |
| 2010-02-27 |
| 2010-02-28 |
+------------+
Luuk
i get 100 rows....
and i always tought that februari was a short month....
;-)
--
Luuk
Erick T. Barkhuis
>Op 6-2-2010 18:31, strawberry schreef:
>> SELECT ADDDATE('2010-02-01',i2.i*10+i1.i) day FROM ints i1,ints i2
>> WHERE i2.i*10+i1.i < '2010-03-01';
>
>i get 100 rows....
>
>and i always tought that februari was a short month....
>
>;-)
:-)
I think 'strawberry' attempted to describe this:
http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#95
--
Erick
strawberry
wrote:
That's a slightly different method - although my method is described
elsewhere on that page. Indeed, I think that's where I got it from.