Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query Help - Huge BigTime Smarts needed

Posted on 2016-07-28
11
49 Views
Last Modified: 2016-07-28
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
0
Comment
Question by:jamesmetcalf74
11 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41733522
This expert suggested creating a Gigs project.
>This is beyond what is probably normally asked as far as help on Experts Exchange.
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.
0
 

Author Comment

by:jamesmetcalf74
ID: 41733526
Totally Understand.... do you do GIgs?
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41733533
I think I understand what you're looking for, but it'll take a minute to make test tables.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:jamesmetcalf74
ID: 41733539
Sweet!  Let me know if I need to go to gigs Dustin and I surely will.
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41733557
So, in TableB you have this:
0951000401	WsteWtr HBTS Inside Res	784551	34.87	1091	1186
0951000401	WsteWtr HBTS Inside Res	784551	34.87	1091	1186

Open in new window


But only one in results:
0951000401	MS [065]	34.87	0951000401	WsteWtr HBTS Inside Res	34.87	1091	1186

Open in new window


Does Table B contain duplicate records or was that a mistake?  Is there a unique ID for either TableA or TableB?
0
 

Author Comment

by:jamesmetcalf74
ID: 41733568
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.
0
 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 500 total points
ID: 41733587
I always do an identity but i guess it doesn't matter really.  Anyways, this should work.  Using your data, I created TABLEA, TABLEB (without the dupes), and TABLEC for the service option translation.

This query:
SELECT a.ACCOUNTNUMBER, a.Service, a.ServiceAmount, b.accountnumber, b.description, b.lineitemamt, b.serviceoptionskey, b.LINEITEMSETUPKEY
FROM TABLEA a
JOIN TABLEC c ON c.serviceoptionstablea = a.Service
LEFT JOIN TABLEB b ON c.serviceoptionstableb = b.serviceoptionskey AND b.accountnumber = a.ACCOUNTNUMBER
WHERE EXISTS (SELECT * FROM TABLEC WHERE serviceoptionstablea = a.Service)

Open in new window


Produces this result:
ACCOUNTNUMBER                                      Service                                            ServiceAmount          accountnumber                                      description                                        lineitemamt            serviceoptionskey LINEITEMSETUPKEY
-------------------------------------------------- -------------------------------------------------- ---------------------- -------------------------------------------------- -------------------------------------------------- ---------------------- ----------------- ----------------
0951000401                                         GB [001]                                           16.5                   0951000401                                         Garbage Residential 90 Gallon                      16.5                   1059              1084
0951000401                                         MS [065]                                           34.87                  0951000401                                         WsteWtr HBTS Inside Res                            34.87                  1091              1186
0951000401                                         ST [081]                                           2.74                   0951000401                                         Storm HBTS Inside ResSm                            2.74                   1069              1156
0951000401                                         VM [001]                                           20                     NULL                                               NULL                                               NULL                   NULL              NULL
0951000501                                         GB [001]                                           16.5                   0951000501                                         Garbage Residential 90 Gallon                      16.5                   1059              1084
0951000501                                         MS [065]                                           31.82                  0951000501                                         WsteWtr HBTS Inside Res                            31.82                  1091              1186
0951000501                                         ST [081]                                           2.74                   0951000501                                         Storm HBTS Inside ResSm                            2.74                   1069              1156

Open in new window

0
 

Author Comment

by:jamesmetcalf74
ID: 41733680
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!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41733703
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.
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41733710
Ah yeah, I put that in when I was initially building the query but you're right, you can drop that.
0
 

Author Closing Comment

by:jamesmetcalf74
ID: 41733761
Awesome!
Totally Friggin Awesome!
everyline item accounted for and it matched perfectly
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
SQL Activity Monitor detail 2 28
SQL create line numbers for data sampling 11 28
Error when creating a table from a function 6 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question