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