Advanced SQL - Extracting values by months

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

whitsey - 28 May 2007 09:30 GMT

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

Reply to this Message

Serge Rielau - 28 May 2007 12:34 GMT

> Help!
>
[quoted text clipped - 45 lines]
>
> Can it be achieved in a single statement?

Yes, a join combined with GROUP BY. Use MONTH(date) to coarsify to the
month.
Why don't you post your best shot and we go from there?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

whitsey - 30 May 2007 01:31 GMT

> > Help!
>
[quoted text clipped - 58 lines]
>
> - Show quoted text -

This is where I've started however this is returning no rows... Also,
I am unable to extract values by months...

SELECT
    COUNT(SEARCHES.SEARCH_ID),
    COUNT(SEARCHES.SEARCH_ID)
FROM
    SEARCHES
    INNER JOIN ORDERS
    ON SEARCHES.SEARCH_ID = ORDERS.SEARCH_ID
    INNER JOIN GROUPS
    ON GROUPS.CUSTOMER_ID = SEARCHES.CUSTOMER_ID
WHERE
    (GROUPS.GROUP_ID = 17)

Reply to this Message

shannonw@reynolds.com.au - 30 May 2007 01:59 GMT

> > > Help!
>
[quoted text clipped - 75 lines]
>
> - Show quoted text -

I've uploaded a screen shot of the tables and their relationship here:
http://picasaweb.google.com/shannon.whitty/Temporary

Also, the 2nd COUNT() clause should read COUNT(ORDERS.SEARCH_ID) my
mistake!

Reply to this Message

Brian Tkatch - 30 May 2007 18:06 GMT

>Help!
>
[quoted text clipped - 47 lines]
>
>Thanks

I took a quick shot at it:

SELECT
    MONTH(Searches.Search_Date),
    COUNT(*)    Searches,
    (
    SELECT
        COUNT(*)
    FROM
        Orders
    WHERE
        Orders.Customer_Id    = Searches.Customer_Id
    AND    MONTH(Orders.Order_Date) = MONTH(Searches.Search_Date)
    )        Orders
FROM
    Searches
WHERE
    EXISTS
    (
    SELECT
        *
    FROM
        Groups
    WHERE
        Groups.Customer_Id = Searches.Customer_Id
    AND    Groups.Group_Id = '17'
    )
GROUP BY
    MONTH(Search_Date)

I used a sub-select for no particular reason. a join ought to work as
well.

B.

Reply to this Message

whitsey - 31 May 2007 08:19 GMT

> >Help!
>
[quoted text clipped - 84 lines]
>
> - Show quoted text -

Got it and it works perfectly, Thanks! ( I used a join myself )
However, the problem I have is that it is calculating the month of
january as a total of every january ever entered in the database!!! I
have tried to narrow it fown by outputting month & year but cannot get
it to work.

I changed MONTH(Searches.Search_Date) to
MONTH(Searches.Search_Date)'/'YEAR(Searches.Search_Date) but it didn't
like that.
I tried CAST(Searches.Search_Date AS DATE FORMAT 'mm/yy') however it
failed at "FORMAT"

Also, the months are in the order Jan - Dec. I am using this for a
graph and want the last month to be this month (May-07) and them going
back historically for a predefined period.

I can't seem to find much help on DATE functions on the web - maybe
I'm not searching right!

Thanks

Reply to this Message

Serge Rielau - 31 May 2007 12:16 GMT

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.d
oc/doc/r0000814.htm
INTEGER(Searched.Search_date) / 100

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Brian Tkatch - 31 May 2007 16:56 GMT

<SNIP>

>Got it and it works perfectly, Thanks! ( I used a join myself )

You're most welcome. Glad i could be of service.

>However, the problem I have is that it is calculating the month of
>january as a total of every january ever entered in the database!!! I
[quoted text clipped - 6 lines]
>I tried CAST(Searches.Search_Date AS DATE FORMAT 'mm/yy') however it
>failed at "FORMAT"

The problem is that month is not divided into years. The solution is
to GROUP BY years as well.

GROUP BY
YEAR(Searches.Search_Date),
MONTH(Searches.Search_Date)

That will first section off all data by years, and then--within each
yearly group--it will further section them by month.

>Also, the months are in the order Jan - Dec. I am using this for a
>graph and want the last month to be this month (May-07) and them going
>back historically for a predefined period.

Can't you use:

ORDER BY
YEAR(Searches.Search_Date) DESC,
MONTH(Searches.Search_Date) DESC

>I can't seem to find much help on DATE functions on the web - maybe
>I'm not searching right!

Optimally, you should be reviewing all FUNCTIONs availble in the DB2
documentation. It's a good list to be familiar with.

B.

Reply to this Message