Обсуждение: Joining a series of dates

Hi All,

I am trying to generate a dynamic date value as part of a much larger query.
The date must be at least one day prior to the ship date and must not be a
weekend or a holiday.

I have created a table to hold the holidays and I am now trying to develop the
query to give me the date.  In the code below I have hard coded the order date
and the ship date but in the final query these are already extracted as part
of the larger query.

This is my first time using a series and I am not sure how to connect it to
the holiday table.  There may be other mistakes in my query as well.  At this
time the error that I am getting is that the dates column soen't exist.

Any hints or pointers to relevant documenation will be appreciated.

CREATE TABLE tbl_holidays
(
  holiday date NOT NULL,
  CONSTRAINT tbl_holidays_pkey PRIMARY KEY (holiday)
)
WITHOUT OIDS;

INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-24'::date);
INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-25'::date);
INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-26'::date);
INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-30'::date);

SELECT '2005-09-07'::date + s.a AS dates  --Generate a years worth of
  FROM generate_series(0,365) AS s(a)     --possible ship dates starting
                                          --with the date the order was
                                          --placed.
  JOIN tbl_holidays                       --Join to the holiday table to
    ON ( dates = tbl_holidays.holiday )   --eliminate holidays from the
                                          --series.
 WHERE dates <= ( '2005-09-12'::date -    --The date must be at least
                  interval '1 day'        --one day prior to the ship date.
               )::date
   AND extract( dow FROM dates            --The date must be during the
              ) IN (1, 2, 3, 4, 5)        --work week.

Kind Regards,
Keith

On Tue, 13 Sep 2005 12:00:54 -0400, Keith Worthington wrote
> Hi All,
>
> I am trying to generate a dynamic date value as part of a much
> larger query. The date must be at least one day prior to the ship
> date and must not be a weekend or a holiday.
>
> I have created a table to hold the holidays and I am now trying to
> develop the query to give me the date.  In the code below I have
> hard coded the order date and the ship date but in the final query
> these are already extracted as part of the larger query.
>
> This is my first time using a series and I am not sure how to
> connect it to the holiday table.  There may be other mistakes in my
> query as well.  At this time the error that I am getting is that the
> dates column soen't exist.
>
> Any hints or pointers to relevant documenation will be appreciated.
>
> CREATE TABLE tbl_holidays
> (
>   holiday date NOT NULL,
>   CONSTRAINT tbl_holidays_pkey PRIMARY KEY (holiday)
> )
> WITHOUT OIDS;
>
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-24'::date);
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-11-25'::date);
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-26'::date);
> INSERT INTO tbl_holidays (holiday) VALUES ('2005-12-30'::date);
>
> SELECT '2005-09-07'::date + s.a AS dates  --Generate a years worth of
>   FROM generate_series(0,365) AS s(a)     --possible ship dates starting
>                                           --with the date the order was
>                                           --placed.
>   JOIN tbl_holidays                       --Join to the holiday
> table to    ON ( dates = tbl_holidays.holiday )   --eliminate
> holidays from the                                          --series.
> WHERE dates <= ( '2005-09-12'::date -    --The date must be at least
>                  interval '1 day'        --one day prior to the ship
> date.               )::date   AND extract( dow FROM dates
> --The date must be during the              ) IN (1, 2, 3, 4, 5)
>   --work week.
>
> Kind Regards,
> Keith

Replying to myself.

Here is what I have figured out.  I can use two querys to get the data and
then EXCEPT them together to eliminate the holidays.  That part works fine.
So using 2005-09-07 as an order date and 2005-11-28 as a ship date this is
what I end up with.

SELECT max( dates ) AS completion_date
  FROM ( SELECT '2005-09-07'::date + s.a AS dates
           FROM generate_series(0,365) AS s(a)
         EXCEPT
         SELECT holiday
           FROM interface.tbl_holidays
       ) AS valid_dates
 WHERE dates <= ('2005-11-28'::date - interval '1 day')::date
   AND NOT extract(dow FROM dates) IN (0, 6);

Out drops 2005-11-23 which is one working day prior to the ship date. :-)

Now, unfortunately, it gets ugly.  I place this into the larger query and my
execution time skyrockets from 668ms to 53804ms. :-(

Here is the complete query.
EXPLAIN ANALYZE SELECT merged_line_items.so_number,
        merged_line_items.so_line,
        merged_line_items.quantity AS line_item_quantity,
        merged_line_items.item_id AS line_item_id,
        merged_line_items.item_type AS line_item_type,
        merged_line_items.description AS line_item_description,
        merged_sales_orders.customer_name,
        merged_sales_orders.ship_to_name,
        merged_sales_orders.ship_to_state,
        merged_sales_orders.so_date AS order_date,
        merged_sales_orders.ship_by_date,

(
SELECT max( dates )
  FROM ( SELECT merged_sales_orders.so_date + s.a AS dates
           FROM generate_series(0,730) AS s(a)
         EXCEPT
         SELECT holiday
           FROM interface.tbl_holidays
       ) AS valid_dates
 WHERE dates <= (merged_sales_orders.ship_by_date - interval '1 day')::date
   AND NOT extract(dow FROM dates) IN (0, 6)
) AS completion_date,

        merged_sales_orders.so_note,
        production_notes.description AS line_item_production_note,
        merged_boms.so_subline,
        merged_boms.quantity AS bom_quantity,
        merged_boms.item_id AS bom_item_id,
        merged_boms.item_type AS bom_item_type,
        merged_boms.description AS bom_item_description,
        trunc((merged_boms.length_in / 12::real)::double precision)::integer
AS length_ft,
        merged_boms.length_in - 12::real * trunc((merged_boms.length_in /
12::real)::double precision) AS length_in,
        trunc((merged_boms.width_in / 12::real)::double precision)::integer AS
width_ft,
        merged_boms.width_in - 12::real * trunc((merged_boms.width_in /
12::real)::double precision) AS width_in,
        round((merged_boms.length_in / merged_boms.mesh_size)::double
precision)::integer AS length_bars,
        round((merged_boms.width_in / merged_boms.mesh_size)::double
precision)::integer AS width_bars,
        merged_boms.tension
   FROM ( SELECT tbl_line_item.so_number, tbl_line_item.so_line,
tbl_line_item.quantity, tbl_line_item.item_id, tbl_item.item_type,
tbl_item_description.description
           FROM sales_order.tbl_line_item
      LEFT JOIN sales_order.tbl_item_description ON tbl_line_item.so_number =
tbl_item_description.so_number AND tbl_line_item.so_line =
tbl_item_description.so_line
   LEFT JOIN peachtree.tbl_item ON tbl_line_item.item_id::text = tbl_item.id::text
  WHERE tbl_item.item_type::text = 'BAY'::text OR tbl_item.item_type::text =
'NET'::text OR tbl_item.item_type::text = 'VAS'::text) merged_line_items
   LEFT JOIN ( SELECT tbl_customer.name AS customer_name,
tbl_detail.so_number, tbl_detail.ship_to_name, tbl_detail.ship_to_state,
tbl_detail.so_date, tbl_detail.ship_by_date, tbl_note.description AS so_note
           FROM sales_order.tbl_detail
      LEFT JOIN sales_order.tbl_note ON tbl_detail.so_number = tbl_note.so_number
   LEFT JOIN peachtree.tbl_customer ON tbl_detail.customer_id::text =
tbl_customer.id::text) merged_sales_orders ON merged_line_items.so_number =
merged_sales_orders.so_number
   LEFT JOIN ( SELECT tbl_line_item.so_number, tbl_line_item.so_line - 1 AS
so_line, tbl_line_item.item_id, tbl_item.item_type, tbl_item.description
      FROM sales_order.tbl_line_item
   LEFT JOIN peachtree.tbl_item ON tbl_line_item.item_id::text = tbl_item.id::text
  WHERE tbl_item.item_type::text = 'PRO'::text AND tbl_item.id::text <> 'PN'::text
UNION ALL
    SELECT tbl_line_item.so_number, tbl_line_item.so_line - 1 AS so_line,
tbl_line_item.item_id, tbl_item.item_type, tbl_item_description.description
      FROM sales_order.tbl_line_item
   LEFT JOIN sales_order.tbl_item_description ON tbl_line_item.so_number =
tbl_item_description.so_number AND tbl_line_item.so_line =
tbl_item_description.so_line
   LEFT JOIN peachtree.tbl_item ON tbl_line_item.item_id::text = tbl_item.id::text
  WHERE tbl_item.id::text = 'PN'::text) production_notes ON
merged_line_items.so_number = production_notes.so_number AND
merged_line_items.so_line = production_notes.so_line
   LEFT JOIN ( SELECT tbl_item_bom.so_number, tbl_item_bom.so_line,
tbl_item_bom.so_subline, tbl_item_bom.quantity, tbl_item_bom.item_id,
tbl_item.item_type, tbl_item.description, tbl_mesh.mesh_size,
tbl_net_production.length_in, tbl_net_production.width_in,
tbl_net_production.tension
   FROM sales_order.tbl_item_bom
   LEFT JOIN peachtree.tbl_item ON tbl_item_bom.item_id::text = tbl_item.id::text
   LEFT JOIN peachtree.tbl_mesh ON tbl_item_bom.item_id::text =
tbl_mesh.item_id::text
   LEFT JOIN sales_order.tbl_net_production ON tbl_item_bom.so_number =
tbl_net_production.so_number AND tbl_item_bom.so_line =
tbl_net_production.so_line AND tbl_item_bom.so_subline =
tbl_net_production.so_subline) merged_boms ON merged_line_items.so_number =
merged_boms.so_number AND merged_line_items.so_line = merged_boms.so_line
  ORDER BY merged_line_items.so_number, merged_line_items.so_line,
merged_boms.so_subline;

Here is the EXPLAIN ANALYZE output with the new code.
"Sort  (cost=880645.11..880670.94 rows=10334 width=317) (actual
time=53785.664..53790.550 rows=7885 loops=1)"
"  Sort Key: tbl_line_item.so_number, tbl_line_item.so_line,
tbl_item_bom.so_subline"
"  ->  Merge Left Join  (cost=5732.80..879956.08 rows=10334 width=317) (actual
time=508.497..53762.260 rows=7885 loops=1)"
"        Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"        ->  Merge Left Join  (cost=4497.19..4578.95 rows=10334 width=238)
(actual time=296.662..329.011 rows=3165 loops=1)"
"              Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"              ->  Sort  (cost=3683.31..3709.14 rows=10334 width=206) (actual
time=247.378..249.776 rows=3165 loops=1)"
"                    Sort Key: tbl_line_item.so_line, tbl_line_item.so_number"
"                    ->  Hash Left Join  (cost=1526.72..2994.28 rows=10334
width=206) (actual time=73.057..226.797 rows=3165 loops=1)"
"                          Hash Cond: ("outer".so_number = "inner".so_number)"
"                          ->  Hash Left Join  (cost=196.80..1103.20
rows=10334 width=94) (actual time=9.012..146.714 rows=3165 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                Filter: ((("inner".item_type)::text =
'BAY'::text) OR (("inner".item_type)::text = 'NET'::text) OR
(("inner".item_type)::text = 'VAS'::text))"
"                                ->  Merge Left Join  (cost=0.00..673.88
rows=10334 width=86) (actual time=0.211..83.295 rows=10351 loops=1)"
"                                      Merge Cond: (("outer".so_number =
"inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                      ->  Index Scan using tbl_line_item_pkey
on tbl_line_item  (cost=0.00..368.45 rows=10334 width=20) (actual
time=0.111..21.762 rows=10351 loops=1)"
"                                      ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.077..11.356 rows=5329 loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=19) (actual time=7.814..7.814 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=19) (actual time=0.062..4.763 rows=2248
loops=1)"
"                          ->  Hash  (cost=1287.39..1287.39 rows=17012
width=116) (actual time=63.693..63.693 rows=0 loops=1)"
"                                ->  Hash Left Join  (cost=271.35..1287.39
rows=17012 width=116) (actual time=24.193..59.081 rows=2447 loops=1)"
"                                      Hash Cond: (("outer".customer_id)::text
= ("inner".id)::text)"
"                                      ->  Hash Left Join  (cost=9.60..770.46
rows=17012 width=102) (actual time=2.389..20.564 rows=2447 loops=1)"
"                                            Hash Cond: ("outer".so_number =
"inner".so_number)"
"                                            ->  Seq Scan on tbl_detail
(cost=0.00..672.12 rows=17012 width=51) (actual time=0.467..7.620 rows=2447
loops=1)"
"                                            ->  Hash  (cost=8.68..8.68
rows=368 width=55) (actual time=1.260..1.260 rows=0 loops=1)"
"                                                  ->  Seq Scan on tbl_note
(cost=0.00..8.68 rows=368 width=55) (actual time=0.007..0.707 rows=369 loops=1)"
"                                      ->  Hash  (cost=244.60..244.60
rows=6860 width=34) (actual time=21.030..21.030 rows=0 loops=1)"
"                                            ->  Seq Scan on tbl_customer
(cost=0.00..244.60 rows=6860 width=34) (actual time=0.529..12.765 rows=3470
loops=1)"
"              ->  Sort  (cost=813.89..815.25 rows=545 width=40) (actual
time=49.231..49.644 rows=493 loops=1)"
"                    Sort Key: production_notes.so_line,
production_notes.so_number"
"                    ->  Subquery Scan production_notes  (cost=12.49..789.12
rows=545 width=40) (actual time=28.682..48.044 rows=493 loops=1)"
"                          ->  Append  (cost=12.49..783.67 rows=545 width=90)
(actual time=28.677..46.974 rows=493 loops=1)"
"                                ->  Subquery Scan "*SELECT* 1"
(cost=12.49..283.82 rows=14 width=52) (actual time=19.815..19.815 rows=0 loops=1)"
"                                      ->  Hash Join  (cost=12.49..283.68
rows=14 width=52) (actual time=19.813..19.813 rows=0 loops=1)"
"                                            Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)"
"                                            ->  Seq Scan on tbl_line_item
(cost=0.00..219.34 rows=10334 width=16) (actual time=0.004..10.130 rows=10351
loops=1)"
"                                            ->  Hash  (cost=12.48..12.48
rows=6 width=47) (actual time=0.145..0.145 rows=0 loops=1)"
"                                                  ->  Index Scan using
idx_tbl_item_item_type on tbl_item  (cost=0.00..12.48 rows=6 width=47) (actual
time=0.110..0.123 rows=2 loops=1)"
"                                                        Index Cond:
((item_type)::text = 'PRO'::text)"
"                                                        Filter: ((id)::text
<> 'PN'::text)"
"                                ->  Subquery Scan "*SELECT* 2"
(cost=269.21..499.85 rows=531 width=90) (actual time=8.857..26.555 rows=493
loops=1)"
"                                      ->  Nested Loop  (cost=269.21..494.54
rows=531 width=90) (actual time=8.851..25.480 rows=493 loops=1)"
"                                            ->  Index Scan using
tbl_part_pkey on tbl_item  (cost=0.00..3.07 rows=1 width=19) (actual
time=0.078..0.083 rows=1 loops=1)"
"                                                  Index Cond: ((id)::text =
'PN'::text)"
"                                            ->  Merge Right Join
(cost=269.21..484.83 rows=531 width=82) (actual time=8.757..23.975 rows=493
loops=1)"
"                                                  Merge Cond:
(("outer".so_number = "inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                                  ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.009..8.025 rows=5329 loops=1)"
"                                                  ->  Sort
(cost=269.21..270.54 rows=531 width=16) (actual time=8.675..9.020 rows=493
loops=1)"
"                                                        Sort Key:
tbl_line_item.so_number, tbl_line_item.so_line"
"                                                        ->  Seq Scan on
tbl_line_item  (cost=0.00..245.18 rows=531 width=16) (actual time=0.043..7.946
rows=493 loops=1)"
"                                                              Filter:
('PN'::text = (item_id)::text)"
"        ->  Sort  (cost=1235.61..1255.06 rows=7780 width=85) (actual
time=203.564..209.484 rows=7787 loops=1)"
"              Sort Key: tbl_item_bom.so_line, tbl_item_bom.so_number"
"              ->  Hash Left Join  (cost=278.85..732.81 rows=7780 width=85)
(actual time=20.843..149.866 rows=7787 loops=1)"
"                    Hash Cond: (("outer".so_number = "inner".so_number) AND
("outer".so_line = "inner".so_line) AND ("outer".so_subline =
"inner".so_subline))"
"                    ->  Hash Left Join  (cost=198.79..536.03 rows=7780
width=66) (actual time=10.046..98.495 rows=7787 loops=1)"
"                          Hash Cond: (("outer".item_id)::text =
("inner".item_id)::text)"
"                          ->  Hash Left Join  (cost=196.80..465.30 rows=7780
width=62) (actual time=8.814..64.220 rows=7787 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                ->  Seq Scan on tbl_item_bom
(cost=0.00..151.80 rows=7780 width=26) (actual time=0.004..9.964 rows=7787
loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=47) (actual time=8.130..8.130 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=47) (actual time=0.061..5.080 rows=2248
loops=1)"
"                          ->  Hash  (cost=1.79..1.79 rows=79 width=18)
(actual time=0.242..0.242 rows=0 loops=1)"
"                                ->  Seq Scan on tbl_mesh  (cost=0.00..1.79
rows=79 width=18) (actual time=0.009..0.122 rows=79 loops=1)"
"                    ->  Hash  (cost=58.61..58.61 rows=2861 width=29) (actual
time=10.062..10.062 rows=0 loops=1)"
"                          ->  Seq Scan on tbl_net_production
(cost=0.00..58.61 rows=2861 width=29) (actual time=0.013..5.636 rows=2865
loops=1)"
"        SubPlan"
"          ->  Aggregate  (cost=84.51..84.51 rows=1 width=4) (actual
time=6.728..6.729 rows=1 loops=7885)"
"                ->  Subquery Scan valid_dates  (cost=76.14..84.42 rows=33
width=4) (actual time=4.001..6.718 rows=9 loops=7885)"
"                      Filter: ((dates <= (($0 - '1 day'::interval))::date)
AND (date_part('dow'::text, (dates)::timestamp without time zone) <> 0::double
precision) AND (date_part('dow'::text, (dates)::timestamp without time zone)
<> 6::double precision))"
"                      ->  SetOp Except  (cost=76.14..81.16 rows=100 width=4)
(actual time=3.922..5.445 rows=719 loops=7885)"
"                            ->  Sort  (cost=76.14..78.65 rows=1004 width=4)
(actual time=3.902..4.376 rows=744 loops=7885)"
"                                  Sort Key: dates"
"                                  ->  Append  (cost=0.00..26.08 rows=1004
width=4) (actual time=0.240..3.079 rows=744 loops=7885)"
"                                        ->  Subquery Scan "*SELECT* 1"
(cost=0.00..25.00 rows=1000 width=4) (actual time=0.238..2.113 rows=731
loops=7885)"
"                                              ->  Function Scan on
generate_series s  (cost=0.00..15.00 rows=1000 width=4) (actual
time=0.219..0.964 rows=731 loops=7885)"
"                                        ->  Subquery Scan "*SELECT* 2"
(cost=0.00..1.08 rows=4 width=4) (actual time=0.005..0.038 rows=13 loops=7885)"
"                                              ->  Seq Scan on tbl_holidays
(cost=0.00..1.04 rows=4 width=4) (actual time=0.004..0.017 rows=13 loops=7885)"
"Total runtime: 53804.044 ms"



Here is the EXPLAIN ANALYZE output without the new code.
"Sort  (cost=7368.90..7394.74 rows=10334 width=317) (actual
time=650.959..655.829 rows=7885 loops=1)"
"  Sort Key: tbl_line_item.so_number, tbl_line_item.so_line,
tbl_item_bom.so_subline"
"  ->  Merge Left Join  (cost=5732.80..6679.88 rows=10334 width=317) (actual
time=496.260..628.888 rows=7885 loops=1)"
"        Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"        ->  Merge Left Join  (cost=4497.19..4578.95 rows=10334 width=238)
(actual time=293.152..318.099 rows=3165 loops=1)"
"              Merge Cond: (("outer".so_line = "inner".so_line) AND
("outer".so_number = "inner".so_number))"
"              ->  Sort  (cost=3683.31..3709.14 rows=10334 width=206) (actual
time=244.063..246.058 rows=3165 loops=1)"
"                    Sort Key: tbl_line_item.so_line, tbl_line_item.so_number"
"                    ->  Hash Left Join  (cost=1526.72..2994.28 rows=10334
width=206) (actual time=70.484..224.614 rows=3165 loops=1)"
"                          Hash Cond: ("outer".so_number = "inner".so_number)"
"                          ->  Hash Left Join  (cost=196.80..1103.20
rows=10334 width=94) (actual time=8.959..147.018 rows=3165 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                Filter: ((("inner".item_type)::text =
'BAY'::text) OR (("inner".item_type)::text = 'NET'::text) OR
(("inner".item_type)::text = 'VAS'::text))"
"                                ->  Merge Left Join  (cost=0.00..673.88
rows=10334 width=86) (actual time=0.192..82.894 rows=10351 loops=1)"
"                                      Merge Cond: (("outer".so_number =
"inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                      ->  Index Scan using tbl_line_item_pkey
on tbl_line_item  (cost=0.00..368.45 rows=10334 width=20) (actual
time=0.092..20.062 rows=10351 loops=1)"
"                                      ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.077..12.190 rows=5329 loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=19) (actual time=7.787..7.787 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=19) (actual time=0.063..4.680 rows=2248
loops=1)"
"                          ->  Hash  (cost=1287.39..1287.39 rows=17012
width=116) (actual time=61.143..61.143 rows=0 loops=1)"
"                                ->  Hash Left Join  (cost=271.35..1287.39
rows=17012 width=116) (actual time=26.560..57.356 rows=2447 loops=1)"
"                                      Hash Cond: (("outer".customer_id)::text
= ("inner".id)::text)"
"                                      ->  Hash Left Join  (cost=9.60..770.46
rows=17012 width=102) (actual time=2.907..21.550 rows=2447 loops=1)"
"                                            Hash Cond: ("outer".so_number =
"inner".so_number)"
"                                            ->  Seq Scan on tbl_detail
(cost=0.00..672.12 rows=17012 width=51) (actual time=0.882..11.461 rows=2447
loops=1)"
"                                            ->  Hash  (cost=8.68..8.68
rows=368 width=55) (actual time=1.346..1.346 rows=0 loops=1)"
"                                                  ->  Seq Scan on tbl_note
(cost=0.00..8.68 rows=368 width=55) (actual time=0.066..0.784 rows=369 loops=1)"
"                                      ->  Hash  (cost=244.60..244.60
rows=6860 width=34) (actual time=22.899..22.899 rows=0 loops=1)"
"                                            ->  Seq Scan on tbl_customer
(cost=0.00..244.60 rows=6860 width=34) (actual time=0.729..14.626 rows=3470
loops=1)"
"              ->  Sort  (cost=813.89..815.25 rows=545 width=40) (actual
time=49.035..49.368 rows=493 loops=1)"
"                    Sort Key: production_notes.so_line,
production_notes.so_number"
"                    ->  Subquery Scan production_notes  (cost=12.49..789.12
rows=545 width=40) (actual time=28.696..47.889 rows=493 loops=1)"
"                          ->  Append  (cost=12.49..783.67 rows=545 width=90)
(actual time=28.691..46.793 rows=493 loops=1)"
"                                ->  Subquery Scan "*SELECT* 1"
(cost=12.49..283.82 rows=14 width=52) (actual time=19.932..19.932 rows=0 loops=1)"
"                                      ->  Hash Join  (cost=12.49..283.68
rows=14 width=52) (actual time=19.929..19.929 rows=0 loops=1)"
"                                            Hash Cond:
(("outer".item_id)::text = ("inner".id)::text)"
"                                            ->  Seq Scan on tbl_line_item
(cost=0.00..219.34 rows=10334 width=16) (actual time=0.004..10.194 rows=10351
loops=1)"
"                                            ->  Hash  (cost=12.48..12.48
rows=6 width=47) (actual time=0.148..0.148 rows=0 loops=1)"
"                                                  ->  Index Scan using
idx_tbl_item_item_type on tbl_item  (cost=0.00..12.48 rows=6 width=47) (actual
time=0.113..0.126 rows=2 loops=1)"
"                                                        Index Cond:
((item_type)::text = 'PRO'::text)"
"                                                        Filter: ((id)::text
<> 'PN'::text)"
"                                ->  Subquery Scan "*SELECT* 2"
(cost=269.21..499.85 rows=531 width=90) (actual time=8.756..26.255 rows=493
loops=1)"
"                                      ->  Nested Loop  (cost=269.21..494.54
rows=531 width=90) (actual time=8.750..25.156 rows=493 loops=1)"
"                                            ->  Index Scan using
tbl_part_pkey on tbl_item  (cost=0.00..3.07 rows=1 width=19) (actual
time=0.061..0.066 rows=1 loops=1)"
"                                                  Index Cond: ((id)::text =
'PN'::text)"
"                                            ->  Merge Right Join
(cost=269.21..484.83 rows=531 width=82) (actual time=8.673..23.692 rows=493
loops=1)"
"                                                  Merge Cond:
(("outer".so_number = "inner".so_number) AND ("outer".so_line = "inner".so_line))"
"                                                  ->  Index Scan using
tbl_item_description_pkey on tbl_item_description  (cost=0.00..182.75
rows=5319 width=72) (actual time=0.009..7.869 rows=5329 loops=1)"
"                                                  ->  Sort
(cost=269.21..270.54 rows=531 width=16) (actual time=8.595..8.925 rows=493
loops=1)"
"                                                        Sort Key:
tbl_line_item.so_number, tbl_line_item.so_line"
"                                                        ->  Seq Scan on
tbl_line_item  (cost=0.00..245.18 rows=531 width=16) (actual time=0.043..7.898
rows=493 loops=1)"
"                                                              Filter:
('PN'::text = (item_id)::text)"
"        ->  Sort  (cost=1235.61..1255.06 rows=7780 width=85) (actual
time=203.038..208.112 rows=7787 loops=1)"
"              Sort Key: tbl_item_bom.so_line, tbl_item_bom.so_number"
"              ->  Hash Left Join  (cost=278.85..732.81 rows=7780 width=85)
(actual time=20.913..150.811 rows=7787 loops=1)"
"                    Hash Cond: (("outer".so_number = "inner".so_number) AND
("outer".so_line = "inner".so_line) AND ("outer".so_subline =
"inner".so_subline))"
"                    ->  Hash Left Join  (cost=198.79..536.03 rows=7780
width=66) (actual time=9.832..98.817 rows=7787 loops=1)"
"                          Hash Cond: (("outer".item_id)::text =
("inner".item_id)::text)"
"                          ->  Hash Left Join  (cost=196.80..465.30 rows=7780
width=62) (actual time=8.608..64.237 rows=7787 loops=1)"
"                                Hash Cond: (("outer".item_id)::text =
("inner".id)::text)"
"                                ->  Seq Scan on tbl_item_bom
(cost=0.00..151.80 rows=7780 width=26) (actual time=0.004..10.151 rows=7787
loops=1)"
"                                ->  Hash  (cost=185.64..185.64 rows=4464
width=47) (actual time=7.953..7.953 rows=0 loops=1)"
"                                      ->  Seq Scan on tbl_item
(cost=0.00..185.64 rows=4464 width=47) (actual time=0.061..4.939 rows=2248
loops=1)"
"                          ->  Hash  (cost=1.79..1.79 rows=79 width=18)
(actual time=0.237..0.237 rows=0 loops=1)"
"                                ->  Seq Scan on tbl_mesh  (cost=0.00..1.79
rows=79 width=18) (actual time=0.010..0.122 rows=79 loops=1)"
"                    ->  Hash  (cost=58.61..58.61 rows=2861 width=29) (actual
time=10.329..10.329 rows=0 loops=1)"
"                          ->  Seq Scan on tbl_net_production
(cost=0.00..58.61 rows=2861 width=29) (actual time=0.051..5.965 rows=2865
loops=1)"
"Total runtime: 667.997 ms"

Kind Regards,
Keith

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> Here is what I have figured out.  I can use two querys to get the data and
> then EXCEPT them together to eliminate the holidays.  That part works fine.
> So using 2005-09-07 as an order date and 2005-11-28 as a ship date this is
> what I end up with.

> SELECT max( dates ) AS completion_date
>   FROM ( SELECT '2005-09-07'::date + s.a AS dates
>            FROM generate_series(0,365) AS s(a)
>          EXCEPT
>          SELECT holiday
>            FROM interface.tbl_holidays
>        ) AS valid_dates
>  WHERE dates <= ('2005-11-28'::date - interval '1 day')::date
>    AND NOT extract(dow FROM dates) IN (0, 6);

SQL is really entirely the wrong tool for this job, or at least you are
applying it in the least efficient possible way.  It seems to me that
you want to step backwards from the ship date, discarding weekend dates
(easily checked) and then discarding holidays.  Since there are few
holidays this should generally require only one probe into the holidays
table, and not too many dates considered in total.  As you've got it
coded above, the entire process is gone through for 365 different dates,
after which you proceed to compare the dates and throw away all but one.

I would personally tend to write this as a plpgsql function containing a
for-loop.  Something like

create function prior_working_day(date) returns date as $$
declare d date;
begin
  for i in 1..10 loop
    d := $1 - i;
    if extract(dow from d) not in (0,6) then
      if not exists(select 1 from interface.tbl_holidays where hdate = d) then
        return d;
      end if;
    end if;
  end loop;
  -- if we get here there's something badly wrong
  raise exception 'could not find a non-holiday date';
end$$ language plpgsql strict stable;

Just as a finger exercise, we could turn this into a single SQL
operation, but the function is likely to be noticeably faster:

select d from
(select ('2005-11-28'::date - s.a) as d
 from generate_series(1,10) as s(a)) ss
where extract(dow from d) not in (0, 6) and
  not exists(select 1 from interface.tbl_holidays where hdate = d)
limit 1;

            regards, tom lane