MySQL Coalesce again

Heydiho.

Ago some time I wrote a small document about MySQL’s COALESCE usage. Today I was asked to help with such a construct for a similar usage-scenario. Here’s the result:

If you’re new to this, read my first article first. It will make some things more obvious. You can read it here.

Basically we’re having two tables. The first stores when a shop has opened (ls_Oeffnungszeiten) and the second stores when a shop has closed (ls_Oeffnungszeiten_spezial) – Don’t worry about the german names for the tables and fields, wasn’t my idea and I’ll translate them in the following texts (Oeffnungszeiten = Opening times, spezial = special).

Table1: ls_Oeffnungszeiten

    FE              (id used to select the shop / distributor)
    tstamp          (some timestamp, not useful for us)
    tag             (tag = day)
    anfangszeit     (anfangszeit = start-time, timestamp)
    endzeit         (endzeit = end-time, timestamp)
    geschlossen     (geschlossen = closed, 16 for closed, 17 for opened)
    bestellanmahne  (wrongly written, should be bestellannahme, and means the time - opening time till someone can buy something (doesn't make sense to buy something 3 minutes before the shop closes, if they need 30 minutes to cook or ship it)

Table2: ls_Oeffnungszeiten_spezial

    FE              (id used to select the shop / distributor)
    tstamp          (some timestamp, not useful for us)
    anfangsdatum    (anfangsdatum = start-date, timestamp)
    enddatum        (enddatum = end-date, timestamp)
    geschlossen     (geschlossen = closed, 16 for closed, 17 for opened)

The field “geschlossen” in table2 might seem useless to you, because everything in table2 in fact means closed; however, for the COALESCE it will help; COALESCE works by using two fields, so if i use it, it has to have the same field in both tables. Thus i added “geschlossen” in table 2 with a default value of 16 (which means closed).

Some simple queries:

To get the distributors which have opened, we’d do:

    SELECT * FROM table1 WHERE geschlossen = '17'

Let’s make it a bit more complicated, to get the distributors which have opened RIGHT NOW, we’d do:

    WHERE WEEKDAY( NOW( ) ) = a.tag -1
    AND CURTIME( ) > a.anfangzeit
    AND CURTIME( ) < ( a.endzeit - a.bestellanmahme )

WEEKDAY gives us a DAY of the WEEK starting at 0, thus we need to do -1. Rest should be clear i guess. Let's take a look at the second table. Basically we've got a range from-to when the shop has closed. So to see when the shop has closed we'd do:

    SELECT * FROM table2 WHERE ( UNIX_TIMESTAMP() BETWEEN anfangsdatum AND enddatum )

Quite logical to asssume, that we need to use NOT BETWEEN to get the times a shop has opened; and as those will nearly always return NULL our COALESCE will just work fine. Because as we know from my previous COALESCE article, COALESCE selects the first NOT NULL field.

So let's put it all together and let's create a view out of it:

CREATE VIEW ls_Offen AS
    SELECT
        a.FE,
        COALESCE( b.geschlossen, a.geschlossen ) AS `status`
    FROM
        ls_Oeffnungszeiten AS a
    LEFT JOIN
        ls_Oeffnungszeiten_spezial AS b
    ON
        ( UNIX_TIMESTAMP( ) BETWEEN b.anfangsdatum AND b.enddatum )
        AND a.FE = b.FE
    WHERE
        WEEKDAY( NOW( ) ) = a.tag -1
    AND
        CURTIME( ) > a.anfangzeit
    AND
        CURTIME( ) < ( a.endzeit - a.bestellanmahme )

This will return two fields (FE) the distributor id and status -> which is either 16 or 17. So you just need to query that view, to see which distributors have opened.

Maybe I should look for a Job as MySQL-Query-Designer *cough*

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>