Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: To retrieve date in the query

    Hi All,
    I have 2 table
    create table bug_status
    (
    project_id text,
    days date,
    submitted integer,
    open integer,
    closed integer
    );
    And
    create table time_dimension
    (
    days date,
    yr integer,
    quarter integer,
    months integer
    );

    project days submitted
    P1 2010-04-21 23
    P1 2010-04-22 34
    P1 2010-04-26 12
    P1 2010-04-28 17

    In my time_dimension I have data from 2010 to 2011.

    There is requirement to have a output like
    project days submitted
    P1 2010-04-21 23
    P1 2010-04-22 34
    P1 2010-04-23 0
    P1 2010-04-24 0
    P1 2010-04-25 0
    P1 2010-04-26 12
    P1 2010-04-28 17

    Is this possible to do the query ?

    regards
    Debadutta

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Yeah, use a left join. And I assume there is another projects table some where.
    Code:
    SELECT p.project_id, t.days, COALESCE(bs.submitted, 0) AS submitted
    FROM project p
    JOIN time_dimension t ON t.days BETWEEN '2010-04-01' AND '2010-06-01'
    LEFT JOIN bug_status bs ON p.project_id = bs.project_id
      AND t.days = bs.days
    WHERE p.project_id = 'P1'

  3. #3
    Join Date
    Jul 2010
    Posts
    10
    thanks buddy for your quick reply.
    I also tried the same thing and it worked.

    Thanks
    debadutta

Posting Permissions

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