Effective Date

[mercadi]

I created a report showing employees that have Dental.(Benefit_Enrollment_Curr.BenefitPlanCode) We just had open enrollment and the employees that signed up for Dental or made changes will go into effect on 04/01/2006. When I run the report the new changes do not show because they are in the system to start at 04/01/2006. How can I show those people in my report that have this effective date.

I use Crystal 9.

 

[synapsevampire]

they are in the system to start at 04/01/2006" doesn't describe anything meaningful, post the data structure.

Perhaps there's a date field called effective date you can key off of?

Askign people to describe your data to you probably isn't going to prove fruitful, so in general post:

Database/connectivity
Example data
Expected output

-k

 

[mercadi]

Not sure I'm giving you the right info. Hope this is more helpful.

I created the report in Crystal TEXDB4 database and the data is pulling from our HRMS system. The effective date for the Dental changes to take place is set in HRMS for 04/01/06. In my Benefit Enrollment table that I used to pull fields from, it does show effective date, but when I go through SelectExpert and do NEW so I can make effective date 04/01/06, (this date 04/01/06) is not showing. Right now all the data that is showing in the report are employees that already had DENTAL. I need those that go into effect 04/01/06. Not sure if this is what your needing.

Example data

Employee_Curr.LastName
Employee_Curr.FirstName
Employee_Curr.Address
Employee_Curr.Person Tax ID
Benefit Enrollment_Curr.Benefit Plan Code
Benefit Enrollment_Curr.Election Option

 

[synapsevampire]

That's not example data, those are a list of the fields, show what's in them, and what you want the output to be.

Since none of the fields shown are dates, you haven't posted sufficient information anyway.

-k

 

[Turkbear]

Hi,
Does the table you are using get updated when a person enrolls for coverage or only after the coverage is actually effective?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mercadi]

I believe the table updates after the coverage is actually effective but the insurance carrier is wanting to see all the new changes now and I'm not sure how to pull those folks out.

Sorry Synapsevampire. I'm not sure what your asking when you say "show what's in them". Here is my SQL Query to show what is going on in report if that will help.

SELECT "TMC_EmployeePos_Stat_Curr"."LastName", "TMC_EmployeePos_Stat_Curr"."FirstName", "TMC_EmployeePos_Stat_Curr"."MiddleName", "TMC_Persons_PrimaryAddresses_curr"."PersonAddress1", "TMC_Persons_PrimaryAddresses_curr"."PersonAddress2", "TMC_Persons_PrimaryAddresses_curr"."PersonAddressCity", "TMC_Persons_PrimaryAddresses_curr"."PersonAddressStateProvince", "TMC_Persons_PrimaryAddresses_curr"."PersonAddressPostalCode", "PERSON_PHONES"."PersonPhoneNo", "PERSON_PHONES"."PersonPhonePrimaryInd", "TMC_EmployeePos_Stat_Curr"."Gender", "TMC_EmployeePos_Stat_Curr"."BirthDate", "TMC_EmployeePos_Stat_Curr"."PersonTaxIdNo", "TMC_EmployeePos_Stat_Curr"."OriginalHireDate", "TMC_Benefit_Enrollments_Curr"."BenefitPlanCode", "TMC_Benefit_Enrollments_Curr"."ElectionOption"
FROM (("HRMS"."dbo"."TMC_EmployeePos_Stat_Curr" "TMC_EmployeePos_Stat_Curr" INNER JOIN "HRMS"."dbo"."TMC_Benefit_Enrollments_Curr" "TMC_Benefit_Enrollments_Curr" ON "TMC_EmployeePos_Stat_Curr"."PersonIdNo"="TMC_Benefit_Enrollments_Curr"."PersonIdNo") INNER JOIN "HRMS"."dbo"."TMC_Persons_PrimaryAddresses_curr" "TMC_Persons_PrimaryAddresses_curr" ON "TMC_EmployeePos_Stat_Curr"."PersonIdNo"="TMC_Persons_PrimaryAddresses_curr"."PersonIdNo") INNER JOIN "HRMS"."dbo"."PERSON_PHONES" "PERSON_PHONES" ON "TMC_Persons_PrimaryAddresses_curr"."PersonIdNo"="PERSON_PHONES"."PersonIdNo"
WHERE "PERSON_PHONES"."PersonPhonePrimaryInd"=1 AND ("TMC_Benefit_Enrollments_Curr"."BenefitPlanCode"=N'Dental High' OR "TMC_Benefit_Enrollments_Curr"."BenefitPlanCode"=N'Dental Low')

My output, I want to look like this.

Last Name: Test
First Name: Ima
Address:Memorial Drive
City:XX
State:XX
Zip:xx
Phone:xxx-xxx-xxxx
Person Tax ID: xxx-xx-xxxx
Benifit Coverage: Dental High or Dental Low
Election Option: Employee + Family

 

[mercadi]

This report works great, it just doesn't pull in the new data that was placed in HRMS with effective date 4/01/06. Still trying to find a way to pull those employees as well.

 

[kskid]

Bottom line.... the effective date has to reside somewhere on the employee or benefit table, which you neglected to say where.

-lw

 

[Turkbear]

Hi,
Given that:

I believe the table updates after the coverage is actually effective but the insurance carrier is wanting to see all the new changes now and I'm not sure how to pull those folks out.


You will not be able to get the data from that table.
Ask the HR folks where they store the 'pending' stuff, if anywhere.





To Paraphrase:"The Help you get is proportional to the Help you give.."