Advanced SQL - Extracting values by months

#11

On Jun 1, 8:39 pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Quote:

"whitsey" <lysterfiel... (AT) gmail (DOT) com> wrote in message

news:1180340909.593482.109010 (AT) z28g2000prd (DOT) googlegroups.com...

Help!

I am trying to write an SQL statement (without much luck) to extract
the total number of searches and orders from a particular customer,
grouped into days/months/weeks (depending on the report type.

The tables are as follows:

Searches
--------------
search_id (PKEY)
customer_id (FKEY)
product_id
cost
date
....

Orders
-----------
order_id (PKEY)
search_id (FKEY)
order_value
date
...

Groups
-----------
group_id (PKEY)
customer_id
...

Customers
---------------
customer_id (PKEY)
....

What I want to achieve from this is

For all customers with Group_id = '17'

Mth 1 1244(Searches) 846(Orders)
Mth 2 2345(Searches) 1834(Orders)
Mth 3 2342(Searches) 1954(Orders)
Mth 4 2425(Searches) 2134(Orders)
Mth 5 5235(Searches) 4253(Orders)

I am clearly nowhere near the level I need to be to achieve this.

Can it be achieved in a single statement?

Thanks

Here's the daily report, made by joining two subqueries together:

select
scount.date,
scount.searches,
ocount.orders

from
(select
s.date,
count (s.search_id) as searches
from
searches s inner join
groups g on s.customer_id = g.customer_id
where
g.group_id = 17
group by
s.date) as scount full outer join
(select
o.date,
count (o.order_id) as orders
from
(orders o inner join
searches s on o.search_id = s.search_id) inner join
groups g on s.customer_id = g.customer_id
where
g.group_id = 17
group by
s.date) as ocount on scount.date = ocount.date;

What's interesting about the above is that the outer join condition is on
the date, and not the customer_id.

Doing it by month or week is the same idea, except you use a built in
function to extract the month number or the week number from the date in a
few places. An alternative to using such a function in the query is to
build an "almanac" table with three columns: day, week, and month.
We did something like that when building a reporting database back in 1994.
We had lots more columns in the almanac table... things like what fiscal
quarter the date belonged to and whether the enterprise was or was not open
that day.

The almanac table made lots of queries a lot simpler. It's sort of like
making a "time dimension" in a multidimensional data mart.- Hide quoted text -

- Show quoted text -

Hi all,

Can Some one help me in my query,
what i've got is similar to what we are discussing.

My Tables are

Sales_Table
-----------------

Cust_Name
Cust_add
Rep_ID (which is a forgain Key)
Sale_Type (different type of product)

Cancels Table
--------------------

Cust_Name
Cust_add
Rep_ID (which is a Foreign Key)
Sale_Type (different type of products i.e Home Phone, HSD, Cable)

Rep_Table

Rep_ID (Primary Key)
Rep_Name
Team_Manager (Team A, Team B, Team C)

What I want to get out this is

1. No of sales and cancels for Team A/B/C for each product
2. No of sales and cancels for Rep for each product

Please Help

Regards,

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Advanced SQL - Extracting values by months

comp.databases comp.databases

Re: Advanced SQL - Extracting values by months - 06-04-2007 , 11:05 PM