Thread: Range
Hi All,
I am writing a query that UNIONs a bunch of data together. I would like to add
a query to the UNION that will insure that if nothing else is retrieved a zero
will be available.
The data I am retrieving in each part of the UNION looks something like this
item,quantity,ship_date
A, 10, 2006-01-01
A, 5, 2006-02-01
A, 3, 2006-02-01
A, 20, 2006-04-01
Notice the gap in the date sequence.
How can I generate a zero quantity to fill the gap? Either in the original
retrieval or as a seperate query for the UNION or some other way so that when I
GROUP BY the date and SUM the quantity I end up with
item,quantity,ship_date
A, 10, 2006-01-01
A, 8, 2006-02-01
A, 0, 2006-03-01
A, 20, 2006-04-01
The queries whose results are being assembled with the UNION all have the form
SELECT item,
quantity,
DATE_TRUNC('MONTH', source_table.ship_date)::date AS ship_date
FROM source_table;
As always hints, suggestions and URLs for applicable documentation will be
appreciate.
Kind Regards,
Keith
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.
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