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

On Dec 6, 1:01 am, Jonathan Leffler <jleff...@xxxxxxxxxxxxx> wrote:
jared.ha...@xxxxxxxxx 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...@xxxxxxxxxxxxx, jleff...@xxxxxxxxxx
Guardian of DBD::Informix v2007.0914 --http://dbi.perl.org/

publictimestamp.org/ptb/PTB-1958 whirlpool0 2007-12-06 06:00:04
3CD39495E510ED46C9B131FF36094B7B8B8BCE7A8693EA7B5BC4F8215CF621B737E014
4F60D9BBED3E4509A730FE1942C03772E0B4892F3DFC1FB106D0FBAE4

.

Relevant Pages

  • Re: JOIN not returning desired value, please help!
    ... > it into a situation where vendor is included. ... > totals table to consider vendor. ... but my goal is to be able to show the vendor's sales compared ... > I'm writing a query that summarizes some data and compares it to other ...
    (microsoft.public.sqlserver.programming)
  • Find all and Sum
    ... I have created a worksheet of new/lost and sales moved from one vendor to ... Date Customer Name Type Vendor Premium Commission SalesPerson ... Date Customer Name Type Reason Vendor Premium Commission SalesPerson ...
    (microsoft.public.excel)
  • Re: Not sure how to join these 2 tables
    ... "I need my SQL to get the total number of items sold in each ... Each record holds a sales event, so Tom might have 5 or 6 records whose ... > TeamMember1Id varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: The Difference is Obvious!
    ... Some sales enough sales. ... Also consider that if you port a non-visual VCL control to a .NET control, ... Any component vendor has to consider whether the effort is worthwhile or not. ...
    (borland.public.delphi.non-technical)
  • Re: JOIN not returning desired value, please help!
    ... If I am pulling 6 columns from the totals table, ... > One sales order can refer to multiple vendors, ... > also appear twice. ... > sd.vendor_pk as 'Vendor Number', ...
    (microsoft.public.sqlserver.programming)