How to Add a row to a report - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How to Add a row to a report

Author

Topic

werseyjersey
Starting Member

47 Posts

Posted - 2008-08-01 : 12:44:43

Currently I have a report that produces all the positions within our business that have someone within that position (Position Control). Within that report, I have also been able to produce a row for each position that is vacant and this works great for the current need. However, we would like to add some additional code that keeps the person in the position and then if there is a Vacancy (based on the budgeted FTE) it adds a line to the position showing "no person in the position".

I am sure this does not make sense when you read it so let me know and I will sent you what you might need. Here is the current code I have:
SELECT dbo.POSITION_CODES.PositionIdNo,
CASE
WHEN PCE.LastName Is Null THEN 'No Employees in Position'
ELSE PCE.LastName
END as LastName,
CASE
WHEN PCE.FirstName Is Null THEN ''
ELSE PCE.FirstName
END as FirstName,
CASE
WHEN PCE.MiddleName Is Null THEN ''
ELSE PCE.MiddleName
END as MiddleName,
CASE
WHEN PCE.EMPBudget Is Null THEN '0'
ELSE PCE.EmpBudget
END as EmpBudget,
CASE
WHEN PCE.EMPFTE Is Null THEN '0'
ELSE PCE.EMPFTE
END as EMPFTE,
CASE
WHEN PCE.LastHireDate Is Null THEN ''
ELSE PCE.LastHireDate
END as LastHireDate,
CASE
WHEN PCE.SeniorityDate Is Null THEN ''
ELSE PCE.SeniorityDate
END as SeniorityDate,
CASE
WHEN PCE.PayRate Is Null THEN '0'
ELSE PCE.PayRate
END as PayRate,
CASE
WHEN PCE.PayRateFrequencyCode Is Null THEN ''
ELSE PCE.PayRateFrequencyCode
END as PayRateFrequencyCode,
CASE
WHEN PCE.AnnualSalary Is Null THEN '0'
ELSE PCE.AnnualSalary
END as AnnualSalary,
CASE
WHEN PCE.HourlyAmount Is Null THEN '0'
ELSE PCE.HourlyAmount
END as HourlyAmount,
CASE
WHEN PCE.EmployeeStatus Is Null THEN ''
ELSE PCE.EmployeeStatus
END as EmployeeStatus,
CASE
WHEN PCE.EmploymentCode Is Null THEN ''
ELSE PCE.EmploymentCode
END as EmploymentCode,
CASE
WHEN PCE.SalariedHourlyCode Is Null THEN ''
ELSE PCE.SalariedHourlyCode
END as SalariedHourlyCode,
CASE
WHEN PCE.EmpNo Is Null THEN ''
ELSE PCE.EmpNo
END as EmpNo,
CASE
WHEN PCE.PersonIdNo Is Null THEN ''
ELSE PCE.PersonIdNo
END as PersonIdNo, dbo.POSITION_CODES.PositionCode, dbo.POSITION_CODES.PositionCodeUserDefined2 AS ClientTitle,
dbo.POSITION_CODES.PositionCodeDescription AS CCSTitle, dbo.M_BusUnit_5_Curr.OrganizationDescription AS BUDescription,
dbo.M_CostCenter_6_Curr.OrganizationDescription AS CCDescription, dbo.M_CostCenter_6_Curr.GLSegment AS GL_CC,
dbo.D_JobCode_5_Curr.GLSegment AS GL_Dept, dbo.M_CostCenter_6_Curr.GLSegment + dbo.D_JobCode_5_Curr.GLSegment AS Department,
dbo.POSITION_CODES.FTEBudget, dbo.POSITION_CODES.FTEBudgetHours, dbo.POSITION_CODES.FTEHoursPerWeek,
dbo.M_BusUnit_5_Curr.OrganizationCode, dbo.CCSCustom_GLCodes.HRPayrollDescription,PosCount.EmpCount,
(dbo.POSITION_CODES.FTEBudget/PosCount.EmpCount) As FTEBudPR, (dbo.POSITION_CODES.FTEBudgetHours/PosCount.EmpCount)As FTEBudHrsPR,
dbo.M_BusUnit_5_Curr.OrganizationCode As BUID, dbo.M_CostCenter_6_Curr.OrganizationCode As CCID

FROM dbo.M_BusUnit_5_Curr INNER JOIN
dbo.POSITION_CODES ON dbo.M_BusUnit_5_Curr.PositionIdNo = dbo.POSITION_CODES.PositionIdNo INNER JOIN
dbo.M_CostCenter_6_Curr ON dbo.POSITION_CODES.PositionIdNo = dbo.M_CostCenter_6_Curr.PositionIdNo INNER JOIN
dbo.D_JobCode_5_Curr ON dbo.POSITION_CODES.PositionIdNo = dbo.D_JobCode_5_Curr.PositionIdNo INNER JOIN
dbo.CCSCustom_GLCodes ON dbo.D_JobCode_5_Curr.GLSegment = dbo.CCSCustom_GLCodes.GLCode LEFT OUTER JOIN
(
SELECT dbo.PERSON_STATUS_POSITION_PAY.FirstName, dbo.PERSON_STATUS_POSITION_PAY.MiddleName,
dbo.PERSON_STATUS_POSITION_PAY.LastName, dbo.PERSON_STATUS_POSITION_PAY.LastHireDate,
dbo.PERSON_STATUS_POSITION_PAY.SeniorityDate, dbo.PERSON_STATUS_POSITION_PAY.PayRate,
dbo.PERSON_STATUS_POSITION_PAY.PayRateFrequencyCode, dbo.PERSON_STATUS_POSITION_PAY.StandardUnitsPerWeek AS EMPBudget,
dbo.PERSON_STATUS_POSITION_PAY.AnnualSalary, dbo.PERSON_STATUS_POSITION_PAY.HourlyAmount,
dbo.PERSON_STATUS_POSITION_PAY.EmployeeStatus, dbo.tEMPLOYMENT_CODES.EmploymentCode,
dbo.PERSON_STATUS_POSITION_PAY.SalariedHourlyCode, dbo.PERSON_STATUS_POSITION_PAY.EmpNo,
dbo.PERSON_STATUS_POSITION_PAY.PersonIdNo, dbo.PERSON_STATUS_POSITION_PAY.StandardUnitsPerWeek / 40 AS EMPFTE,
dbo.PERSON_STATUS_POSITION_PAY.PositionIdNo
FROM dbo.tEMPLOYMENT_CODES INNER JOIN
dbo.PERSON_STATUS_POSITION_PAY ON
dbo.tEMPLOYMENT_CODES.EmploymentCodeIdNo = dbo.PERSON_STATUS_POSITION_PAY.EmploymentStatus
WHERE (dbo.PERSON_STATUS_POSITION_PAY.PersonFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.EmploymentStatusFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.pesPositionOrgFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PositionFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PayStatusFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PayStatusToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PositionToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.pesPositionOrgToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.EmploymentStatusToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PersonToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.EmployeeStatus <> N'Terminated')
) As PCE ON dbo.POSITION_CODES.PositionIdNo = PCE.PositionIdNo LEFT OUTER JOIN
(
SELECT PositionIdNo, COUNT(EmpNo) AS EmpCount
FROM dbo.PERSON_STATUS_POSITION_PAY
WHERE (dbo.PERSON_STATUS_POSITION_PAY.PersonFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.EmploymentStatusFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.pesPositionOrgFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PositionFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PayStatusFromEffectDate <= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PayStatusToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PositionToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.pesPositionOrgToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.EmploymentStatusToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.PersonToEffectDate >= GETDATE()) AND
(dbo.PERSON_STATUS_POSITION_PAY.EmployeeStatus <> N'Terminated')
GROUP BY PositionIdNo
) As PosCount On dbo.POSITION_CODES.PositionIdNo=PosCount.PositionIdNo
WHERE (dbo.POSITION_CODES.PositionCodeFromEffectDate <= GETDATE()) AND
(dbo.POSITION_CODES.PositionCodeToEffectDate >= GETDATE())AND
(dbo.M_CostCenter_6_Curr.GLSegment <> N'000')
-- AND ( PCE.LastName Is Null)
AND ((LTRIM(RTRIM(dbo.POSITION_CODES.PositionCode)) LIKE '%-P') OR (dbo.POSITION_CODES.FTEBudget > 0) OR (EmpCount > 0) )
ORDER BY PCE.LastName

END

Thanks for any help you might provide.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 12:50:07

i think you just need to use left join here. Take the table containing position information as base table and left join with person detail table. if a match is found then a person exist for position so retrieve his details else no person so use coalesce and show string "no person in the position". If you need more help post your table structures with some sample data.

werseyjersey
Starting Member

47 Posts

Posted - 2008-08-01 : 13:10:37

Hope this helps:
PositionIdNo LastName FirstName MiddleName EmpBudget EMPFTE LastHireDate SeniorityDate PayRateFrequencyCode EmployeeStatus EmploymentCode SalariedHourlyCode PositionCode ClientTitle Title FTEBudget FTEBudgetHours FTEHoursPerWeek EmpCount FTEBudPR FTEBudHrsPR
302 Mouse Mickey L. 40 1 00:00.0 00:00.0 Hourly Active Regular Full Time Hourly MS1200-850-D Medical Records Medical Records-850 2 80 40 1 2 80
Let me know if you need anything else.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:34:42

quote:
Originally posted by werseyjersey

Hope this helps:
PositionIdNo LastName FirstName MiddleName EmpBudget EMPFTE LastHireDate SeniorityDate PayRateFrequencyCode EmployeeStatus EmploymentCode SalariedHourlyCode PositionCode ClientTitle Title FTEBudget FTEBudgetHours FTEHoursPerWeek EmpCount FTEBudPR FTEBudHrsPR
302 Mouse Mickey L. 40 1 00:00.0 00:00.0 Hourly Active Regular Full Time Hourly MS1200-850-D Medical Records Medical Records-850 2 80 40 1 2 80
Let me know if you need anything else.

werseyjersey

this is table with person deatils. but where's the table with position info?

werseyjersey
Starting Member

47 Posts

Posted - 2008-08-01 : 13:36:13

This stored procedure pulls both tables into this table and the MS1200-850-D is the position. Does that help?

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 13:38:29

quote:
Originally posted by werseyjersey

This stored procedure pulls both tables into this table and the MS1200-850-D is the position. Does that help?

werseyjersey

not much. if you want the position details without having a person assigned for it you need to take it from the position information table. the one you posted is person detail table which has predominantly person details who holds the various positions. those positions without person wont be present in this.

werseyjersey
Starting Member

47 Posts

Posted - 2008-08-01 : 13:54:15

Lets try this again then
Position Code Table
PositionIdNo Position Code JobCodeIdNo PositionCodeDescription
302 302 MS1200-850-D 93 Medical Records-850 2080 5 -10001 NULL 850606 Medical Records NULL NULL FALSE 1/2/1900 0:00 1/1/3000 0:00 en-us TRUE 2 40 2 80 <Binary data> THis does not include headings for all columns just the ones I thought you might need to know. Thanks for all the help.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 05:15:03

quote:
Originally posted by werseyjersey

Lets try this again then
Position Code Table
PositionIdNo Position Code JobCodeIdNo PositionCodeDescription
302 302 MS1200-850-D 93 Medical Records-850 2080 5 -10001 NULL 850606 Medical Records NULL NULL FALSE 1/2/1900 0:00 1/1/3000 0:00 en-us TRUE 2 40 2 80 <Binary data> THis does not include headings for all columns just the ones I thought you might need to know. Thanks for all the help.

werseyjersey

select otherfields,COALESCE(persontablefiled,'No Employees in Position'
FROM [Position Code] pc
LEFT JOIN PersonPositiontable ppt
ON ppt.PositionIdNo=pc.PositionIdNo
...

PersonPositiontable is the table you posted in earlier post on 08/01/2008 : 13:10:37 (i dunno the name neither have you specified it). just take left join with that. for those that have matching record you will get person details and for others they will return NULL. then just use coalesce as shown

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources