Re: Select from multiple tables, date between 2 columns.

On Dec 6, 1:01 am, Jonathan Leffler <jleff...@earthlink.net> wrote: > jared.ha...@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; I ended up using the following: select distinct wx_week ,it_store ,inv_id2 || " " || inv_id1 ,count(inv_id3) from invtranarc ,week ,inv where (it_date = wx_begdate or it_date = wx_enddate or it_date between wx_begdate and wx_enddate) and it_seq = inv_id3 and it_date between today-7 and today-1 and it_type = "S" and it_qty > 0 group by 1,2,3 order by 1,2,3 We needed to get a count of all the different styles that were sold for a given time, which is why I used a count instead of a sum. Thanks for the help. > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleff...@earthlink.net, jleff...@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

On Dec 6, 3:47 pm, jared.ha...@gmail.com wrote: > On Dec 6, 1:01 am, Jonathan Leffler <jleff...@earthlink.net> wrote: > You could have also done: > select distinct wx_week, it_store, inv_id2 || " " || inv_id, count(inv_id3) from invtranarc, week, inv where (wx_begdate <= it_date and wx_enddate >= it_date) and it_seq = inv_id3 and it_date between today-7 and today-1 and it_type = "S" and it_qty > 0 group by 1,2,3 order by 1,2,3; Might be slightly faster since it can use indexes more directly. Art S. Kagel > > > > jared.ha...@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;> > I ended up using the following: > > select distinct wx_week > ,it_store > ,inv_id2 || " " || inv_id1 > ,count(inv_id3) > from invtranarc > ,week > ,inv > where (it_date = wx_begdate or it_date = wx_enddate > or it_date between wx_begdate and wx_enddate) > and it_seq = inv_id3 > and it_date between today-7 and today-1 > and it_type = "S" > and it_qty > 0 > group by 1,2,3 > order by 1,2,3> > We needed to get a count of all the different styles that were sold > for a given time, which is why I used a count instead of a sum. > > Thanks for the help. > > > -- > > Jonathan Leffler #include <disclaimer.h> > > Email: jleff...@earthlink.net, jleff...@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