Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query the same column twice

Author  Topic 

newtoitall
Starting Member

5 Posts

Posted - 2013-02-15 : 09:23:03
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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-02-15 : 18:12:39
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 - 2013-02-18 : 17:43:02
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

52326 Posts

Posted - 2013-02-18 : 23:46:26
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 - 2013-02-19 : 04:33:20
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

52326 Posts

Posted - 2013-02-19 : 05:15:00
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
   

- Advertisement -