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.
| Author |
Topic |
|
jodie
Starting Member
4 Posts |
 Posted - 2005-01-19 : 15:02:35
|
| I have a problem...hope you guys can help.I am working with a sqr which is supposed to display employees in alphabetical order by name. Below is a shorter verison of my original code. What this does is when the employee name changes, it goes into procedure Do-Get-Earnings and prints the employee info on to the report. However, the problem with this is that when a woman gets married and changes her last name, her data is in the database with 2 different names and the same emplid. So, the report produced displayed her name twice..once with her maiden name, and second with her new last name bec it currently prints every new employee name. I thought the best solution for me to change the bolded text below so that it prints the info for a new employee only if it was a new employee id (emplid). Changes made to bolded text:--------------------- if $emplid <> &C.EMPLID DO Get-Earnings end-ifORDER BY C.COMPANY, E.DEPTID, C.EMPLID ----------------------- But when I did that , then it started printing the report sorted by emplids. However, I want the report to display sorted by name.. Is there a way to do this (i.e. print the info for a new emplid only but display sorted by name). BEGIN-SELECT C.EMPLID C.NAME !() on-break print=neC.COMPANY E.DEPTID ! () on-break print=neC.DEPTID if $first-time = 'N' if $CK_NAME <> rtrim(&C.NAME, ' ') DO Get-Earnings end-if if $dept1 <> &E.DEPTID DO New-Break1 end-if if $company <> &C.COMPANY DO Print-Comp-totals end-if end-if let $dept_cd = substr(&E.DEPTID,1,1) let $company = &C.COMPANY let $CK_name = rtrim(&C.NAME, ' ') let $ee_name = rtrim(&C.NAME, ' ')let $emplid = &C.EMPLID let $dept1 = &E.DEPTID FROM PS_PAY_CALENDAR PC, PS_PAY_CHECK C, PS_PAY_EARNINGS E WHERE PC.CHECK_DT BETWEEN $Begin_Yr_Dt AND $end_dt ORDER BY C.COMPANY, E.DEPTID, C.NAME |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-19 : 15:11:33
|
quote:
Originally posted by jodie
I have a problem...hope you guys can help.
We can try..quote:
I am working with a sqr
AAAAAAAAAHHHHHHHHHHHHHHHHHHSorryquote:
which is supposed to display employees in alphabetical order by name. Below is a shorter verison of my original code.
Well I guess this should go into "Other development tools" forum..no matter...quote:
What this does is when the employee name changes, it goes into procedure Do-Get-Earnings and prints the employee info on to the report. However, the problem with this is that when a woman gets married and changes her last name, her data is in the database with 2 different names and the same emplid. So, the report produced displayed her name twice..once with her maiden name, and second with her new last name bec it currently prints every new employee name. I thought the best solution for me to change the bolded text below so that it prints the info for a new employee only if it was a new employee id (emplid).
You need to resolve the fact that they keep historical information, and you need the most current info.Is there an add datetime column in the table?And I know most of SQR is usually "cursor" based...yes?Brett8-) |
 |
|
|
jodie
Starting Member
4 Posts |
Posted - 2005-01-19 : 15:18:13
|
I checked the table that has the emplid and name , that is the PS_PAY_CHECK table.I do not see a date time column, the only two date columns are PAY_END_DT and CHECK_DT. We are pulling all the CHECK_DT YTD because this report contains YTD earnings. Therefore, it can go back and pull up maiden names also. Is there anything else I can do to pull all info since we need it for YTD column in the report, but only list it once under the new name. Thanks |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-19 : 15:26:14
|
| Sounds like the data is a tad denormalized...Do you have an employee table that only contains the latest version of the name?I would join to that and use the name from that table.In lieu of that, I would take the last CHECK_DT, find the name for that row, and then use that.That's gonna be a lot of hoop jumping especially for SQR...Do you have the ability to create a view on sql server? That would be your best bet....for all reporting going forwar.Create all your business rules in the view, and then simply select against the view.Do you know what DDL is?Can you post all of the table DDL (including hopefully the employee table I mentioned)?Brett8-) |
|
|
|
jodie
Starting Member
4 Posts |
Posted - 2005-01-19 : 15:35:43
|
| Brett,Yes, there is a table called EMPLOYEE_DETAIL that has only the current name (not old one..) I will try joining to that table.What is DDL. No, I don't know what is that.No, I can't create views on SQL Serverthanks so much. Atleast I feel I have some ideas to work on. I was so clueless before.. |
|
|
|
jodie
Starting Member
4 Posts |
Posted - 2005-01-19 : 15:55:01
|
Brett,This is what I added to the select statement WHERE clause to limit it so it pulls the name from the latest check_dt only. It seems to pull only the latest name. I have yet to do more verifyig and testing to ensure I am not losing any other significant data by doing this..but it doesn't seem so at this point.Thanks so so so much... and C.CHECK_DT = (Select MAX(C2.CHECK_DT) From PS_PAY_CHECK C2 where C2.Emplid = C.Emplid and C2.CHECK_DT <= $End_Dt) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-19 : 16:28:42
|
| Well, good luck, seems to look ok...I would've tried the join to the employee table though...I think it would be more straight forward, AND playing "by the rules".In other words, is there any case where the greates date and name would still be out of dates as compared to the employee tabe?Maybe it's highly unlikely, but I only try to code toward absoultes in the context of the business model.Anyway, like I said...Good LuckLET IT SNOWBrett8-) |
|
|
|
|
|
|
|
|
Select emplid but sort alphabetically