Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Question Unanswered: Create table for season/date comparison

    my problem,
    how to create table by SQL for faciliating the comparison of seasons (i.e. spring, summer, autumn and winter) with date.
    given the followings.
    Season months
    -------------------
    Spring March-May
    summer June-August
    autumn september-November
    winter December-February
    The require table
    Season date_start date_end
    ------------------------------
    Spring YY/MM/DD YY/MM/DD
    Summer YY/MM/DD YY/MM/DD
    Autumn YY/MM/DD YY/MM/DD
    Winter YY/MM/DD YY/MM/DD
    YY is a variable, start/end MM/DD is a fixed interval, Dec.-FeB
    date start/end is not in ascending order.
    Last edited by 1263264; 10-02-03 at 12:44.

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Smile Re: Create table for season/date comparison

    Originally posted by 1263264
    my problem,
    how to create table by SQL for faciliating the comparison of seasons (i.e. spring, summer, autumn and winter) with date.
    given the followings.
    Season months
    -------------------
    Spring March-May
    summer June-August
    autumn september-November
    winter December-February
    The require table
    Season date_start date_end
    ------------------------------
    Spring YY/MM/DD YY/MM/DD
    Summer YY/MM/DD YY/MM/DD
    Autumn YY/MM/DD YY/MM/DD
    Winter YY/MM/DD YY/MM/DD
    YY is a variable, start/end MM/DD is a fixed interval, Dec.-FeB
    date start/end is not in ascending order.
    My suggestion would be to have an input table and then an output/report table. You could do this with one table but that has some disadvantages, assuming that you plan to do summaries.

    The other question is do plan to end you season on dates or do do the seasons as whole months. That makes it easier.

    My suggestion would be to create the report table with 4 Yes/No (boolean) columns of Spring, Summer, Winter, Fall.
    When you do your consolidation into the report table you run date check as on your transaction date as
    Summer: IIf(Month([Sale_Date])=6 Or Month([Sale_Date])=7 Or Month([Sale_Date])=8 ),Yes,No).

    Follow this stye for the other seasons. You'll ne to do a validation check that all trans have a date in them.

    Then you can easily build a crosstab query on the seasons columns to output that on the reports.

    Just my .02
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Question

    Dear Jim P, Thanks a lot for your reply.
    Meanwhile, I try to solve the problem by ¡§create table¡¨ and ¡§insert¡¨ data as below. However, I have difficulties in inputting the¡§value¡¨ field.
    CREATE TABLE "season_interval_1" ("seasons" char(10) NOT NULL , "date_start" date NOT NULL , "date_end" date NOT NULL );
    INSERT INTO "season_interval_1" ( "seasons", "date_start", "date_end" )
    VALUES (I have difficulties to define the field value because YY is a variable, start/end MM/DD is a fixed interval, Dec.-Feb date start/end is not in ascending order.)
    I think that I have difficulties to use Month (Jan., Feb¡K.) to define¡§season¡¨ because the record for another related Column-table was defined by YY/MM/DD.
    Two tables below - data integrity problem between Observation date and season- I try to solve this problem by creating a season_interval table.
    Name Observation date l Name Season
    Xxx 04/15/1961 l xxx Spring
    grateful if you would enlighten me
    1263264
    Last edited by 1263264; 10-02-03 at 12:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •