[thelist] sql spaghetti or script?
Liz Lawson
lizlawson at charitycards.co.uk
Fri Dec 8 09:24:00 CST 2000
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!
More information about the thelist
mailing list