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 (AT) gmail (DOT) com wrote:
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 (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-1958 whirlpool0 2007-12-06 06:00:04
3CD39495E510ED46C9B131FF36094B7B8B8BCE7A8693EA7B5B C4F8215CF621B737E014
4F60D9BBED3E4509A730FE1942C03772E0B4892F3DFC1FB106 D0FBAE4

On Dec 6, 1:01 am, Jonathan Leffler <jleff... (AT) earthlink (DOT) net> wrote:
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.

On Dec 6, 3:47 pm, jared.ha... (AT) gmail (DOT) com wrote:
You could have also done:

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