I am trying to get my stored procedure to work in SQL Reporting Services. It seems to pull just fine and dandy but I get some people with duplicate rows. Additionally, I want to sum by a particular EarnCode say (SIC) or (VAC). It doesn't seem to be doing that part of the query. I have looked and looked and I am not seeing it. Can someone with fresh eyes take a look and tell me what I am doing wrong. I am fairly new to SQL Reporting Services so maybe I didn't something stupid. Thanks.Here is the entire stored procedure:SELECT PER_1.EmpNo, JOB.PositionIdNo, JOB.PositionCodeSeqNo, JOB.PositionCode, JOB.GL_Job AS Job, JOB.CCS_Title AS JobCCSTitle, JOB.Client_Title AS JobClientTitle, JOB.Kronos_Dept, PER_1.LastName, PER_1.FirstName, JOB.FTEBudgetHours, JOB.FTEHoursPerWeek, PER_1.ExemptStatusInd, JOB.FTEBudget AS JOBFTEBudget, PER_1.FTEHoursPerWeek AS PER1FTEHoursPerWeek, CAT1.SumHrs, CAT1.Expr1FROM (SELECT DISTINCT V_Kr_PersonPosition.PositionIdNo, V_Kr_JobPosition.PositionCodeSeqNo, V_Kr_JobPosition.GL_Job, V_Kr_JobPosition.Client_Title, V_Kr_JobPosition.CCS_Title, V_Kr_JobPosition.FTEBudget, ADP_PayrollMaster.PayrollStart, ADP_PayrollMaster.PayrollEnd FROM V_Kr_JobPosition LEFT OUTER JOIN ADP_PayData INNER JOIN ADP_PayrollMaster ON ADP_PayData.PayrollmasterID = ADP_PayrollMaster.PayrollMasterID INNER JOIN V_Kr_PersonPosition ON ADP_PayData.PersonID = V_Kr_PersonPosition.EmpNo ON V_Kr_JobPosition.PositionIdNo = V_Kr_PersonPosition.PositionIdNo WHERE (@PED BETWEEN V_Kr_PersonPosition.PersonFromEffectDate AND V_Kr_PersonPosition.PersonToEffectDate) AND (@PED BETWEEN V_Kr_PersonPosition.PositionFromEffectDate AND V_Kr_PersonPosition.PositionToEffectDate) AND (ADP_PayrollMaster.PayrollStart = CONVERT(DATETIME, '2008-07-05 00:00:00', 102)) AND (ADP_PayrollMaster.PayrollEnd = CONVERT(DATETIME, '2008-07-19 00:00:00', 102)) AND (V_Kr_JobPosition.FTEBudget IS NOT NULL) GROUP BY V_Kr_PersonPosition.PersonIdNo, V_Kr_PersonPosition.PositionIdNo, V_Kr_JobPosition.GL_Job, V_Kr_JobPosition.Client_Title, V_Kr_JobPosition.CCS_Title, V_Kr_JobPosition.FTEBudget, ADP_PayrollMaster.PayrollStart, ADP_PayrollMaster.PayrollEnd, V_Kr_JobPosition.PositionCodeSeqNo) AS VPP RIGHT OUTER JOIN (SELECT DISTINCT TJC.JobCode, TJC.JobCodeDescription, TPC.PositionIdNo, TPC.PositionCodeSeqNo, TPC.PositionCode, TPC.PositionCodeDescription AS CCS_Title, TPC.PositionCodeUserDefined1 AS Kronos_Dept, TPC.PositionCodeUserDefined2 AS Client_Title, TPC.FTEBudget, TPC.FTEBudgetHours, TPC.FTEHoursPerWeek, ORGJ.GLSegment AS GL_Job, ORGB.GLSegment AS GL_BU, ORGC.GLSegment AS GL_CC, ORGP.GLSegment AS GL_PG FROM Staging.dbo.tPOSITION_ORGS AS TPO_3 INNER JOIN Staging.dbo.tORGANIZATIONS AS ORGP ON TPO_3.OrgCodeIdNo = ORGP.OrgCodeIdNo INNER JOIN Staging.dbo.tORGANIZATIONS AS ORGC INNER JOIN Staging.dbo.tPOSITION_ORGS AS TPO_2 ON ORGC.OrgCodeIdNo = TPO_2.OrgCodeIdNo INNER JOIN Staging.dbo.tORGANIZATIONS AS ORGJ INNER JOIN Staging.dbo.tPOSITION_ORGS AS TPO INNER JOIN Staging.dbo.tPOSITION_CODES AS TPC ON TPO.PositionIdNo = TPC.PositionIdNo INNER JOIN Staging.dbo.tJOB_CODES AS TJC ON TPC.JobCodeIdNo = TJC.JobCodeIdNo ON ORGJ.OrgCodeIdNo = TPO.OrgCodeIdNo INNER JOIN Staging.dbo.tPOSITION_ORGS AS TPO_1 INNER JOIN Staging.dbo.tORGANIZATIONS AS ORGB ON TPO_1.OrgCodeIdNo = ORGB.OrgCodeIdNo ON TPC.PositionIdNo = TPO_1.PositionIdNo ON TPO_2.PositionIdNo = TPC.PositionIdNo ON TPO_3.PositionIdNo = TPC.PositionIdNo WHERE (LTRIM(RTRIM(ORGC.GLSegment)) = @Location) AND (ORGC.OrganizationTypeIdNo = 3) AND (ORGB.OrganizationTypeIdNo = 10) AND (ORGJ.OrganizationTypeIdNo = 9) AND (ORGP.OrganizationTypeIdNo = 11) AND (@PED BETWEEN ORGJ.OrganizationFromEffectDate AND ORGJ.OrganizationToEffectDate) AND (@PED BETWEEN ORGB.OrganizationFromEffectDate AND ORGB.OrganizationToEffectDate) AND (@PED BETWEEN ORGC.OrganizationFromEffectDate AND ORGC.OrganizationToEffectDate) AND (@PED BETWEEN ORGP.OrganizationFromEffectDate AND ORGP.OrganizationToEffectDate) AND (@PED BETWEEN TPO.PositionOrgFromEffectDate AND TPO.PositionOrgToEffectDate) AND (@PED BETWEEN TPO_1.PositionOrgFromEffectDate AND TPO_1.PositionOrgToEffectDate) AND (@PED BETWEEN TPO_2.PositionOrgFromEffectDate AND TPO_2.PositionOrgToEffectDate) AND (@PED BETWEEN TPO_3.PositionOrgFromEffectDate AND TPO_3.PositionOrgToEffectDate) AND (@PED BETWEEN TPC.PositionCodeFromEffectDate AND TPC.PositionCodeToEffectDate) AND (@PED BETWEEN TJC.JobCodeFromEffectDate AND TJC.JobCodeToEffectDate)) AS JOB ON VPP.PositionIdNo = JOB.PositionIdNo AND VPP.PositionCodeSeqNo = JOB.PositionCodeSeqNo LEFT OUTER JOIN (SELECT DISTINCT ES.EmpNo, PRP.PositionIdNo, PER.PersonIdNo, PER.FirstName, PER.LastName, PER.BirthDate, PRP.ExemptStatusInd, PRP.FTEHoursPerWeek, ES.EmploymentStatus, ES.FullTimeInd, ES.LastHireDate, ES.OriginalHireDate, ES.SeniorityDate, ES.TerminationDate FROM Staging.dbo.tPERSONS AS PER INNER JOIN Staging.dbo.tEMPLOYMENT_STATUS AS ES ON PER.PersonIdNo = ES.PersonIdNo INNER JOIN Staging.dbo.tPERSON_POSITIONS AS PRP ON PER.PersonIdNo = PRP.PersonIdNo INNER JOIN Staging.dbo.tPERSON_ADDRESSES AS PRA ON PER.PersonIdNo = PRA.PersonIdNo INNER JOIN Staging.dbo.tLOCAL_CODES AS LC ON PRA.PersonAddressStateProvinceIdNo = LC.LocalCodeIdNo WHERE (@PED BETWEEN PER.PersonFromEffectDate AND PER.PersonToEffectDate) AND (@PED BETWEEN PRA.PersonAddressFromEffectDate AND PRA.PersonAddressToEffectDate) AND (@PED BETWEEN ES.EmploymentStatusFromEffectDate AND ES.EmploymentStatusToEffectDate) AND (@PED BETWEEN PRP.PositionFromEffectDate AND PRP.PositionToEffectDate) AND (@PED BETWEEN LC.LocalCodeFromEffectDate AND LC.LocalCodeToEffectDate)) AS PER_1 ON PER_1.PositionIdNo = JOB.PositionIdNo LEFT OUTER JOIN (SELECT DISTINCT ADP_Hrs.HrsAmt AS SumHrs, ADP_Hrs.PersonID, V_Kr_PersonPosition_1.PositionIdNo, V_Kr_PersonPosition_1.PersonIdNo, V_Kr_JobPosition_1.PositionCode, V_Kr_JobPosition_1.PositionIdNo AS Expr1, V_Kr_JobPosition_1.PositionCodeSeqNo FROM V_Kr_JobPosition AS V_Kr_JobPosition_1 INNER JOIN V_Kr_PersonPosition AS V_Kr_PersonPosition_1 ON V_Kr_JobPosition_1.PositionIdNo = V_Kr_PersonPosition_1.PositionIdNo INNER JOIN ADP_Hrs ON V_Kr_PersonPosition_1.EmpNo = ADP_Hrs.PersonID INNER JOIN ADP_PayrollMaster AS ADP_PayrollMaster_1 INNER JOIN ADP_PayData AS ADP_PayData_1 ON ADP_PayrollMaster_1.PayrollMasterID = ADP_PayData_1.PayrollmasterID ON ADP_Hrs.PayDataID = ADP_PayData_1.PayDataID WHERE (ADP_Hrs.HrsCode = 'REG') GROUP BY V_Kr_PersonPosition_1.PersonFromEffectDate, V_Kr_PersonPosition_1.PersonToEffectDate, V_Kr_PersonPosition_1.EmploymentStatusFromEffectDate, V_Kr_JobPosition_1.PositionCodeSeqNo, V_Kr_PersonPosition_1.EmploymentStatusToEffectDate, V_Kr_PersonPosition_1.PositionFromEffectDate, V_Kr_PersonPosition_1.PositionToEffectDate, ADP_Hrs.HrsCode, V_Kr_PersonPosition_1.EmpNo, ADP_Hrs.HrsAmt, ADP_PayrollMaster_1.PayrollStart, ADP_PayrollMaster_1.PayrollEnd, ADP_Hrs.PersonID, V_Kr_PersonPosition_1.PositionIdNo, V_Kr_PersonPosition_1.PersonIdNo, V_Kr_JobPosition_1.PositionCode, V_Kr_JobPosition_1.PositionIdNo) AS CAT1 ON CAT1.PositionIdNo = VPP.PositionIdNo AND VPP.PositionIdNo = CAT1.PositionIdNo AND VPP.PositionCodeSeqNo = CAT1.PositionCodeSeqNo
Here is a snippet of data that I am seeing that doesn't seem to be pulling into the query correctly. EmpNO PositionIDNO PositionCodeSeqNO PositionCode Job JobTitle JobClient KronosDep LastName FirstName FTEBudHours FTEHrsPerWk ExemptStatusInd JobFTEBud FTEHrsPerWk SumHrs200216 512 513 RN1300-195-D 611 RN-195 RN 195611 Bailey Jones 80 40 FALSE 2 40 67.5200216 512 513 RN1300-195-D 611 RN-195 RN 195611 Bailey Jones 80 40 FALSE 2 40 72.75200216 512 513 RN1300-195-D 611 RN-195 RN 195611 Bailey Jones 80 40 FALSE 2 40 76.25200216 512 513 RN1300-195-D 611 RN-195 RN 195611 Bailey Jones 80 40 FALSE 2 40 80 @PED 7/19/2008 @Location 195 I am on a deadline of COB today so would appreciate a quick response. werseyjersey
Stored Procedure(s) Use in SQL Reporting Services
Posted - 2008-08-22 : 13:58:48
