MySQL Select all days in a month

#11

Op 6-2-2010 15:26, Beauregard T. Shagnasty schreef:

Quote:

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

#12

Luuk:

Quote:

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

#13

Erick T. Barkhuis:

Quote:

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

#14

Erick T. Barkhuis wrote:

Quote:

Queries may become....errr....interesting, though. :-)

"I'll agree with that." :-)

(I am glad I am retired...)

--
-bts
-Four wheels carry the body; two wheels move the soul

#15

On Feb 5, 6:00*pm, master44 <trp... (AT) gmail (DOT) com> wrote:

Quote:

I have a table that I am pulling results from, but there are missing
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?

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';
+------------+

Quote:

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 |
+------------+

#16

Op 6-2-2010 18:31, strawberry schreef:

Quote:

On Feb 5, 6:00 pm, master44 <trp... (AT) gmail (DOT) com> wrote:
I have a table that I am pulling results from, but there are missing
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?

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';

i get 100 rows....

and i always tought that februari was a short month....

;-)

--
Luuk

#17

Luuk:

Quote:

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/infotr...hp?&bw=1280#95

--
Erick

#18

On Feb 6, 5:57*pm, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:

Quote:

Luuk:

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/infot...hp?&bw=1280#95

--
Erick

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

MySQL Select all days in a month

comp.databases.mysql comp.databases.mysql

Re: MySQL Select all days in a month - 02-06-2010 , 10:03 AM

Re: MySQL Select all days in a month - 02-06-2010 , 10:14 AM

Re: MySQL Select all days in a month - 02-06-2010 , 10:19 AM

Re: MySQL Select all days in a month - 02-06-2010 , 10:49 AM

Re: MySQL Select all days in a month - 02-06-2010 , 11:31 AM

Re: MySQL Select all days in a month - 02-06-2010 , 11:50 AM

Re: MySQL Select all days in a month - 02-06-2010 , 11:57 AM

Re: MySQL Select all days in a month - 02-08-2010 , 09:09 AM