[thelist] sql spaghetti or script?

[Liz Lawson]

Hi

I've come up against one of those questions of good practice where being
self-taught starts to show...I've seen a lot of neat tutorials but not so
much real-world code.

I need to records from my db depending on the date. These records can be
active once only, annually, monthly, weekly, daily or always (default), and
they have a start and end date. If the active period is monthly, only the
day part of these dates is considered, and so on for
weekly, daily etc. The shortest rotation period has priority. When the dates
are inserted, unused parts of the dates will be given default values so I
can assume that they will always be the same.

In general, is trying to do the lot in one query acceptable, (query below,
if anyone wants to wade through it), or should I handle more in the
script and have a simpler query?

Liz




bloated query follows:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT
            sa.seasonalAreaType
           ,sa.seasonalAreaId
           ,sc.seasonalCodeString
           ,sc.seasonalCodeActive
         FROM
            seasonal_areas sa
           ,seasonal_codes sc
         WHERE
            sc.seasonalAreaId = sa.seasonalAreaId
         AND   sc.seasonalSetId = 1
         AND ( seasonalCodeType = '0_default'
             OR
             (seasonalCodeType = '1_annually' AND DAYOFYEAR(NOW()) BETWEEN
DAYOFYEAR(seasonalCodeStart) AND DAYOFYEAR(seasonalCodeEnd)
                     AND (
                               (DAYOFYEAR(NOW()) !=
DAYOFYEAR(seasonalCodeStart) AND DAYOFYEAR(NOW()) !=
DAYOFYEAR(seasonalCodeEnd))
                          OR   (DAYOFYEAR(NOW()) =
DAYOFYEAR(seasonalCodeStart) AND TIME_FORMAT(NOW(), '%T') >
TIME_FORMAT(seasonalCodeStart, '%T'))
                          OR   (DAYOFYEAR(NOW()) =
DAYOFYEAR(seasonalCodeEnd) AND TIME_FORMAT(NOW(), '%T') <
TIME_FORMAT(seasonalCodeEnd, '%T'))
                        )
                    )
             OR
             (seasonalCodeType = '2_monthly' AND DAYOFMONTH(NOW()) BETWEEN
DAYOFMONTH(seasonalCodeStart) AND DAYOFMONTH(seasonalCodeEnd)
                     AND (
                               (DAYOFMONTH(NOW()) !=
DAYOFMONTH(seasonalCodeStart) AND DAYOFMONTH(NOW()) !=
DAYOFMONTH(seasonalCodeEnd))
                          OR   (DAYOFMONTH(NOW()) =
DAYOFMONTH(seasonalCodeStart) AND TIME_FORMAT(NOW(), '%T') >
TIME_FORMAT(seasonalCodeStart, '%T'))
                          OR   (DAYOFMONTH(NOW()) =
DAYOFMONTH(seasonalCodeEnd) AND TIME_FORMAT(NOW(), '%T') <
TIME_FORMAT(seasonalCodeEnd, '%T'))
                        )
                    )
             OR
             (seasonalCodeType = '3_weekly' AND DAYOFWEEK(NOW()) BETWEEN
DAYOFWEEK(seasonalCodeStart) AND DAYOFWEEK(seasonalCodeEnd)
                     AND (
                               (DAYOFWEEK(NOW()) !=
DAYOFWEEK(seasonalCodeStart) AND DAYOFWEEK(NOW()) !=
DAYOFWEEK(seasonalCodeEnd))
                          OR   (DAYOFWEEK(NOW()) =
DAYOFWEEK(seasonalCodeStart) AND TIME_FORMAT(NOW(), '%T') >
TIME_FORMAT(seasonalCodeStart, '%T'))
                          OR   (DAYOFWEEK(NOW()) =
DAYOFWEEK(seasonalCodeEnd) AND TIME_FORMAT(NOW(), '%T') <
TIME_FORMAT(seasonalCodeEnd, '%T'))
                        )
                    )
             OR
             (seasonalCodeType = '4_daily'
                 AND TIME_FORMAT(NOW(), '%T') BETWEEN
TIME_FORMAT(seasonalCodeStart, '%T') AND TIME_FORMAT(seasonalCodeEnd, '%T')
                      )
             OR
               (seasonalCodeType = '5_one_off'
                 AND NOW() BETWEEN seasonalCodeStart AND seasonalCodeEnd
                     )
             )
             ORDER BY sc.seasonalCodeType

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

phew!