June 8, 2012 at 12:30 pm
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',
June 8, 2012 at 1:10 pm
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.
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
June 8, 2012 at 1:23 pm
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
June 8, 2012 at 1:39 pm
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.
June 8, 2012 at 1:47 pm
Hi Lynn,
Thanks for your reply.
Yep, join sure sounds like what I need.
How would I go about that type of join?
June 8, 2012 at 1:54 pm
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.
June 8, 2012 at 2:09 pm
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?
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
June 8, 2012 at 2:48 pm
Thanks Lynn.
Do I have to use a CTE? Always have trouble writing those..
Could I use a temp table?
June 9, 2012 at 8:13 am
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply