[thelist] sql spaghetti or script?

[Tab Alleman]

Seriously, what does your server say about it?  If it runs, and it runs fast
enough for ya, I'd keep it the way you have it.  I've written scripts that
have dynamically composed WHERE clauses that were so long, that Hal stopped
and said "I'm sorry Dave, I'm afraid I can't let you do that."  But I think
that generally, performance-wise, it's better to let your SQL do the work
rather than your script.

PS:  I'd really make this a stored procedure if that's an available option.

-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Liz Lawson
Sent: Friday, December 08, 2000 10:23 AM
To: thelist at lists.evolt.org
Subject: [thelist] sql spaghetti or script?


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!




---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !