Query Help - Huge BigTime Smarts needed
Guys-
This is beyond what is probably normally asked as far as help on Experts Exchange.
I thought I would take a shot and ask anyway. Attached is a spreadsheet.
At the top of the spreadsheet you will see a sample of the two tables involved in the query. TableA and TableB
There is a common identifier with the AccountNumber column between the two tables.
The accounts basically have the same services listed. The way the services are listed is completely different unfortunately.
The services translate between the tables though. Please refer to the excel cells highlighted under the title.
"Service Option Translation - Column 'N' Corresponds to Column 'O'. Highlighted with bright orange in background and Red Bold Text.
The desired Results table in the bottom left shows the desired information deduced from TableA and TableB.
Table A columns data is specified in blue and Table B columns data highlighted in Yellow.
The columns used in the original tables are highlighted in Dark Orange to help.
The match needs to be based on two variables. A.Accountnumber = B.Accountnumber and also A.Service = B.ServiceOptionsKey
Problem.... A.Service and B.ServiceOptionskey are not the same data but they are consistently translated if that makes sense.
Please refer to excel section labeled "Service Option Translation" highlighted in Orange with Red Text for the Translation "key"
I would also need for line items to show up that do not have a match. This is highlighted in light green. this occurs when an account
has a service that does not translate.... Meaning... there isn't a corresponding service set up on Table B
I am open to creating views or a new table to provide the translation.
Experts-Exchange-Help.xlsx
This is beyond what is probably normally asked as far as help on Experts Exchange.
I thought I would take a shot and ask anyway. Attached is a spreadsheet.
At the top of the spreadsheet you will see a sample of the two tables involved in the query. TableA and TableB
There is a common identifier with the AccountNumber column between the two tables.
The accounts basically have the same services listed. The way the services are listed is completely different unfortunately.
The services translate between the tables though. Please refer to the excel cells highlighted under the title.
"Service Option Translation - Column 'N' Corresponds to Column 'O'. Highlighted with bright orange in background and Red Bold Text.
The desired Results table in the bottom left shows the desired information deduced from TableA and TableB.
Table A columns data is specified in blue and Table B columns data highlighted in Yellow.
The columns used in the original tables are highlighted in Dark Orange to help.
The match needs to be based on two variables. A.Accountnumber = B.Accountnumber and also A.Service = B.ServiceOptionsKey
Problem.... A.Service and B.ServiceOptionskey are not the same data but they are consistently translated if that makes sense.
Please refer to excel section labeled "Service Option Translation" highlighted in Orange with Red Text for the Translation "key"
I would also need for line items to show up that do not have a match. This is highlighted in light green. this occurs when an account
has a service that does not translate.... Meaning... there isn't a corresponding service set up on Table B
I am open to creating views or a new table to provide the translation.
Experts-Exchange-Help.xlsx
ASKER
Totally Understand.... do you do GIgs?
I think I understand what you're looking for, but it'll take a minute to make test tables.
ASKER
Sweet! Let me know if I need to go to gigs Dustin and I surely will.
So, in TableB you have this:
But only one in results:
Does Table B contain duplicate records or was that a mistake? Is there a unique ID for either TableA or TableB?
0951000401 WsteWtr HBTS Inside Res 784551 34.87 1091 1186
0951000401 WsteWtr HBTS Inside Res 784551 34.87 1091 1186But only one in results:
0951000401 MS [065] 34.87 0951000401 WsteWtr HBTS Inside Res 34.87 1091 1186Does Table B contain duplicate records or was that a mistake? Is there a unique ID for either TableA or TableB?
ASKER
My mistake... there should only be one line item of that data.
There is no unique id for tablea or tableB at this point. I am open to creating one if need be.
This data that we are looking at is not dynamic. It is from a bill run... once the bill run completes. It is done.... So if need be... i can export the bulk of the data out... work with it in excel and import back in if you need certain columns added. Preparation of the data for a better query is doable with this data.
There is no unique id for tablea or tableB at this point. I am open to creating one if need be.
This data that we are looking at is not dynamic. It is from a bill run... once the bill run completes. It is done.... So if need be... i can export the bulk of the data out... work with it in excel and import back in if you need certain columns added. Preparation of the data for a better query is doable with this data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wanted to give you a heads up since you have been so receptive and time sensitive. I greatly appreciate it. I configured one table wrong and have to head out to a meeting. I think i will have you an update in about 2 hours.... again... thanks a ton for this. I will get back to you soon!
One minor point: you don't need the WHERE clause:
WHERE EXISTS (SELECT * FROM TABLEC WHERE serviceoptionstablea = a.Service)
on the query, because the INNER JOIN to TABLEC has already enforced that a tablec row must exist.
WHERE EXISTS (SELECT * FROM TABLEC WHERE serviceoptionstablea = a.Service)
on the query, because the INNER JOIN to TABLEC has already enforced that a tablec row must exist.
Ah yeah, I put that in when I was initially building the query but you're right, you can drop that.
ASKER
Awesome!
Totally Friggin Awesome!
everyline item accounted for and it matched perfectly
Totally Friggin Awesome!
everyline item accounted for and it matched perfectly
Ok but just to set expectations that and huge big-time smarts would lend itself to Live or Gigs as opposed to a single question.