Baz said:
Switch([Sport_Date] Between #01/01/2004# And #05/30/2004#,"Group
A",[Sport_Date] Between #05/01/2004# And #09/30/2004#,"Group B",[Sport_Date]
Between #01/01/2005# And #06/30/2005#,"Group C")
Hard coding the dates in this way may be a maintenance issue i.e. if
the group dates change then a whole bunch of SQL code may need to be
changed. As an alternative, the groups and respective dates could be
held in a separate table then just this table needs to be altered when
the dates change (and not the SQL code).
- you seem to have a 1-month overlap between groups A and B
- you have a 3-month gap between groups B and C.
Yes, cases which you code doesn't handle very well ;-)
The problem with Switch is that the 'cases' are mutually exclusive i.e.
it returns the value associated with the first TRUE value (although
IIRC all cases are actually evaluated, meaning no short circuiting and
all must be valid for all values).
Here's a demo of my suggested alternative approach using a separate
table ...
- the US-format dates are deliberate.
.... in which the use of ISO standard date formats is deliberate <g> and
handles the overlapped/omitted groups:
CREATE TABLE Groups (
group_code CHAR(1) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
CHECK(
HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND(start_date) = 0
),
end_date DATETIME,
CHECK(
HOUR(end_date) = 0
AND MINUTE(end_date) = 0
AND SECOND(end_date) = 0
),
CHECK(start_date < end_date)
)
;
INSERT INTO Groups
VALUES ('A', #2004-01-01#, #2004-05-30#)
;
INSERT INTO Groups
VALUES ('B', #2004-05-01#, #2004-09-30#)
;
INSERT INTO Groups
VALUES ('C', #2005-01-01#, #2005-06-30#)
;
CREATE TABLE Sports (
sport_date DATETIME NOT NULL,
CHECK(
HOUR(sport_date) = 0
AND MINUTE(sport_date) = 0
AND SECOND(sport_date) = 0
)
)
;
INSERT INTO Sports VALUES (#2004-06-03#)
;
INSERT INTO Sports VALUES (#2004-02-02#)
;
INSERT INTO Sports VALUES (#2004-05-15#)
;
INSERT INTO Sports VALUES (#2004-12-31#)
;
SELECT Sports.sport_date, Groups.group_code
FROM Sports
LEFT JOIN
Groups ON (
Sports.sport_date BETWEEN
Groups.start_date AND Groups.end_date
)
;
