Hi Susan,
One way to do this is to create a Table (e.g. zzLicenseTable) with the
following columns: SourceID, EmployeeID, QueryID, PeriodDate, Response. All
columns (except the PeriodDate) come from the PpEmployeeQueries table where
(I suspect) you are capturing the license data from. The PeriodDate column
would be a DateTime column and will house the date that the values were
updated into the table. If you plan to index the table, set the cluster
index to include the SourceID, EmployeeID, QueryID and PeriodDate columns;
however I would suspect that the overhead isn't necessary for the amount of
values you'll have in the table.
Once done, you would design a query to be used in a SQL Server DTS (or SSIS)
routine to read in the License rows from the PpEmployeeQueries and update
them (including the 'current date' as the PeriodDate) into the new table
zzLicenseTable. This DTS can be scheduled for an annual update or however
often you wish to update the table.
Querying the table for HR, you could add a sub select into the query
referencing the "TOP 1 Result from zzLicenseTable WHERE .. with the order
DESC in it to get the 'latest' value.
Hope this helps!
gmc
_____
Garry McAninch
Principal
Dimensions Analysis
Phone: 905-704-1356
Mobile: 905-941-1356
Fax : 905-688-2256
e-mail: <mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED]
web : <http://www.dimensionsanalysis.com/> www.dimensionsanalysis.com
This communication is intended solely for the addressee(s) and contains
information that is privileged, confidential and subject to copyright. Any
unauthorized use, copying, review or disclosure is prohibited. If received
in error, please notify us immediately by return e-mail.
_____
From: dr [mailto:[EMAIL PROTECTED]
Sent: Monday, July 30, 2007 5:36 PM
To: Garry McAninch
Subject: [SPAM] Creating new tables in the DR
Is it possible to load customer defined fields from the Payroll Personnel
module to create a table in the DR. We have a request from our HR department
to store the fields for Employee License number, License expiration date,
and License type in the DR for reporting. They would like to be able to keep
multiple instances in the DR (track from year to year).
We are a C/S 5.5 SR3 site.
Is this possible? Has anyone done this? I would like any advice you can
provide.
Thank you
Susan Wozniak
Manager/IS
Elmhurst Memorial Healthcare
630.993.5796
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
To subscribe or unsubscribe to the meditech-l, visit
http://mtusers.com/mailman/listinfo/meditech-l_mtusers.com
To check the status of the meditech-l, visit MTUsers.NET
For help, email [EMAIL PROTECTED]
Please visit and add information to the MTUsers WikiPedia at MTUsers.NET/mwiki
______________________________________
meditech-l mailing list
[email protected]
http://mtusers.com/mailman/listinfo/meditech-l_mtusers.com
