Please help with SQL query

10 Feb 2006 6:38 AM

coocoonut

I have question about SQL query to generate the following result.
Please advise how this can be done with SQL query. Thanks in advance!

We need to generate a report to show % of revenue for each category
under a perticular product line.   Here are the simplified structure
and data sample:

Product Line 1, 2, 3 (P1, P2, P3)
There are 3 categories: Market Segment, Chanel, Type of Customer.

For each product line, there are 3 different market segments:
    Market Segment 1, 2, 3 (M1, M2, M3)

For each product line, there are 3 different market channels:
    Channel 1, 2, 3 (C1, C2, C3)

For each product line, there are 3 different customer types:
    Type of Customer 1, 2, 3 (T1, T2, T3)

The table has the following fields with a couple of sample records
below. For each category, total percentage adds up to 100%.

Invoice, Amount, P-Line, M1-Percent, M2-Percent, M3-Percent,
C1-Percent, C2-Percent, C3-Percent, T1-Percent, T2-Percent, T3-Percent
'12341', $2,500, 'Furniture', 30%, 50%, 20%, 100%, 0%, 0%, 40%, 0%, 60%
'12342', $4,800, 'Tools', 100%, 0%, 0%, 50%, 25%, 25%, 0%, 0% 100%

We need to general a report similar to this:
P1, Market Segment: M1 - %, M2 - %, M3 % (note: total 100%)
      Channel: C1 - %, C2 - %, C3 - %, (total 100%)
      Type of customer: T1 - %, T2 - %, T3 - % (total 100%)
P2, ....

P3, ....

10 Feb 2006 6:52 AM

Uri Dimant

Hi
Please post DDL+ sample data + expected result. If you want us to test it
and not guessing so provide more info

<coocoo***@hotmail.com> wrote in message
Show quoteHide quote

news:1139553489.879494.84120@g43g2000cwa.googlegroups.com...
>I have question about SQL query to generate the following result.
> Please advise how this can be done with SQL query. Thanks in advance!
>
> We need to generate a report to show % of revenue for each category
> under a perticular product line.   Here are the simplified structure
> and data sample:
>
> Product Line 1, 2, 3 (P1, P2, P3)
> There are 3 categories: Market Segment, Chanel, Type of Customer.
>
> For each product line, there are 3 different market segments:
> Market Segment 1, 2, 3 (M1, M2, M3)
>
> For each product line, there are 3 different market channels:
> Channel 1, 2, 3 (C1, C2, C3)
>
> For each product line, there are 3 different customer types:
> Type of Customer 1, 2, 3 (T1, T2, T3)
>
> The table has the following fields with a couple of sample records
> below. For each category, total percentage adds up to 100%.
>
> Invoice, Amount, P-Line, M1-Percent, M2-Percent, M3-Percent,
> C1-Percent, C2-Percent, C3-Percent, T1-Percent, T2-Percent, T3-Percent
> '12341', $2,500, 'Furniture', 30%, 50%, 20%, 100%, 0%, 0%, 40%, 0%, 60%
> '12342', $4,800, 'Tools', 100%, 0%, 0%, 50%, 25%, 25%, 0%, 0% 100%
>
> We need to general a report similar to this:
> P1, Market Segment: M1 - %, M2 - %, M3 % (note: total 100%)
>      Channel: C1 - %, C2 - %, C3 - %, (total 100%)
>      Type of customer: T1 - %, T2 - %, T3 - % (total 100%)
> P2, ....
>
> P3, ....
>

10 Feb 2006 7:34 AM

coocoonut

Below are the table structure and sample data
Table 'invoice'
Invoice_No, Amount, Product_Line, M1_Percent, M2_Percent, M3_Percent,
C1_Percent, C2_Percent, C3_Percent, T1_Percent, T2_Percent, T3_Percent

Sample data:
'12341', $2,500, 'Furniture', 30, 50, 20, 100, 0, 0, 40, 0, 60
'12342', $4,800, 'Tools', 100, 0, 0, 50, 25, 25, 0, 0, 100

Sample result:
Furniture --- Market Segment: M1: 31%, M2: 20%, M3: 49%
Channel: C1: 12%, C2: 46%, C3: 42%
Type of customer: T1: 66%, T2: 25%, T3: 9%
Tools ----

Thanks,
-jesk