Re: [SQL] Counting days ...
> > Check my work, but I think the sum part of the query simply becomes: > > sum ( > ( > date_smaller(res_end_day, '2008-02-29'::date) - > date_larger(res_start_day, '2008-01-31'::date) > ) * group_size > ) > > Basically remove the "+1" so we don't include both start and end dates > but move the start base back one day so anyone starting prior to Feb 1 > gets the extra day added. > > Cheers, > Steve Thanks Steve, I'm not sure if I quite grasped this. It gives a bit funny results: SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) - date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS days_in_period, c.country_name AS country FROM product_res pr LEFT JOIN countries c ON pr.country_id = c.country_id WHERE group_id = 1 AND res_end_day >= '2007-01-01' AND res_end_day <= '2008-12-31' group by pr.country_id, c.country_name; days_in_period | country + -441137 | -30 | Germany -28 | Estonia 60 | Bulgaria -25003 | Russian Federation -207670 | Suomi 256 | Ukraine -6566 | Latvia -280 | United States -1889 | Switzerland 114 | Lithuania 36 | Norway -66 | Sweden 170 | Kazakhstan 72 | Belarus (15 rows) Anyway, I have to rethink and elaborate the query. I know that it will usually be on a monthly or yearly basis, but a reservation can actually be any of the following in relation to the given (arbitrary) period: 1. start_day before period_start, end_day = period_start 2. start_day before period_start, end_day in period 3. start_day before period_start, end_day = period_end 4. start_day = period_start, end_day in period 5. start_day in period, end_day in period 6. start_day = period_start, end_day = period_end 7. start_day in period, end_day = period_end 8. start_day in period, end_day after period_end 9. start_day = period_start, end_day = period_end 10 start_day before period_start, end_day after period_end Hmm ... Best regards, -- Aarni Ruuhimäki --- Burglars usually come in through your windows. --- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Counting days ...
Aarni Ruuhimäki wrote: Check my work, but I think the sum part of the query simply becomes: sum ( ( date_smaller(res_end_day, '2008-02-29'::date) - date_larger(res_start_day, '2008-01-31'::date) ) * group_size ) Basically remove the "+1" so we don't include both start and end dates but move the start base back one day so anyone starting prior to Feb 1 gets the extra day added. Cheers, Steve Thanks Steve, I'm not sure if I quite grasped this. It gives a bit funny results: SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) - date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS days_in_period, c.country_name AS country FROM product_res pr LEFT JOIN countries c ON pr.country_id = c.country_id WHERE group_id = 1 AND res_end_day >= '2007-01-01' AND res_end_day <= '2008-12-31' group by pr.country_id, c.country_name; days_in_period | country + -441137 | -30 | Germany -28 | Estonia 60 | Bulgaria -25003 | Russian Federation -207670 | Suomi 256 | Ukraine -6566 | Latvia -280 | United States -1889 | Switzerland 114 | Lithuania 36 | Norway -66 | Sweden 170 | Kazakhstan 72 | Belarus (15 rows) Anyway, I have to rethink and elaborate the query. I know that it will usually be on a monthly or yearly basis, but a reservation can actually be any of the following in relation to the given (arbitrary) period: 1. start_day before period_start, end_day = period_start 2. start_day before period_start, end_day in period 3. start_day before period_start, end_day = period_end 4. start_day = period_start, end_day in period 5. start_day in period, end_day in period 6. start_day = period_start, end_day = period_end 7. start_day in period, end_day = period_end 8. start_day in period, end_day after period_end 9. start_day = period_start, end_day = period_end 10 start_day before period_start, end_day after period_end Hmm ... Best regards, #6 and #9 are the same. You missed these: a start_day before period_start, end_day before period_start b start_day = period_start, end_day = period_start c start_day = period_start, end_day after period_end d start_day = period_end, end_day = period_end e start_day = period_end, end_day after period_end f start_day after period_end, end_day after period_end Granted, a & f should not match where clause; but then groups 10,c,e don't meet your where clause either. Your where clause should probably be: WHERE group_id = 1 AND (res_start_day >= '2007-01-01' AND res_end_day <= '2008-12-31') Are you sure that your database does not have any rows where start_day is after end_day? These rows could certainly skew results. I would suggest that you identify a few rows that meet each of these conditions. Change the where clause to select rows in one group at a time. You might consider using a unique row identifier in where clause during these tests to make sure you are processing the rows you think you are. When all test cases work properly; then run your generalized query again. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Counting days ...
Frank Bax wrote: Aarni Ruuhimäki wrote: Anyway, I have to rethink and elaborate the query. I know that it will usually be on a monthly or yearly basis, but a reservation can actually be any of the following in relation to the given (arbitrary) period: 1. start_day before period_start, end_day = period_start 2. start_day before period_start, end_day in period 3. start_day before period_start, end_day = period_end 4. start_day = period_start, end_day in period 5. start_day in period, end_day in period 6. start_day = period_start, end_day = period_end 7. start_day in period, end_day = period_end 8. start_day in period, end_day after period_end 9. start_day = period_start, end_day = period_end 10 start_day before period_start, end_day after period_end #6 and #9 are the same. You missed these: astart_day before period_start, end_day before period_start bstart_day = period_start, end_day = period_start cstart_day = period_start, end_day after period_end dstart_day = period_end, end_day = period_end estart_day = period_end, end_day after period_end fstart_day after period_end, end_day after period_end Granted, a & f should not match where clause; but then groups 10,c,e don't meet your where clause either. Your where clause should probably be: WHERE group_id = 1 AND (res_start_day >= '2007-01-01' AND res_end_day <= '2008-12-31') Are you sure that your database does not have any rows where start_day is after end_day? These rows could certainly skew results. I would suggest that you identify a few rows that meet each of these conditions. Change the where clause to select rows in one group at a time. You might consider using a unique row identifier in where clause during these tests to make sure you are processing the rows you think you are. When all test cases work properly; then run your generalized query again. Change 10,c,e to 8,10,c,e - Group 8 also does not meet your initial WHERE clause. My suggestion for WHERE clause also does not work. This might work better (although it still could be wrong): WHERE group_id = 1 AND (res_start_day BETWEEN '2007-01-01' AND '2008-12-31' OR res_end_day BETWEEN '2007-01-01' AND '2008-12-31') In case I still have it wrong, try each test group separately and you'll soon find out if the WHERE clause is correct or not. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Counting days ...
Thanks Frank, Top and between posting ... On Friday 14 March 2008 15:58, Frank Bax wrote: > Frank Bax wrote: > > Aarni Ruuhimäki wrote: > >> Anyway, I have to rethink and elaborate the query. I know that it will > >> usually be on a monthly or yearly basis, but a reservation can > >> actually be any of the following in relation to the given (arbitrary) > >> period: > >> > >> 1. start_day before period_start, end_day = period_start > >> 2. start_day before period_start, end_day in period > >> 3. start_day before period_start, end_day = period_end > >> 4. start_day = period_start, end_day in period > >> 5. start_day in period, end_day in period > >> 6. start_day = period_start, end_day = period_end > >> 7. start_day in period, end_day = period_end > >> 8. start_day in period, end_day after period_end > >> 9. start_day = period_start, end_day = period_end > >> 10 start_day before period_start, end_day after period_end > > > > #6 and #9 are the same. You missed these: Whoops, 9 should be c. > > > > astart_day before period_start, end_day before period_start This I don't have to care about as it is not in the period we are looking at. > > bstart_day = period_start, end_day = period_start Is zero days/nights, ignored. Not even possible to insert in the application. end_day must be greater than start_day. > > cstart_day = period_start, end_day after period_end Yes. Number 9 now. > > dstart_day = period_end, end_day = period_end Is zero days, ignored. like b. > > estart_day = period_end, end_day after period_end Is outside the period. Day changes / the night starts at midnight, so this would go in the 'next' period. Like number 1 comes in this period as one day. > > fstart_day after period_end, end_day after period_end This is also outside the period we are looking at. > > > > Granted, a & f should not match where clause; but then groups 10,c,e > > don't meet your where clause either. Your where clause should probably > > be: > > > > WHERE group_id = 1 AND (res_start_day >= '2007-01-01' AND res_end_day <= > > '2008-12-31') > > > > Are you sure that your database does not have any rows where start_day > > is after end_day? These rows could certainly skew results. Yes, the application does not allow this. SELECT res_id FROM product_res WHERE res_start_day > res_end_day; res_id (0 rows) > > > > I would suggest that you identify a few rows that meet each of these > > conditions. Change the where clause to select rows in one group at a > > time. You might consider using a unique row identifier in where clause > > during these tests to make sure you are processing the rows you think > > you are. When all test cases work properly; then run your generalized > > query again. > > Change 10,c,e to 8,10,c,e - Group 8 also does not meet your initial > WHERE clause. My suggestion for WHERE clause also does not work. This > might work better (although it still could be wrong): > > WHERE group_id = 1 AND (res_start_day BETWEEN '2007-01-01' AND > '2008-12-31' OR res_end_day BETWEEN '2007-01-01' AND '2008-12-31') > > In case I still have it wrong, try each test group separately and you'll > soon find out if the WHERE clause is correct or not. I think I need more ORs in the WHERE clause to find all res_ids I want to count according to the 10 rules. Ie. if one or more days of a reservation is 'inside' the given period. Testing ... Best regards, -- Aarni Ruuhimäki --- Burglars usually come in through your windows. --- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Counting days ...
Aarni Ruuhimäki wrote: Thanks Frank, astart_day before period_start, end_day before period_start This I don't have to care about as it is not in the period we are looking at. bstart_day = period_start, end_day = period_start Is zero days/nights, ignored. Not even possible to insert in the application. end_day must be greater than start_day. You should still consider rows that are "out of range" or "zero nights" in your test cases to make sure your report processes them correctly. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Counting days ...
Aarni Ruuhimäki wrote: Thanks Steve, I'm not sure if I quite grasped this. It gives a bit funny results: SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) - date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS days_in_period, c.country_name AS country FROM product_res pr LEFT JOIN countries c ON pr.country_id = c.country_id WHERE group_id = 1 AND res_end_day >= '2007-01-01' AND res_end_day <= '2008-12-31' group by pr.country_id, c.country_name; days_in_period | country + -441137 | -30 | Germany -28 | Estonia ... I see one error in my logic. It doesn't account for the situation where res_end_day is prior to the start of the period you are viewing. You can fix this by limiting records with the appropriate where-clause or by wrapping the date_smaller inside a date_larger (and vice-versa) to ensure that all dates stay inside the desired period. Or you can fix it by using an appropriate where-clause. Yours appears broken - I think you want res_end_day >2006-12-31 (or >=2007-01-01 - I prefer mine as you can use the same date in multiple places in the query) which is what you have. But I think you want the end of period to be limited to res_start_day <=2007-12-31. IOW, if your *end* date is *before* the period of interest or your *start* date is *after* the period of interest, skip the record. My guess is that you have records with res_start_day > 2007-12-31. After applying the larger and smaller functions, this will end up with a res_end_day of 2007-12-31 giving an end_day < start_day. (I'm presuming you have appropriate constraints to prevent end_day from being earlier than start_day. If not, check for that and add the constraints.) Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] DB Design
I have a inventory system design in which I would like some help with to
see if it's efficient. The products are broken into:
Product tables
Property tables
Configurable Products - this would include colors (i.e. - black, blue
and green) tied to products
I'm thinking of breaking inventory into 2 tables.
Product Inventory
Inventory Adjustment
Should I include the fk of the Configurable Product in the above tables
or break it out further into more tables?
Product Inventory
Inventory Adjustment
--plus--
Product Property Inventory
Inventory Property Adjustment
Thanks for any input,
J
These are my Product tables:
\d cart_product
Table "public.cart_product"
Column | Type |
Modifiers
---+--+---
id| integer | not null default
nextval('cart_product_id_seq'::regclass)
name | character varying(128) | not null
kind | character varying(40)|
sku | character varying(15)|
short_description | character varying(255) | not null
description | text |
category_id | integer | not null
date_created | timestamp with time zone | not null
active| boolean | not null
in_stock | boolean | not null
featured | boolean | not null
ordering | integer |
Indexes:
"cart_product_pkey" PRIMARY KEY, btree (id)
"cart_product_category_id" btree (category_id)
Foreign-key constraints:
"cart_product_category_id_fkey" FOREIGN KEY (category_id) REFERENCES
cart_category(id) DEFERRABLE INITIALLY DEFERRED
\d cart_propertyvariation
Table "public.cart_propertyvariation"
Column | Type |
Modifiers
---+---+-
id| integer | not null default
nextval('cart_propertyvariation_id_seq'::regclass)
properties_id | integer | not null
name | character varying(42) | not null
value | character varying(20) | not null
order | integer |
Indexes:
"cart_propertyvariation_pkey" PRIMARY KEY, btree (id)
"cart_propertyvariation_properties_id" btree (properties_id)
Check constraints:
"cart_propertyvariation_order_check" CHECK ("order" >= 0)
Foreign-key constraints:
"properties_id_refs_id_73bc0a59" FOREIGN KEY (properties_id)
REFERENCES cart_property(id) DEFERRABLE INITIALLY DEFERRED
\d cart_configurableproduct
Table "public.cart_configurableproduct"
Column | Type |
Modifiers
-+--+---
id | integer | not null default
nextval('cart_configurableproduct_id_seq'::regclass)
product_id | integer | not null
variation_id| integer | not null
price_change| numeric(8,2) |
weight_change | integer |
quantity_change | integer |
active | boolean | not null
Indexes:
"cart_configurableproduct_pkey" PRIMARY KEY, btree (id)
"cart_configurableproduct_product_id" btree (product_id)
"cart_configurableproduct_variation_id" btree (variation_id)
Foreign-key constraints:
"cart_configurableproduct_product_id_fkey" FOREIGN KEY (product_id)
REFERENCES cart_product(id) DEFERRABLE INITIALLY DEFERRED
"cart_configurableproduct_variation_id_fkey" FOREIGN KEY
(variation_id) REFERENCES cart_propertyvariation(id) DEFERRABLE
INITIALLY DEFERRED
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Counting days ...
On Friday 14 March 2008 18:09, Frank Bax wrote: > > > > Is zero days/nights, ignored. Not even possible to insert in the > > application. end_day must be greater than start_day. > > You should still consider rows that are "out of range" or "zero nights" > in your test cases to make sure your report processes them correctly. For the WHERE clause for Jan 08 (will be $date1 and $date2), getting there. 1. start_day before period_start, end_day = period_start #1 2. start_day before period_start, end_day in period #1 3. start_day before period_start, end_day = period_end #1 4. start_day = period_start, end_day in period #2 5. start_day in period, end_day in period #3 6. start_day = period_start, end_day = period_end #2 7. start_day in period, end_day = period_end #3 8. start_day in period, end_day after period_end #3 9. start_day = period_start, end_day after period_end #2 10. start_day before period_start, end_day after period_end #1 SELECT res_id, to_char(res_start_day, 'DD.MM.'), to_char(res_end_day, 'DD.MM.') FROM product_res WHERE group_id = 1 AND res_start_day < '2008-01-01' AND res_end_day >= '2008-01-01' # covers 1,2,3,10 OR group_id = 1 AND res_start_day = '2008-01-01' AND res_end_day >= '2008-01-01' # covers 4,6,9 OR group_id = 1 AND res_start_day >= '2008-01-01' AND res_start_day < '2008-01-31' AND res_end_day >= '2008-01-01'; # covers 5,7,8 (499 rows) not yet summing up or grouping by. But is this getting too heavy ? I have three more (optional) parameters to pass into the query, which narrow down the result. All values are stored also in the product_res table. 1. Area/region ID from dropdown, populated by areas that have products 2. Company ID from dropdown, dynamically populated according to the optional area selection with companies that have products in the selected area 3. Product ID from dropdown, dynamically populated by the optional company selection with the selected company's products in the selected area So the WHERE clause would go like: group_id = 1 AND res_start_day < '$date1' AND res_end_day >= '$date1' [AND region_id = $region_id] [AND company_id = $company_id] [AND product_id = $product_id] OR group_id = 1 AND res_start_day = '$date1' AND res_end_day >= '$date1' [AND region_id = $region_id] [AND company_id = $company_id] [AND product_id = $product_id] OR group_id = 1 AND res_start_day >= '$date1' AND res_start_day < '$date2' AND res_end_day >= '$date1' [AND region_id = $region_id] [AND company_id = $company_id] [AND product_id = $product_id] Cheerio, -- Aarni Ruuhimäki --- Burglars usually come in through your windows. --- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
