SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query the same column twice
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

newtoitall
Starting Member

5 Posts

Posted - 02/15/2013 :  09:23:03  Show Profile  Reply with Quote
Hi

I have a table called ANALYSIS_CODES that contains a column called ANALYSIS$$ (in short form).

This column is joined with another table called CLIENTS (in short form)

Now, each client my have multiple CODES in the ANALYSIS COLUMN, e.g.

John Shipping has ANALYSIS$$ codes EXCR30, CLT_UF, IMCR30

Pete Shipping has ANALYSIS$$ codes EXCR30, CLT_UC

Now, how do I filter on the same column twice, then outputting the result horizontally?

e.g. filtering Analysis$$ twice , once for EXCR30 + CLT_UF or CLT_UF

Then outputting these results in different columns

Name------------Analysis1---Analysis2
John Shipping---EXCR30--------CLT_UF
Pete Shipping---EXCR30--------CLT_UC

Only one customer should output per row.

I cannot found a way on Google to do this! I think i need to create an Alias, and another Join , by cannot figure it out.

Here is the SQL i have so far.

Would appreciate some guidance.


select 
    NAME_ADDRESS_1,
    CASE BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE
        WHEN '1' then 'Sales Account'
        else 'NA'
    end AS ACCOUNT_TYPE,
    ANALYSIS$$,
    client_number,
    INVNO$$,
    OPSREF$$_ORIGINAL,
    CONTYP,
    date_format(NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.LAST_DATE,
            '%d/%m/%Y') as 'Last Date',
    CASE NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.activity_type
        WHEN '13' then 'INVOICE'
        else 'NA'
    end AS LAST_TYPE
from
    NAMES_AND_ADDRESSES_CLIENTS_UNS
        left outer join
    CLIENT_DEFAULTS_ANALYSIS_CODES_UNS ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.CLIENT_KEY
        left outer join
    BRANCH_USAGE_ACCOUNT_NUMBERS_UNS ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.CLIENT$$
        left outer join
    NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.CLIENT$$
        left outer join
    INVOICE_HEADER_UNS ON NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.LAST_REF = INVOICE_HEADER_UNS.INVNO$$
        left outer join
    CONSIGNMENT_STATISTICS_UNS ON INVOICE_HEADER_UNS.OPSREF$$_ORIGINAL = CONSIGNMENT_STATISTICS_UNS.BOOKING_REFERENCE
WHERE
    CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.ANALYSIS$$ like 'EXCR3%'
        and BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE = '1'
        AND NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.ACTIVITY_TYPE = '13'
        OR CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.ANALYSIS$$ LIKE 'IMCR1%'
        and BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE = '1'
        AND NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.ACTIVITY_TYPE = '13'
        OR CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.ANALYSIS$$ = 'TEMPCR'
        AND NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.ACTIVITY_TYPE = '13'
        and BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE = '1'
        or CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.ANALYSIS$$ like 'EXCR6%'
        and BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE = '1'
        AND NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.ACTIVITY_TYPE = '13'
        or CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.ANALYSIS$$ like 'IMCR3%'
        and BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE = '1'
        AND NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.ACTIVITY_TYPE = '13'
        or CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.ANALYSIS$$ like 'IMCR6%'
        and BRANCH_USAGE_ACCOUNT_NUMBERS_UNS.TYPE = '1'
        AND NEW_SAM_ACTIVITIES_CTRL_HEADER_UNS.ACTIVITY_TYPE = '13'
Group by client_number
order by NAME_ADDRESS_1 ASC





Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 02/15/2013 :  18:12:39  Show Profile  Reply with Quote
1) This really should be done at the application layer.
2) In the case where there are more than two codes, how do you decide which two to select (e.g., "John Shipping has ANALYSIS$$ codes EXCR30, CLT_UF, IMCR30")?
3) If there is only one (or none) should data be returned anyway?
4) Have you looked into the PIVOT operator? (See BOL for details)

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

newtoitall
Starting Member

5 Posts

Posted - 02/18/2013 :  17:43:02  Show Profile  Reply with Quote
Hi, thanks for your reply
the purpose of this report is to determine if customer have either EXCR or IMCR code. It isn't necessary for the IM or EX codes to output on the report.

The important code to output is whether the customer is either a CLT_UF or CLT_UC (customer classification)

the filtering for EX and IM codes works fine, the part i'm struggling to work out is to then query the same column again to output whether customer is CLT_UF or CLT_UC.

is this just a SQL statement I'm trying to execute via the MySQL workbench, i'm not programming with this statement, just trying to filter the database for management reporting purposes.



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/18/2013 :  23:46:26  Show Profile  Reply with Quote
I dont know about MySQL

But in MS SQL Server T-SQL this can be done by applying GROUP BY and conditional aggregating

like

SELECT Analysis1,
MAX(CASE WHEN Analysis2 = 'CLT_UF' THEN Name END) AS CLT_UF,
MAX(CASE WHEN Analysis2 = 'CLT_UC' THEN Name END) AS CLT_UC
FROM Table
GROUP BY ANalysis1


I cant make out which columns in your query represents Analysis1,Analysis2 etc thats why i gave the stub for youto work on. you should replace the actual columns in above query to get your output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

newtoitall
Starting Member

5 Posts

Posted - 02/19/2013 :  04:33:20  Show Profile  Reply with Quote
Hi
Thanks for your reply.
Can you advise how I link the two stubs to analysis code column in the table?

Is this done at join level? Do I replace the exising join, this is what I cannot get my head around.

e.g.

left outer join
CLIENT_DEFAULTS_ANALYSIS_CODES_UNS ANALYSIS1 ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.CLIENT_KEY


left outer join
CLIENT_DEFAULTS_ANALYSIS_CODES_UNS ANALYSIS2 ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.CLIENT_KEY




quote:
Originally posted by visakh16

I dont know about MySQL

But in MS SQL Server T-SQL this can be done by applying GROUP BY and conditional aggregating

like

SELECT Analysis1,
MAX(CASE WHEN Analysis2 = 'CLT_UF' THEN Name END) AS CLT_UF,
MAX(CASE WHEN Analysis2 = 'CLT_UC' THEN Name END) AS CLT_UC
FROM Table
GROUP BY ANalysis1


I cant make out which columns in your query represents Analysis1,Analysis2 etc thats why i gave the stub for youto work on. you should replace the actual columns in above query to get your output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/19/2013 :  05:15:00  Show Profile  Reply with Quote
make join like

left outer join
(SELECT CLIENT_KEY,
MAX(CASE WHEN ANALYSIS$$ = 'CLT_UF' THEN NameFieldHere END) AS CLT_UF,
MAX(CASE WHEN ANALYSIS$$ = 'CLT_CF' THEN NameFieldHere END) AS CLT_CF,
FROM CLIENT_DEFAULTS_ANALYSIS_CODES_UNS 
GROUP BY CLIENT_KEY
)ANALYSIS1 ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = ANALYSIS1.CLIENT_KEY


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000