| Author |
 Topic  |
|
|
newtoitall
Starting Member
3 Posts |
 Posted - 02/15/2013 : 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
Flowing Fount of Yak Knowledge
USA
1435 Posts |
Posted - 02/15/2013 : 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 |
 |
|
|
newtoitall
Starting Member
3 Posts |
Posted - 02/18/2013 : 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.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 02/18/2013 : 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/
|
|
|
|
newtoitall
Starting Member
3 Posts |
Posted - 02/19/2013 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 02/19/2013 : 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/
|
|
|
| |
Topic |
|
|
|
Query the same column twice
