|
|
 Up |
|
|
  |
Author: MortenMorten Date: Apr 21, 2008 05:04
Hej, jeg har en applikation hvor brugeren vælger en dato range, fex.
2008-04-01 til 2008-04-03. Denne benyttes til at tælle antal events
der var "åbne" på den givne dato, altså for hver dato some_date findes
events hvor "some_date BETWEEN events.start_date AND events.end_date"
Udfordringen er, at få datoerne sendt til MySQL, her er en måde:
SELECT date_range.some_day, COUNT(*)
FROM events, (
SELECT DATE('2008-04-01') AS some_day FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL
) AS date_range
WHERE date_range.some_day BETWEEN events.start_date AND
events.end_date
GROUP BY 1
Er det den mest elegante måde? Altså, hvis der er 200 dage i den
initielle date range, så er det en pæn del UNION ALL's der skal til...
Nogen smartere måde? Jeg overvejer om jeg skal lave en stored
procedure som returnerer dagene givet start + slut, men vil helst
holde det i ren SQL om muligt...
|
| Show full article (1.01Kb) |
|
| | 6 Comments |
|
|
Author: Michael ZedelerMichael Zedeler Date: Apr 21, 2008 22:59
Morten wrote:
> Hej, jeg har en applikation hvor brugeren vælger en dato range, fex.
> 2008-04-01 til 2008-04-03. Denne benyttes til at tælle antal events
> der var "åbne" på den givne dato, altså for hver dato some_date findes
> events hvor "some_date BETWEEN events.start_date AND events.end_date"
>
> Udfordringen er, at få datoerne sendt til MySQL, her er en måde:
>
> SELECT date_range.some_day, COUNT(*)
> FROM events, (
> SELECT DATE('2008-04-01') AS some_day FROM DUAL UNION ALL
> SELECT DATE('2008-04-02') FROM DUAL UNION ALL
> SELECT DATE('2008-04-03') FROM DUAL
> ) AS date_range
> WHERE date_range.some_day BETWEEN events.start_date AND
> events.end_date
> GROUP BY 1
Hvorfor ikke bare noget i denne stil?
|
| Show full article (1.03Kb) |
|
| | no comments |
|
|
Author: MortenMorten Date: Apr 22, 2008 02:12
On Apr 22, 7:59 am, Michael Zedeler wrote:
> Morten wrote:
>> Hej, jeg har en applikation hvor brugeren vælger en dato range, fex.
>> 2008-04-01 til 2008-04-03. Denne benyttes til at tælle antal events
>> der var "åbne" på den givne dato, altså for hver dato some_date findes
>> events hvor "some_date BETWEEN events.start_date AND events.end_date"
>
>> Udfordringen er, at få datoerne sendt til MySQL, her er en måde:
>
>> SELECT date_range.some_day, COUNT(*)
>> FROM events, (
>> SELECT DATE('2008-04-01') AS some_day FROM DUAL UNION ALL
>> SELECT DATE('2008-04-02') FROM DUAL UNION ALL
>> SELECT DATE('2008-04-03') FROM DUAL
>> ) AS date_range
>> WHERE date_range.some_day BETWEEN events.start_date AND
>> events.end_date
>> GROUP BY 1
>
> Hvorfor ikke bare noget i denne stil? ...
|
| Show full article (1.59Kb) |
| no comments |
|
|
Author: Kristian Damm JensenKristian Damm Jensen Date: Apr 22, 2008 06:22
Michael Zedeler wrote:
> Morten wrote:
>> Hej, jeg har en applikation hvor brugeren vælger en dato range, fex.
>> 2008-04-01 til 2008-04-03. Denne benyttes til at tælle antal events
>> der var "åbne" på den givne dato, altså for hver dato some_date
>> findes events hvor "some_date BETWEEN events.start_date AND
>> events.end_date" Udfordringen er, at få datoerne sendt til MySQL, her er
>> en måde:
>>
>> SELECT date_range.some_day, COUNT(*)
>> FROM events, (
>> SELECT DATE('2008-04-01') AS some_day FROM DUAL UNION ALL
>> SELECT DATE('2008-04-02') FROM DUAL UNION ALL
>> SELECT DATE('2008-04-03') FROM DUAL
>> ) AS date_range
>> WHERE date_range.some_day BETWEEN events.start_date AND
>> events.end_date
>> GROUP BY 1
>
> Hvorfor ikke bare noget i denne stil? ...
|
| Show full article (1.87Kb) |
| no comments |
|
|
Author: Michael ZedelerMichael Zedeler Date: Apr 22, 2008 08:03
Morten wrote:
> On Apr 22, 7:59 am, Michael Zedeler wrote:
>> Morten wrote:
>>> Hej, jeg har en applikation hvor brugeren vælger en dato range, fex.
>>> 2008-04-01 til 2008-04-03. Denne benyttes til at tælle antal events
>>> der var "åbne" på den givne dato, altså for hver dato some_date findes
>>> events hvor "some_date BETWEEN events.start_date AND events.end_date"
>>> Udfordringen er, at få datoerne sendt til MySQL, her er en måde:
>>> SELECT date_range.some_day, COUNT(*)
>>> FROM events, (
>>> SELECT DATE('2008-04-01') AS some_day FROM DUAL UNION ALL
>>> SELECT DATE('2008-04-02') FROM DUAL UNION ALL
>>> SELECT DATE('2008-04-03') FROM DUAL
>>> ) AS date_range
>>> WHERE date_range.some_day BETWEEN events.start_date AND
>>> events.end_date
>>> GROUP BY 1
>> Hvorfor ikke bare noget i denne stil?
>>
>> select count(*), date(events.start_date) ...
|
| Show full article (1.83Kb) |
| no comments |
|
|
Author: MortenMorten Date: Apr 23, 2008 05:47
On Apr 22, 5:03 pm, Michael Zedeler wrote:
> Morten wrote:
>> On Apr 22, 7:59 am, Michael Zedeler wrote:
>>> Morten wrote:
>>>> Hej, jeg har en applikation hvor brugeren vælger en dato range, fex.
>>>> 2008-04-01 til 2008-04-03. Denne benyttes til at tælle antal events
>>>> der var "åbne" på den givne dato, altså for hver dato some_date findes
>>>> events hvor "some_date BETWEEN events.start_date AND events.end_date"
>>>> Udfordringen er, at få datoerne sendt til MySQL, her er en måde:
>>>> SELECT date_range.some_day, COUNT(*)
>>>> FROM events, (
>>>> SELECT DATE('2008-04-01') AS some_day FROM DUAL UNION ALL
>>>> SELECT DATE('2008-04-02') FROM DUAL UNION ALL
>>>> SELECT DATE('2008-04-03') FROM DUAL
>>>> ) AS date_range
>>>> WHERE date_range.some_day BETWEEN events.start_date AND
>>>> events.end_date
>>>> GROUP BY 1
>>> Hvorfor ikke bare noget i denne stil?
> ...
|
| Show full article (2.59Kb) |
| no comments |
|
|
|
|
|
Author: MortenMorten Date: Apr 23, 2008 06:50
> Der kommer en update fra mig hvis jeg nogensinde finder en løsning.
> Ellers er andre meget velkomne til at kaste et blik på ovenstående
> pastie.
|
| |
| no comments |
|
|
