Select from multiple tables, date between 2 columns.

Hello, Here's the situation: 3 tables (retail_week, items, sales) Columns: -retail_week: week_num, week_begdate, week_enddate -items: item_num, item_vendor, item_category -sales: sale_date, sale_store, sale_item, sale_qty I'm having trouble writing the query to return the following: I need the week number (from retail_week table), the store number (sale_store), the combination of category and vendor with space between the two, and the count of the items sold for the category/ vendor combination. example format: week_num, sale_store, item_category || " " || item_vendor, count(distinct sale_item). Since we're use a retail calendar I need to be able to get week numbers based on retail weeks. The retail_week looks like this: For date range 11/30/07-12/5/07: week_year week_num week_begdate week_enddate week_month 2007 43 11/25/2007 12/01/2007 10 2007 44 12/02/2007 12/08/2007 11 How can I write the query to return the week number? I'm guessing I need to do something like "where sale_date = week_begdate or sale_date = week_enddate or sale_date between week_begdate and week_enddate" but I can't seem to get it to work. Any help is greatly appreciated. Thanks, Jared

jared.hanks@gmail.com wrote: > 3 tables (retail_week, items, sales) > Columns: > -retail_week: week_num, week_begdate, week_enddate > -items: item_num, item_vendor, item_category > -sales: sale_date, sale_store, sale_item, sale_qty > > I'm having trouble writing the query to return the following: > > I need the week number (from retail_week table), the store number > (sale_store), the combination of category and vendor with space > between the two, and the count of the items sold for the category/ > vendor combination. > > example format: > week_num, sale_store, item_category || " " || item_vendor, > count(distinct sale_item). > > Since we're use a retail calendar I need to be able to get week > numbers based on retail weeks. The retail_week looks like this: > > For date range 11/30/07-12/5/07: > week_year week_num week_begdate week_enddate week_month > 2007 43 11/25/2007 12/01/2007 10 > 2007 44 12/02/2007 12/08/2007 11 > > How can I write the query to return the week number? I'm guessing I > need to do something like "where sale_date = week_begdate or sale_date > = week_enddate or sale_date between week_begdate and week_enddate" but > I can't seem to get it to work. > > Any help is greatly appreciated. SELECT w.week_num, s.store, i.category || " " || i.item_vendor, COUNT(DISTINCT s.sale_item) FROM retail_week w, sales s, items i WHERE i.item_num = s.sale_item AND s.sale_date BETWEEN w.week_beg_date AND w.week_end_date GROUP BY w.week_num, s.store, i.category || " " || i.item_vendor; Untested - but eminently plausible. If there's a problem, it will likely be in the GROUP BY clause. The COUNT(DISTINCT s.sale_item) has me puzzled; I'd have expected something more like SUM(s.sale_item * s.sale_qty), but it's your data so I assume you know what you're doing. (Did you include parentheses around your OR conditions? If not, that was probably the trouble -- or you forgot to ensure the join between items and sales and had a cartesian product; both possibilities could be guessed from your description of the WHERE condition you tried. Note that x BETWEEN y AND z is equivalent to (x >= y AND x <= z) so your OR'd conditions were redundant - and hence omitted from my answer.) I also don't see any obvious value in the concatenation operation (and some demerit); I'd prefer to see the following, which I'm more confident is correct. SELECT w.week_num, s.store, i.category, i.item_vendor, COUNT(DISTINCT s.sale_item) FROM retail_week w, sales s, items i WHERE i.item_num = s.sale_item AND s.sale_date BETWEEN w.week_beg_date AND w.week_end_date GROUP BY w.week_num, s.store, i.category, i.item_vendor; -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-1958 whirlpool0 2007-12-06 06:00:04 3CD39495E510ED46C9B131FF36094B7B8B8BCE7A8693EA7B5BC4F8215CF621B737E014 4F60D9BBED3E4509A730FE1942C03772E0B4892F3DFC1FB106D0FBAE4