Using Union..but that's wrong..is there an un-union?

• krypto69

  SSChampion

  Points: 13604

  More actions

  June 8, 2012 at 12:30 pm

  #262077

  I have two statements (below) that do two different things.--With Terminations

  SELECT

  ----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',

  --EEPDATEOFBIRTH,

  CmpCompanyCode as 'Company Code',

  E.EecOrgLvl2 as 'Org Level',

  cmpCompanyName as 'Company Name',

  E.eecCoID as 'Co ID',

  E.EecEEID as 'EE ID', --stays with person, if listed more then once

  E.eecDateOfTermination as 'Term Date',

  E.eecEmplStatus as 'Emp Status',

  E.eecEmpNo as 'Emp Number',

  E.eecLocation as 'Location',

  E.eecFullTimeorPartTime as 'Full or Part',

  E.eecJobCode as 'Job Code',

  eepNameLast as 'Last Name',

  eepNameSuffix as 'Suffix',

  eepNameFirst as 'First Name',

  EepNameMiddle as 'Middle Name',

  E.EecJobtitle as 'Job Title',

  E.EecAnnSalary as 'Annual Salary',

  E.EecDateLastPayDatePaid as 'Last Pay Date',

  E.EecTermReason as 'Term Code'

  FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID

  JOIN Company ON eecCoID = cmpCoID

  WHERE EecDateOfTermination IS NOT NULL

  and not exists (select 1 from EmpComp e2 where e2.EecEEID = E.EecEEID and e2.eecEmplStatus = 'A')-- this is to filter out anyone that was termed then re-hired

  AND E.EecTermReason not in ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13')

  AND E.eecDateOfTermination in (SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= '12/31/2011' AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= '01/01/2011')))

  --AND DATEPART(YEAR, e.eecDateOfLastHire) = '2010'

  AND DATEPART(YEAR, e.eecDateOfTermination) = '2011'

  --AND E.EecOrgLvl2 = 'WSIALC'

  --ORDER BY eepNameLast,eepNameFirst DESC

  --GRoup by E.EecOrgLvl2

  --select distinct EecTermReason from EmpComp

  --order by EecTermReason

  --select distinct count (EecOrgLvl2) from EmpComp

  UNION all

  SELECT

  --E.AuditKey, --can't use newest key to get term info

  ----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',

  --E.eecDateOfLastHire,

  --EEPDATEOFBIRTH,

  CmpCompanyCode as 'Company Code',

  E.EecOrgLvl2 as 'Org Level',

  cmpCompanyName as 'Company Name',

  E.eecCoID as 'Co ID',

  E.EecEEID as 'EE ID', --stays with person, if listed more then once

  E.eecDateOfTermination as 'Term Date',

  E.eecEmplStatus as 'Emp Status',

  E.eecEmpNo as 'Emp Number',

  E.eecLocation as 'Location',

  E.eecFullTimeorPartTime as 'Full or Part',

  E.eecJobCode as 'Job Code',

  eepNameLast as 'Last Name',

  eepNameSuffix as 'Suffix',

  eepNameFirst as 'First Name',

  EepNameMiddle as 'Middle Name',

  E.EecJobtitle as 'Job Title',

  E.EecAnnSalary as 'Annual Salary',

  E.EecDateLastPayDatePaid as 'Last Pay Date',

  E.EecTermReason as 'Term Code'

  FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID

  JOIN Company C ON eecCoID = cmpCoID

  --WHERE EecEmplStatus in ('A','L')--

  --AND EecDateOfTermination is NULL

  --AND DATEPART(YEAR, e.eecDateOfLastHire) = '2012'

  WHERE (e.eecDateOfLastHire <= '12/31/2011'

  AND (E.eecDateOfTermination is null OR E.eecDateOfTermination >= '01/01/2011'))

  AND E.eecDateOfLastHire in (SELECT MAX(EC.eecDateOfLastHire) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfLastHire <= '12/31/2011' AND (EC.eecDateOfTermination is null OR EC.eecDateOfTermination >= '01/01/2011')))

  As you can see I have them in a union...and it works. But I know I don't really want to union. I'm looking for something that adds the seconds statment to the end of the first, not combine them.

  In the above statement I would like to be able to have the second statement return additional rows at the end. So that my seconds statement would have all those fields after the first statements fields.

  ie:

  from 1st statment-

  CmpCompanyCode as 'Company Code',

  E.EecOrgLvl2 as 'Org Level',

  cmpCompanyName as 'Company Name',

  E.eecCoID as 'Co ID',

  from 2nd statment

  CmpCompanyCode as 'Company Code_terms',

  E.EecOrgLvl2 as 'Org Level_terms',

  cmpCompanyName as 'Company Name_terms',

  E.eecCoID as 'Co ID_terms',

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  June 8, 2012 at 1:10 pm

  #1498900

  What you're looking to do is really return two different recordsets. The first query in a union defines what the field types/names/etc are. This is what we call metadata.

  Once that's set, you can't go mucking around with that in an attached set of rows to the first, they need to match.

  So, what you'd end up doing here is returning two unique recordsets to the calling application, one with the first definition, one with the second. It'll also keep the data better organized.

  I realize what you're trying to do here (and you can't), but what are you trying to end up at, in your final solution? What's the purpose of combining disparate data into a single recordset return? If you can let us know where you're trying to go, we can probably help you find a much better way to get there.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• krypto69

  SSChampion

  Points: 13604

  More actions

  June 8, 2012 at 1:23 pm

  #1498906

  Thanks Craig.

  It's a long ugly story, but I'm stuck with creating a report w/SSRS 2005. Those two queries are each used to populate a table on the report. Two tables, two different procs. Turns out that SSRS2005 doesn't not allow for me to reference the results from the two different tables. Apparently SSRS2008 does allow for that - doesn't help me tho.

  So what I am praying for- Is a way to combine the two statements so that I can put the new statement in one SSRS table. That way I can average results from both.

  So results would look something like -

  CmpCompanyCode E.EecOrgLvl2 CmpCompanyCode_2 E.EecOrgLvl2_2

  dataset_1 dataset_1 dataset_2 dataset_2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 8, 2012 at 1:39 pm

  #1498919

  krypto69 (6/8/2012)

  ---

  Thanks Craig.

  It's a long ugly story, but I'm stuck with creating a report w/SSRS 2005. Those two queries are each used to populate a table on the report. Two tables, two different procs. Turns out that SSRS2005 doesn't not allow for me to reference the results from the two different tables. Apparently SSRS2008 does allow for that - doesn't help me tho.

  So what I am praying for- Is a way to combine the two statements so that I can put the new statement in one SSRS table. That way I can average results from both.

  So results would look something like -

  CmpCompanyCode E.EecOrgLvl2 CmpCompanyCode_2 E.EecOrgLvl2_2

  dataset_1 dataset_1 dataset_2 dataset_2

  This looks more like a join between the two datasets than a union.

• krypto69

  SSChampion

  Points: 13604

  More actions

  June 8, 2012 at 1:47 pm

  #1498924

  Hi Lynn,

  Thanks for your reply.

  Yep, join sure sounds like what I need.

  How would I go about that type of join?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 8, 2012 at 1:54 pm

  #1498927

  krypto69 (6/8/2012)

  ---

  Hi Lynn,

  Thanks for your reply.

  Yep, join sure sounds like what I need.

  How would I go about that type of join?

  Make the second query a CTE, then join it to the first like any other table.

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  June 8, 2012 at 2:09 pm

  #1498934

  krypto69 (6/8/2012)

  ---

  Hi Lynn,

  Thanks for your reply.

  Yep, join sure sounds like what I need.

  How would I go about that type of join?

  Krypto, that would be difficult for us to answer as we're not sure of your data-layout and the like. All we've seen so far is the originating query (which uses JOINs). We could tell you the technical way to layout the join but we don't understand the data-relationships and the like to tell you the proper ON clause for it to layout the way you like.

  Is there anyone else at the company who has a solid grasp of the data-relationships that you're pulling from that can help you figure that part out?

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• krypto69

  SSChampion

  Points: 13604

  More actions

  June 8, 2012 at 2:48 pm

  #1498946

  Thanks Lynn.

  Do I have to use a CTE? Always have trouble writing those..

  Could I use a temp table?

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  June 9, 2012 at 8:13 am

  #1499015

  krypto69 (6/8/2012)

  ---

  Thanks Lynn.

  Do I have to use a CTE? Always have trouble writing those..

  Could I use a temp table?

  Yes. You can use a temp table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)