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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 New to SQL - Help with Query

Author  Topic 

werseyjersey
Starting Member

47 Posts
Posted - 2009-03-17 : 15:26:10
Hi. I have two queries that work well alone. I am new to SQL and need to put them together. I know this seems elementary to those who do this alot but I don't.

QUERY #1
SELECT SUM(tPOSITION_CODES.FTEBudget) as FTEBudget_Position,
M_CostCenter_6_Curr.GLSegment


FROM tPOSITION_CODES INNER JOIN
M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo =
M_CostCenter_6_Curr.PositionIdNo


GROUP BY M_CostCenter_6_Curr.GLSegment


order by GLSegment

QUERY #2
SELECT DISTINCT
M_CostCenter_6_Curr.GLSegment AS GL_CC, M_CostCenter_6_Curr.OrganizationDescription, tSY_PERSON_CODES.PersonCode AS TerminationType,
tPERSONS.LastName, tPERSONS.FirstName, tPERSONS.MiddleName, tSY_PERSON_CODES_1.PersonCode AS Gender, tETHNIC_CODES.EthnicCode,
tREASON_CODES.ReasonCode, CONVERT(varchar(10),tEMPLOYMENT_STATUS.OriginalHireDate, 101) AS OrigHireDt,
CONVERT(varchar(10), tEMPLOYMENT_STATUS.TerminationDate, 101) AS TermDate, CONVERT(varchar(10), tEMPLOYMENT_STATUS.LastHireDate, 101) AS LastHireDt, DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25 AS YrsofSrvcOrgHire,
tPOSITION_CODES.FTEBudget, tPOSITION_CODES.FTEBudgetHours, tPERSON_POSITIONS.FTEHoursPerWeek, tPOSITION_CODES.FTEHoursPerWeek AS PosFTEHrs,
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.00000000 AND 0.24640657 THEN 1 ELSE 0 END) AS [<90 days],
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.24640658 AND
0.99999999 THEN 1 ELSE 0 END) AS [<1 year],
CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate,
tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 1.00000000 AND 2.99999999 THEN 1 ELSE 0 END) AS [<3 year],
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) > 3.00000000 THEN 1 ELSE 0 END) AS [>3 year],
(CASE WHEN tSY_PERSON_CODES.PersonCode IN ('Voluntary') THEN 1 ELSE 0 END) AS Voluntary,
(CASE WHEN tSY_PERSON_CODES.PersonCode IN 'Involuntary', 'Uncontrollable') THEN 1 ELSE 0 END) AS Involuntary,
(CASE WHEN tETHNIC_CODES.EthnicCode IN ('Ameri Indn/Alask Nativ', 'Asian', 'Black or African Ameri', 'Hispanic or Latino',
'Nativ Hawii/Othr PacIsldr') THEN 1 ELSE 0 END) AS Minority,
(CASE WHEN tETHNIC_CODES.EthnicCode IN ('White') THEN 1 ELSE 0 END)
AS NonMinority, (CASE WHEN (TermReasonCodeCategory = 1) THEN 1 ELSE 0 END) AS Attendance,
(CASE WHEN (TermReasonCodeCategory = 2)
THEN 1 ELSE 0 END) AS [Conduct/Violation of Rules],
(CASE WHEN (TermReasonCodeCategory = 3) THEN 1 ELSE 0 END) AS [Lost Security Clearance],
(CASE WHEN (TermReasonCodeCategory = 4) THEN 1 ELSE 0 END) AS [Unsatisfactory Performance],
(CASE WHEN (TermReasonCodeCategory = 5) THEN 1 ELSE 0 END) AS [Dislike type of work/Job],
(CASE WHEN (TermReasonCodeCategory = 6) THEN 1 ELSE 0 END) AS [Dissatified w/Supervisor/Mgmt],
(CASE WHEN (TermReasonCodeCategory = 7) THEN 1 ELSE 0 END) AS
[Job Abandonment/No Call No Show],
(CASE WHEN (TermReasonCodeCategory = 8) THEN 1 ELSE 0 END) AS [Salary/Career Advancement],
(CASE WHEN (TermReasonCodeCategory = 9) THEN 1 ELSE 0 END) AS [Relocation/Retire/School],
(CASE WHEN (TermReasonCodeCategory = 10) THEN 1 ELSE 0 END) AS [Personal/Other Emp],
(CASE WHEN (TermReasonCodeCategory = 11) THEN 1 ELSE 0 END) AS
[No Return for LOA],
(CASE WHEN (TermReasonCodeCategory = 12) THEN 1 ELSE 0 END) AS
[No Reason Given/Resignation],
(CASE WHEN (TermReasonCodeCategory = 13) THEN 1 ELSE 0 END) AS [Position Elim/Company Reason],
(CASE WHEN (TermReasonCodeCategory = 14) THEN 1 ELSE 0 END) AS [Unavailable to Work], tPOSITION_CODES.PositionCodeUserDefined2, tPERSON_POSITIONS.PositionToEffectDate, tPERSON_POSITIONS.PositionFromEffectDate,
tPERSONS.PersonToEffectDate, tPERSONS.PersonFromEffectDate, tPOSITION_CODES.PositionCodeToEffectDate,
tPOSITION_CODES.PositionCodeFromEffectDate, tEMPLOYMENT_CODES.EmploymentCode, tEMPLOYMENT_STATUS.TerminationDate,
tPOSITION_CODES.PositionCodeDescription


FROM tEMPLOYMENT_CODES INNER JOIN
tEMPLOYMENT_STATUS ON tEMPLOYMENT_CODES.EmploymentCodeIdNo = tEMPLOYMENT_STATUS.EmploymentStatus INNER JOIN
tSY_PERSON_CODES ON tEMPLOYMENT_STATUS.TerminationTypeIdNo = tSY_PERSON_CODES.PersonCodeIdNo INNER JOIN
tREASON_CODES ON tEMPLOYMENT_STATUS.EmploymentStatusReasonIdNo = tREASON_CODES.ReasonCodeIdNo INNER JOIN
tPERSONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSONS.PersonIdNo INNER JOIN
tETHNIC_CODES ON tPERSONS.EthnicCodeIdNo = tETHNIC_CODES.EthnicCodeIdNo INNER JOIN
tSY_PERSON_CODES AS tSY_PERSON_CODES_1 ON tPERSONS.GenderIdNo = tSY_PERSON_CODES_1.PersonCodeIdNo INNER JOIN
tPERSON_POSITIONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSON_POSITIONS.PersonIdNo INNER JOIN
tPOSITION_CODES ON tPERSON_POSITIONS.PositionIdNo = tPOSITION_CODES.PositionIdNo INNER JOIN
M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo INNER JOIN
CCSCustom_TermReasons ON tREASON_CODES.ReasonCodeIdNo = CCSCustom_TermReasons.ReasonCodeIdNo


WHERE tPERSON_POSITIONS.PositionToEffectDate = '3000-01-01 00:00:00' AND tPERSONS.PersonToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)
AND tPOSITION_CODES.PositionCodeToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)
AND tREASON_CODES.ReasonCode <> 'TERDEC-Declined Employ'
AND tEMPLOYMENT_CODES.EmploymentCode = 'Terminated'
AND tSY_PERSON_CODES.PersonCode <> 'None'
AND tPOSITION_CODES.PositionCodeDescription NOT LIKE '%PRN%'
AND tPOSITION_CODES.PositionCodeUserDefined2 NOT LIKE '%PRN%'
AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%Subs%'
AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%KSMD%'
AND tEMPLOYMENT_STATUS.TerminationDate BETWEEN '1-1-2008' and '12-31-2008'


ORDER BY tPERSONS.LastName

werseyjersey

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-03-18 : 05:41:55
Hi.

What do you mean by put the together? Do you mean that the output of one should be concatenated to the other. In that case look up UNION ALL (and make sure the the same number / datatypes of columns are returned by both quesries -- add NULLS if nescessary.

Or do you mean that your first query should be JOIN'ed into the second somehow.

In either case -- it would be really helpfull if you could provide sample data and expected output.

Regards,


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2009-03-18 : 09:33:31
The first and second query would be join'ed into one query with the results being the combination of both queries. I get a little confused on putting them together into one query.

werseyjersey
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-03-18 : 09:54:18
Maybe thin?

SELECT DISTINCT 
M_CostCenter_6_Curr.GLSegment AS GL_CC, M_CostCenter_6_Curr.OrganizationDescription, tSY_PERSON_CODES.PersonCode AS TerminationType, 
tPERSONS.LastName, tPERSONS.FirstName, tPERSONS.MiddleName, tSY_PERSON_CODES_1.PersonCode AS Gender, tETHNIC_CODES.EthnicCode, 
tREASON_CODES.ReasonCode, CONVERT(varchar(10),tEMPLOYMENT_STATUS.OriginalHireDate, 101) AS OrigHireDt, 
CONVERT(varchar(10), tEMPLOYMENT_STATUS.TerminationDate, 101) AS TermDate, CONVERT(varchar(10), tEMPLOYMENT_STATUS.LastHireDate, 101) AS LastHireDt, DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25 AS YrsofSrvcOrgHire, 
tPOSITION_CODES.FTEBudget, tPOSITION_CODES.FTEBudgetHours, tPERSON_POSITIONS.FTEHoursPerWeek, tPOSITION_CODES.FTEHoursPerWeek AS PosFTEHrs, 
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.00000000 AND 0.24640657 THEN 1 ELSE 0 END) AS [<90 days], 
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.24640658 AND
0.99999999 THEN 1 ELSE 0 END) AS [<1 year], 
CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, 
tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 1.00000000 AND 2.99999999 THEN 1 ELSE 0 END) AS [<3 year], 
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) > 3.00000000 THEN 1 ELSE 0 END) AS [>3 year], 
(CASE WHEN tSY_PERSON_CODES.PersonCode IN ('Voluntary') THEN 1 ELSE 0 END) AS Voluntary, 
(CASE WHEN tSY_PERSON_CODES.PersonCode IN 'Involuntary', 'Uncontrollable') THEN 1 ELSE 0 END) AS Involuntary, 
(CASE WHEN tETHNIC_CODES.EthnicCode IN ('Ameri Indn/Alask Nativ', 'Asian', 'Black or African Ameri', 'Hispanic or Latino', 
'Nativ Hawii/Othr PacIsldr') THEN 1 ELSE 0 END) AS Minority, 
(CASE WHEN tETHNIC_CODES.EthnicCode IN ('White') THEN 1 ELSE 0 END) 
AS NonMinority, (CASE WHEN (TermReasonCodeCategory = 1) THEN 1 ELSE 0 END) AS Attendance, 
(CASE WHEN (TermReasonCodeCategory = 2) 
THEN 1 ELSE 0 END) AS [Conduct/Violation of Rules], 
(CASE WHEN (TermReasonCodeCategory = 3) THEN 1 ELSE 0 END) AS [Lost Security Clearance],
(CASE WHEN (TermReasonCodeCategory = 4) THEN 1 ELSE 0 END) AS [Unsatisfactory Performance], 
(CASE WHEN (TermReasonCodeCategory = 5) THEN 1 ELSE 0 END) AS [Dislike type of work/Job], 
(CASE WHEN (TermReasonCodeCategory = 6) THEN 1 ELSE 0 END) AS [Dissatified w/Supervisor/Mgmt], 
(CASE WHEN (TermReasonCodeCategory = 7) THEN 1 ELSE 0 END) AS 
[Job Abandonment/No Call No Show], 
(CASE WHEN (TermReasonCodeCategory = 8) THEN 1 ELSE 0 END) AS [Salary/Career Advancement], 
(CASE WHEN (TermReasonCodeCategory = 9) THEN 1 ELSE 0 END) AS [Relocation/Retire/School], 
(CASE WHEN (TermReasonCodeCategory = 10) THEN 1 ELSE 0 END) AS [Personal/Other Emp], 
(CASE WHEN (TermReasonCodeCategory = 11) THEN 1 ELSE 0 END) AS 
[No Return for LOA], 
(CASE WHEN (TermReasonCodeCategory = 12) THEN 1 ELSE 0 END) AS 
[No Reason Given/Resignation],
(CASE WHEN (TermReasonCodeCategory = 13) THEN 1 ELSE 0 END) AS [Position Elim/Company Reason], 
(CASE WHEN (TermReasonCodeCategory = 14) THEN 1 ELSE 0 END) AS [Unavailable to Work], tPOSITION_CODES.PositionCodeUserDefined2, tPERSON_POSITIONS.PositionToEffectDate, tPERSON_POSITIONS.PositionFromEffectDate, 
tPERSONS.PersonToEffectDate, tPERSONS.PersonFromEffectDate, tPOSITION_CODES.PositionCodeToEffectDate, 
tPOSITION_CODES.PositionCodeFromEffectDate, tEMPLOYMENT_CODES.EmploymentCode, tEMPLOYMENT_STATUS.TerminationDate, 
tPOSITION_CODES.PositionCodeDescription

, posBud.[FTEBudget_Position]


FROM tEMPLOYMENT_CODES INNER JOIN
tEMPLOYMENT_STATUS ON tEMPLOYMENT_CODES.EmploymentCodeIdNo = tEMPLOYMENT_STATUS.EmploymentStatus INNER JOIN
tSY_PERSON_CODES ON tEMPLOYMENT_STATUS.TerminationTypeIdNo = tSY_PERSON_CODES.PersonCodeIdNo INNER JOIN
tREASON_CODES ON tEMPLOYMENT_STATUS.EmploymentStatusReasonIdNo = tREASON_CODES.ReasonCodeIdNo INNER JOIN
tPERSONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSONS.PersonIdNo INNER JOIN
tETHNIC_CODES ON tPERSONS.EthnicCodeIdNo = tETHNIC_CODES.EthnicCodeIdNo INNER JOIN
tSY_PERSON_CODES AS tSY_PERSON_CODES_1 ON tPERSONS.GenderIdNo = tSY_PERSON_CODES_1.PersonCodeIdNo INNER JOIN
tPERSON_POSITIONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSON_POSITIONS.PersonIdNo INNER JOIN
tPOSITION_CODES ON tPERSON_POSITIONS.PositionIdNo = tPOSITION_CODES.PositionIdNo INNER JOIN
M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo INNER JOIN
CCSCustom_TermReasons ON tREASON_CODES.ReasonCodeIdNo = CCSCustom_TermReasons.ReasonCodeIdNo

LEFT JOIN (
	SELECT
		SUM(tPOSITION_CODES.FTEBudget) AS [FTEBudget_Position]
		, M_CostCenter_6_Curr.GLSegment AS [GLSegment]
	FROM
		tPOSITION_CODES
		INNER JOIN M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo
	GROUP BY
		M_CostCenter_6_Curr.GLSegment
	)
	posBud ON posBud.[GLSegment] = M_CostCenter_6_Curr.GLSegment



WHERE tPERSON_POSITIONS.PositionToEffectDate = '3000-01-01 00:00:00' AND tPERSONS.PersonToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102) 
AND tPOSITION_CODES.PositionCodeToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)
AND tREASON_CODES.ReasonCode <> 'TERDEC-Declined Employ'
AND tEMPLOYMENT_CODES.EmploymentCode = 'Terminated' 
AND tSY_PERSON_CODES.PersonCode <> 'None' 
AND tPOSITION_CODES.PositionCodeDescription NOT LIKE '%PRN%'
AND tPOSITION_CODES.PositionCodeUserDefined2 NOT LIKE '%PRN%'
AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%Subs%'
AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%KSMD%' 
AND tEMPLOYMENT_STATUS.TerminationDate BETWEEN '1-1-2008' and '12-31-2008'


ORDER BY tPERSONS.LastName

OH -- Also *please* do some sort of formatting of your code -- looking through that mess is horrible and you'll get a lot more support if people can read what you are posting.



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2009-03-18 : 15:13:38
Sorry Charlie I tried to format it but the website doesn't let you do a lot of it on the posting. Unless I don't understand how.

werseyjersey
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2009-03-18 : 16:06:17
One other question. The query above works great. THANKS Charlie. Now if I want all rows to show even those that don't have matches in the first query do I change the join? IF so I have tried a RIGHT OUTER and LEFT OUTER but they don't seem to produce what I want. Maybe I did something wrong.

werseyjersey
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-03-19 : 06:13:28
USe RIGHT OUTER JOIN -- but you will also have to modify the WHERE clause -- the RIGHT JOIN will return NULL for all the columns in your first query. The WHERE clause is eliminating those rows.

You can either:

Switch the queries round (so that the part I put in red is the primary query) and put the bigger portion in a derived table using the syntax like I posted in red.

OR

Change to RIGHT JOIN and then change the WHERE clause

For each clause change it like this:

WHERE
	( tPERSON_POSITIONS.PositionToEffectDate = '3000-01-01 00:00:00' OR tPERSON_POSITIONS.PositionToEffectDate IS NULL )
	AND ( tPERSONS.PersonToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102) OR tPERSONS.PersonToEffectDate IS NULL )


Switching the queries round is probably the more elegant solution but it takes more work.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-03-19 : 06:16:53
Actually -- you don't need to do this for all clauses: I think this should work

SELECT DISTINCT 
M_CostCenter_6_Curr.GLSegment AS GL_CC, M_CostCenter_6_Curr.OrganizationDescription, tSY_PERSON_CODES.PersonCode AS TerminationType, 
tPERSONS.LastName, tPERSONS.FirstName, tPERSONS.MiddleName, tSY_PERSON_CODES_1.PersonCode AS Gender, tETHNIC_CODES.EthnicCode, 
tREASON_CODES.ReasonCode, CONVERT(varchar(10),tEMPLOYMENT_STATUS.OriginalHireDate, 101) AS OrigHireDt, 
CONVERT(varchar(10), tEMPLOYMENT_STATUS.TerminationDate, 101) AS TermDate, CONVERT(varchar(10), tEMPLOYMENT_STATUS.LastHireDate, 101) AS LastHireDt, DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25 AS YrsofSrvcOrgHire, 
tPOSITION_CODES.FTEBudget, tPOSITION_CODES.FTEBudgetHours, tPERSON_POSITIONS.FTEHoursPerWeek, tPOSITION_CODES.FTEHoursPerWeek AS PosFTEHrs, 
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.00000000 AND 0.24640657 THEN 1 ELSE 0 END) AS [<90 days], 
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.24640658 AND
0.99999999 THEN 1 ELSE 0 END) AS [<1 year], 
CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, 
tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 1.00000000 AND 2.99999999 THEN 1 ELSE 0 END) AS [<3 year], 
(CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) > 3.00000000 THEN 1 ELSE 0 END) AS [>3 year], 
(CASE WHEN tSY_PERSON_CODES.PersonCode IN ('Voluntary') THEN 1 ELSE 0 END) AS Voluntary, 
(CASE WHEN tSY_PERSON_CODES.PersonCode IN 'Involuntary', 'Uncontrollable') THEN 1 ELSE 0 END) AS Involuntary, 
(CASE WHEN tETHNIC_CODES.EthnicCode IN ('Ameri Indn/Alask Nativ', 'Asian', 'Black or African Ameri', 'Hispanic or Latino', 
'Nativ Hawii/Othr PacIsldr') THEN 1 ELSE 0 END) AS Minority, 
(CASE WHEN tETHNIC_CODES.EthnicCode IN ('White') THEN 1 ELSE 0 END) 
AS NonMinority, (CASE WHEN (TermReasonCodeCategory = 1) THEN 1 ELSE 0 END) AS Attendance, 
(CASE WHEN (TermReasonCodeCategory = 2) 
THEN 1 ELSE 0 END) AS [Conduct/Violation of Rules], 
(CASE WHEN (TermReasonCodeCategory = 3) THEN 1 ELSE 0 END) AS [Lost Security Clearance],
(CASE WHEN (TermReasonCodeCategory = 4) THEN 1 ELSE 0 END) AS [Unsatisfactory Performance], 
(CASE WHEN (TermReasonCodeCategory = 5) THEN 1 ELSE 0 END) AS [Dislike type of work/Job], 
(CASE WHEN (TermReasonCodeCategory = 6) THEN 1 ELSE 0 END) AS [Dissatified w/Supervisor/Mgmt], 
(CASE WHEN (TermReasonCodeCategory = 7) THEN 1 ELSE 0 END) AS 
[Job Abandonment/No Call No Show], 
(CASE WHEN (TermReasonCodeCategory = 8) THEN 1 ELSE 0 END) AS [Salary/Career Advancement], 
(CASE WHEN (TermReasonCodeCategory = 9) THEN 1 ELSE 0 END) AS [Relocation/Retire/School], 
(CASE WHEN (TermReasonCodeCategory = 10) THEN 1 ELSE 0 END) AS [Personal/Other Emp], 
(CASE WHEN (TermReasonCodeCategory = 11) THEN 1 ELSE 0 END) AS 
[No Return for LOA], 
(CASE WHEN (TermReasonCodeCategory = 12) THEN 1 ELSE 0 END) AS 
[No Reason Given/Resignation],
(CASE WHEN (TermReasonCodeCategory = 13) THEN 1 ELSE 0 END) AS [Position Elim/Company Reason], 
(CASE WHEN (TermReasonCodeCategory = 14) THEN 1 ELSE 0 END) AS [Unavailable to Work], tPOSITION_CODES.PositionCodeUserDefined2, tPERSON_POSITIONS.PositionToEffectDate, tPERSON_POSITIONS.PositionFromEffectDate, 
tPERSONS.PersonToEffectDate, tPERSONS.PersonFromEffectDate, tPOSITION_CODES.PositionCodeToEffectDate, 
tPOSITION_CODES.PositionCodeFromEffectDate, tEMPLOYMENT_CODES.EmploymentCode, tEMPLOYMENT_STATUS.TerminationDate, 
tPOSITION_CODES.PositionCodeDescription

, posBud.[FTEBudget_Position]


FROM tEMPLOYMENT_CODES INNER JOIN
tEMPLOYMENT_STATUS ON tEMPLOYMENT_CODES.EmploymentCodeIdNo = tEMPLOYMENT_STATUS.EmploymentStatus INNER JOIN
tSY_PERSON_CODES ON tEMPLOYMENT_STATUS.TerminationTypeIdNo = tSY_PERSON_CODES.PersonCodeIdNo INNER JOIN
tREASON_CODES ON tEMPLOYMENT_STATUS.EmploymentStatusReasonIdNo = tREASON_CODES.ReasonCodeIdNo INNER JOIN
tPERSONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSONS.PersonIdNo INNER JOIN
tETHNIC_CODES ON tPERSONS.EthnicCodeIdNo = tETHNIC_CODES.EthnicCodeIdNo INNER JOIN
tSY_PERSON_CODES AS tSY_PERSON_CODES_1 ON tPERSONS.GenderIdNo = tSY_PERSON_CODES_1.PersonCodeIdNo INNER JOIN
tPERSON_POSITIONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSON_POSITIONS.PersonIdNo INNER JOIN
tPOSITION_CODES ON tPERSON_POSITIONS.PositionIdNo = tPOSITION_CODES.PositionIdNo INNER JOIN
M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo INNER JOIN
CCSCustom_TermReasons ON tREASON_CODES.ReasonCodeIdNo = CCSCustom_TermReasons.ReasonCodeIdNo

RIGHT JOIN (
	SELECT
		SUM(tPOSITION_CODES.FTEBudget) AS [FTEBudget_Position]
		, M_CostCenter_6_Curr.GLSegment AS [GLSegment]
	FROM
		tPOSITION_CODES
		INNER JOIN M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo
	GROUP BY
		M_CostCenter_6_Curr.GLSegment
	)
	posBud ON posBud.[GLSegment] = M_CostCenter_6_Curr.GLSegment



WHERE
	(
	tPERSON_POSITIONS.PositionToEffectDate = '3000-01-01 00:00:00'
	AND tPERSONS.PersonToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102) 
	AND tPOSITION_CODES.PositionCodeToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)
	AND tREASON_CODES.ReasonCode <> 'TERDEC-Declined Employ'
	AND tEMPLOYMENT_CODES.EmploymentCode = 'Terminated' 
	AND tSY_PERSON_CODES.PersonCode <> 'None' 
	AND tPOSITION_CODES.PositionCodeDescription NOT LIKE '%PRN%'
	AND tPOSITION_CODES.PositionCodeUserDefined2 NOT LIKE '%PRN%'
	AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%Subs%'
	AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%KSMD%' 
	AND tEMPLOYMENT_STATUS.TerminationDate BETWEEN '1-1-2008' and '12-31-2008'
	)
	OR (
		M_CostCenter_6_Curr.GLSegment IS NULL
		)
ORDER BY
	tPERSONS.LastName



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2009-03-19 : 16:41:09
Sorry I probably didn't explain this right. I want all the smaller records to be present even if they have NO personnel that have been terminated during that time. I have added some data so you see what I am looking at. If you look at it you will see that NOT all GLSegments have Terminated employees. But when I run the report I need to include those GLSegments as part of the report eventhough they do not have terminated employees. For Example, I want to see glsegment 002 with it's name on the report with a FTEBud_Position amount in the proper column.

TABLE #1
GL_CC OrgDescription TermType LastName FirstName MiddleName Gender EthnicGroup ReasonCode OrigHireDt TermDt LastHireDt YrsofSrvcOrgHire FTEBudget FTEBudHrs FTEHrsPerWk PosFTEHrs <90days <1Yr <3Yr >3Yr Voluntary Involuntary Uncontrollable Minority Non-Minority Attendance Conduct LostSecClear UnsatPerf DislikeWork DissatSuper JobAband Salary Relo Personal NoRtnLOA NoRsnGvn PosElim UnavailWork PositionCode EmplyCode PostionCodeDesc FTEBud_Position

001 M_6_Assistant_001 Voluntary Mickey Mouse Female White TERTMP-End Temporary Emp 5/10/2005 4/29/2008 6/5/2007 2.970568 2 80 40 40 0 0 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 Office Assistant Terminated Office Assistant-001 15
003 M_6_Marketing_003 Voluntary Davis Tonoy Male White TERREF - Refuse Transfer 10/1/2005 8/2/2008 10/1/2005 2.836413 1 40 40 40 0 0 1 0 1 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 Territory Manager Terminated Dir. of Business Development-003 4
004 M_6_Human Resources_004 Voluntary Richardson Natasha Female White TERREL-Relocation 3/19/2008 12/6/2008 3/19/2008 0.717316 1 40 40 40 0 1 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 HR Assistant Terminated HR Assistant-004 7
006 M_6_Payroll_006 Voluntary Williams Brian Male White TEROTP-Resignation-Other 3/29/2004 2/21/2008 3/29/2004 3.898699 1 40 40 40 0 0 0 1 1 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 Financial Manager Terminated Financial Manager-006 13
007 M_6_Information Technology_007 Voluntary DeGeorge Anthony Male White TEROTP-Resignation-Other 4/30/2007 8/30/2008 4/30/2007 1.336071 1 40 40 40 0 0 1 0 1 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 Project Mgr./Developer Terminated Project Mgr./Developer-007 12
009 M_6_Risk Management_009 Voluntary Shelby Jessica Female White TERRES-Resignation 5/19/2008 6/28/2008 5/19/2008 0.109514 1 40 40 40 1 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 Paralegal Terminated Paralegal-009 5
010 M_6_Montgomery Co Jail_010 Voluntary Bailey Kimberly Female White TERREL-Relocation 2/22/2007 3/9/2008 2/22/2007 1.043121 6 240 36 40 0 0 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 LPN Terminated LPN-010 13.2
TABLE #2

FTEBud_Position GLSeg
15 001
6 002
4 003
7 004
4 005
13 006
12 007
2 008
5 009
13.2 010
2 011

As you can see on the two column table I have a FTEBud_Position for 002; 005; and 008 but no one is in those GL's as terminated. I want all FTEBud_Positions to show on the combined report. I hope that helps.


werseyjersey
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-03-20 : 05:35:33
Does this work?


SELECT
	query1.*
 	, posBud.[FTEBudget_Position]
FROM
	(
	SELECT
		SUM(tPOSITION_CODES.FTEBudget) AS [FTEBudget_Position]
		, M_CostCenter_6_Curr.GLSegment AS [GLSegment]
	FROM
		tPOSITION_CODES
		INNER JOIN M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo
	GROUP BY
		M_CostCenter_6_Curr.GLSegment
	)
	posBud

	LEFT JOIN (
		SELECT DISTINCT 
			M_CostCenter_6_Curr.GLSegment AS GL_CC
			, M_CostCenter_6_Curr.OrganizationDescription
			, tSY_PERSON_CODES.PersonCode AS TerminationType
			, tPERSONS.LastName
			, tPERSONS.FirstName
			, tPERSONS.MiddleName
			, tSY_PERSON_CODES_1.PersonCode AS Gender
			, tETHNIC_CODES.EthnicCode
			, tREASON_CODES.ReasonCode
			, CONVERT(varchar(10),tEMPLOYMENT_STATUS.OriginalHireDate, 101) AS OrigHireDt
			, CONVERT(varchar(10), tEMPLOYMENT_STATUS.TerminationDate, 101) AS TermDate
			, CONVERT(varchar(10), tEMPLOYMENT_STATUS.LastHireDate, 101) AS LastHireDt
			, DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25 AS YrsofSrvcOrgHire
			, tPOSITION_CODES.FTEBudget
			, tPOSITION_CODES.FTEBudgetHours
			, tPERSON_POSITIONS.FTEHoursPerWeek
			, tPOSITION_CODES.FTEHoursPerWeek AS PosFTEHrs
			, (CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.00000000 AND 0.24640657 THEN 1 ELSE 0 END) AS [<90 days]
			, (CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 0.24640658 AND 0.99999999 THEN 1 ELSE 0 END) AS [<1 year]
			, CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) BETWEEN 1.00000000 AND 2.99999999 THEN 1 ELSE 0 END) AS [<3 year]
			, (CASE WHEN (DATEDIFF(y, tEMPLOYMENT_STATUS.OriginalHireDate, tEMPLOYMENT_STATUS.TerminationDate) / 365.25) > 3.00000000 THEN 1 ELSE 0 END) AS [>3 year]
			, (CASE WHEN tSY_PERSON_CODES.PersonCode IN ('Voluntary') THEN 1 ELSE 0 END) AS Voluntary
			, (CASE WHEN tSY_PERSON_CODES.PersonCode IN 'Involuntary', 'Uncontrollable') THEN 1 ELSE 0 END) AS Involuntary
			, (CASE WHEN tETHNIC_CODES.EthnicCode IN ('Ameri Indn/Alask Nativ', 'Asian', 'Black or African Ameri', 'Hispanic or Latino', 'Nativ Hawii/Othr PacIsldr') THEN 1 ELSE 0 END) AS Minority
			, (CASE WHEN tETHNIC_CODES.EthnicCode IN ('White') THEN 1 ELSE 0 END) AS NonMinority, (CASE WHEN (TermReasonCodeCategory = 1) THEN 1 ELSE 0 END) AS Attendance
			, (CASE WHEN (TermReasonCodeCategory = 2) THEN 1 ELSE 0 END) AS [Conduct/Violation of Rules]
			, (CASE WHEN (TermReasonCodeCategory = 3) THEN 1 ELSE 0 END) AS [Lost Security Clearance]
			, (CASE WHEN (TermReasonCodeCategory = 4) THEN 1 ELSE 0 END) AS [Unsatisfactory Performance]
			, (CASE WHEN (TermReasonCodeCategory = 5) THEN 1 ELSE 0 END) AS [Dislike type of work/Job] 
			, (CASE WHEN (TermReasonCodeCategory = 6) THEN 1 ELSE 0 END) AS [Dissatified w/Supervisor/Mgmt] 
			, (CASE WHEN (TermReasonCodeCategory = 7) THEN 1 ELSE 0 END) AS [Job Abandonment/No Call No Show]
			, (CASE WHEN (TermReasonCodeCategory = 8) THEN 1 ELSE 0 END) AS [Salary/Career Advancement]
			, (CASE WHEN (TermReasonCodeCategory = 9) THEN 1 ELSE 0 END) AS [Relocation/Retire/School]
			, (CASE WHEN (TermReasonCodeCategory = 10) THEN 1 ELSE 0 END) AS [Personal/Other Emp]
			, (CASE WHEN (TermReasonCodeCategory = 11) THEN 1 ELSE 0 END) AS [No Return for LOA]
			, (CASE WHEN (TermReasonCodeCategory = 12) THEN 1 ELSE 0 END) AS [No Reason Given/Resignation]
			, (CASE WHEN (TermReasonCodeCategory = 13) THEN 1 ELSE 0 END) AS [Position Elim/Company Reason]
			, (CASE WHEN (TermReasonCodeCategory = 14) THEN 1 ELSE 0 END) AS [Unavailable to Work]
			, tPOSITION_CODES.PositionCodeUserDefined2
			, tPERSON_POSITIONS.PositionToEffectDate
			, tPERSON_POSITIONS.PositionFromEffectDate
			, tPERSONS.PersonToEffectDate
			, tPERSONS.PersonFromEffectDate
			, tPOSITION_CODES.PositionCodeToEffectDate
			, tPOSITION_CODES.PositionCodeFromEffectDate
			, tEMPLOYMENT_CODES.EmploymentCode
			, tEMPLOYMENT_STATUS.TerminationDate
			, tPOSITION_CODES.PositionCodeDescription
		FROM
			tEMPLOYMENT_CODES
			INNER JOIN tEMPLOYMENT_STATUS ON tEMPLOYMENT_CODES.EmploymentCodeIdNo = tEMPLOYMENT_STATUS.EmploymentStatus
			INNER JOIN tSY_PERSON_CODES ON tEMPLOYMENT_STATUS.TerminationTypeIdNo = tSY_PERSON_CODES.PersonCodeIdNo
			INNER JOIN tREASON_CODES ON tEMPLOYMENT_STATUS.EmploymentStatusReasonIdNo = tREASON_CODES.ReasonCodeIdNo
			INNER JOIN tPERSONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSONS.PersonIdNo
			INNER JOIN tETHNIC_CODES ON tPERSONS.EthnicCodeIdNo = tETHNIC_CODES.EthnicCodeIdNo
			INNER JOIN tSY_PERSON_CODES AS tSY_PERSON_CODES_1 ON tPERSONS.GenderIdNo = tSY_PERSON_CODES_1.PersonCodeIdNo
			INNER JOIN tPERSON_POSITIONS ON tEMPLOYMENT_STATUS.PersonIdNo = tPERSON_POSITIONS.PersonIdNo
			INNER JOIN tPOSITION_CODES ON tPERSON_POSITIONS.PositionIdNo = tPOSITION_CODES.PositionIdNo
			INNER JOIN M_CostCenter_6_Curr ON tPOSITION_CODES.PositionIdNo = M_CostCenter_6_Curr.PositionIdNo
			INNER JOIN CCSCustom_TermReasons ON tREASON_CODES.ReasonCodeIdNo = CCSCustom_TermReasons.ReasonCodeIdNo
		WHERE
			tPERSON_POSITIONS.PositionToEffectDate = '3000-01-01 00:00:00'
			AND tPERSONS.PersonToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102) 
			AND tPOSITION_CODES.PositionCodeToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)
			AND tREASON_CODES.ReasonCode <> 'TERDEC-Declined Employ'
			AND tEMPLOYMENT_CODES.EmploymentCode = 'Terminated' 
			AND tSY_PERSON_CODES.PersonCode <> 'None' 
			AND tPOSITION_CODES.PositionCodeDescription NOT LIKE '%PRN%'
			AND tPOSITION_CODES.PositionCodeUserDefined2 NOT LIKE '%PRN%'
			AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%Subs%'
			AND M_CostCenter_6_Curr.OrganizationDescription NOT LIKE '%KSMD%' 
			AND tEMPLOYMENT_STATUS.TerminationDate BETWEEN '1-1-2008' and '12-31-2008'
		)
		query1 ON query1.[GL_CC] = posBud.[GLSegment]
ORDER BY
	query1.[LastName]



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2009-03-23 : 12:59:36
Yes this works but it doesn't tell me who the NULL values are for (i.e. it is for GLSegment 002 or 009) I added the GLSegment to the first query and it appears to give me what I need. I'll do some more testing and see if that is truly it. Thanks for all your help!

werseyjersey
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site