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.
Author |
Topic |
newtoitall
Starting Member
5 Posts |
Posted - 2013-02-15 : 09:23:03
|
HiI 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, IMCR30Pete Shipping has ANALYSIS$$ codes EXCR30, CLT_UCNow, 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_UFThen outputting these results in different columnsName------------Analysis1---Analysis2John Shipping---EXCR30--------CLT_UFPete Shipping---EXCR30--------CLT_UCOnly 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_TYPEfrom 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_REFERENCEWHERE 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_numberorder 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 |
|
|
newtoitall
Starting Member
5 Posts |
Posted - 2013-02-18 : 17:43:02
|
Hi, thanks for your replythe 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
52326 Posts |
Posted - 2013-02-18 : 23:46:26
|
I dont know about MySQLBut in MS SQL Server T-SQL this can be done by applying GROUP BY and conditional aggregatinglikeSELECT Analysis1,MAX(CASE WHEN Analysis2 = 'CLT_UF' THEN Name END) AS CLT_UF,MAX(CASE WHEN Analysis2 = 'CLT_UC' THEN Name END) AS CLT_UCFROM TableGROUP 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 MVPhttp://visakhm.blogspot.com/ |
|
|
newtoitall
Starting Member
5 Posts |
Posted - 2013-02-19 : 04:33:20
|
HiThanks 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_KEYleft outer join CLIENT_DEFAULTS_ANALYSIS_CODES_UNS ANALYSIS2 ON NAMES_AND_ADDRESSES_CLIENTS_UNS.client_number = CLIENT_DEFAULTS_ANALYSIS_CODES_UNS.CLIENT_KEYquote: Originally posted by visakh16 I dont know about MySQLBut in MS SQL Server T-SQL this can be done by applying GROUP BY and conditional aggregatinglikeSELECT Analysis1,MAX(CASE WHEN Analysis2 = 'CLT_UF' THEN Name END) AS CLT_UF,MAX(CASE WHEN Analysis2 = 'CLT_UC' THEN Name END) AS CLT_UCFROM TableGROUP 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 MVPhttp://visakhm.blogspot.com/
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|