Re: Range

Frank Bax wrote:
> At 06:17 PM 10/13/06, Keith Worthington wrote:
>
>> Notice the gap in the date sequence.
>> How can I generate a zero quantity to fill the gap?
>>
>> As always hints, suggestions and URLs for applicable documentation
>> will be appreciate.
>
>
>
> Check the archives of this list - same question was asked on Sep 30 with
> two replies.
> Subject line was "Potentially annoying question about date ranges".
>
> URL for list archive is included in msg headers of every email you get
> from the list.
>

Hmmm, I never knew that about the mail header.  I don't usually (ever)
have them turned on.  Mainly because they fill me screen and I can't see
the actual message.

I looked at the message and it helped me to understand the generate
series command a little bit.

I have come up with the following solution for my problem that I am
posting in the hopes that it will help someone else.  I would really
like a way to use an INTERVAL so that it is not necessary to generate
all the dates and then extract but I couldn't figure that part out.

-- Get all the direct and netting items so that if nothing has shipped
-- in the date range of interest we return a zero.
SELECT tbl_item.id AS item_id,
        month_series.ship_date,
        0::float4 AS quantity
   FROM tbl_item
  CROSS JOIN ( SELECT day_series.ship_date
                 FROM ( SELECT (
--                              Obtain the minimum ship date.
                                 SELECT min(DATE_TRUNC('MONTH',
                                                       tbl_detail.ship_date
                                                      )::date
                                           ) AS start_date
                                   FROM tbl_detail
--                              Add an integer from 0 to the number
--                              of days between the min and max ship
--                              date.
                               ) + integer_counter AS ship_date
                          FROM generate_series(
--                               The start date is the minimum ship date so
--                               add zero the first time.
                                  0,
                                  ( SELECT max(DATE_TRUNC('MONTH',

tbl_detail.ship_date
                                                         )::date
                                              )
                                      FROM tbl_detail
                                  ) -
                                  ( SELECT min(DATE_TRUNC('MONTH',

tbl_detail.ship_date
                                                         )::date
                                              )
                                      FROM tbl_detail
                                  ),
--                               Increment by one.
                                  1
                               ) AS integer_series(integer_counter)
                      ) AS day_series
                WHERE EXTRACT(DAY FROM day_series.ship_date) = 1
             ) AS month_series
WHERE tbl_item.item_type::text = 'DIR'::text
    OR tbl_item.item_type::text = 'NET'::text

This results in a table with every item  having a zero quantity for
every month between the first and the last month in a source table.  It
seemed like a lot of work to get there but it does work.

--

Kind Regards,
Keith