Ranking Query

Hrithika

Registered User.
Local time
Today, 17:38
Joined
Aug 5, 2011
Messages
53
I have a certain set of data that needs to be ranked based on sorting on 5 different data fields.

The ranking is based on sorting at 5 level

1. Location
2. Category
3. Unit
4. HireDate
5. Seniority Score

The ranking has to be within each Location and within each category and within each unit based on the hire date. If two people within the same unit have the same hire date, then the seniority score will be considered to rank who is on top.

I Know it's hard to explain this in word. I have attached the spreadsheet for everyone to look at. I was able to use the first rank based on the first 4 but was not able to use the Seniority score in the query.

Given below is the sample query for just one location and union all for each of the location. I don't think that this is the most graceful way of doing. Please advise. Thanks in advance.

SELECT EMP.First, EMP.Category, EMP.Unit, EMP.Location, EMP.Hire, EMP.SSN_Digits (Select Count(*) from UIAB_STAFF_TBL2 Where (Location='Buffalo' and Category='Admin' and Unit='Hearing'and [Hire] < [EMP].[Hire]))+1 AS Seniority
FROM UIAB_STAFF_TBL2 AS EMP
WHERE EMP.LOCATION='Buffalo'
and Category='Admin'
and Unit='Hearing'

union all

SELECT EMP.First, EMP.Category, EMP.Unit, EMP.Location, EMP.Hire, EMP.SSN_Digits (Select Count(*) from UIAB_STAFF_TBL2 Where (Location='Buffalo' and Category='Admin' and Unit='Appeal'and [Hire] < [EMP].[Hire]))+1 AS Seniority
FROM UIAB_STAFF_TBL2 AS EMP
WHERE EMP.LOCATION='Buffalo'
and Category='Admin'
and Unit='Appeal'

union all

SELECT EMP.First, EMP.Category, EMP.Unit, EMP.Location, EMP.Hire, EMP.SSN_Digits (Select Count(*) from UIAB_STAFF_TBL2 Where (Location='Buffalo' and Category='Admin' and Unit='Hearing'and [Hire] < [EMP].[Hire]))+1 AS Seniority
FROM UIAB_STAFF_TBL2 AS EMP
WHERE EMP.LOCATION='Buffalo'
and Category='ALJ'
and Unit='Hearing'

union all

SELECT EMP.First, EMP.Category, EMP.Unit, EMP.Location, EMP.Hire, EMP.SSN_Digits (Select Count(*) from UIAB_STAFF_TBL2 Where (Location='Buffalo' and Category='Admin' and Unit='Appeal'and [Hire] < [EMP].[Hire]))+1 AS Seniority
FROM UIAB_STAFF_TBL2 AS EMP
WHERE EMP.LOCATION='Buffalo'
and Category='ALJ'
and Unit='Appeal'
 

Attachments

Yuo can use an "ASCENDING" or "DESCENDING" sort.
Look at "DemoRankingQueryA20022003.mdb" (attachment, zip).
Look at table and queries.
Rune Query1 or Query2 and see.
 

Attachments

Thanks for your suggestion. I could do the sorting as you suggested. That was not the problem. The challenge is in getting the ranking score inserted based on the result of this sort. If you see the attached spreadsheet, the creation of the data in the highlighted column named Ranking is the challenge. Is there any way to get that done?
 
Not sure I'm grasping exactly what you're trying to do, but have you considered using the the rank function? You can assign a rank based on any column.

So for each category you want to rank by, you could create a subquery, and join them together. The end result would be something like

Employee, LocationRank, CategoryRank, UnitRank....

You could then sort based on a combination of all 5 of those fields.
 
arichins,

What database package are you using that has a Rank function?
Please tell us more about the Rank function.

I noticed another post by you, showing the SUBSTRING function -- you are aware this is an MS Access forum, right?
 
I was also wondering the same thing. I don't think that access supports the ranking function. I know oracle does.
 
Yes, many statistcal functions and Oracle has SUBSTR also.

Perhaps arichinis felt he was on a different forum???

Did you work with the database that MStef provided?

I really don't know what you are trying to do, but when I see UNION ALL (which includes duplicates) I tend to shudder a little.

Why the importance with ranking?
 
Yes. I did look at the database that MStef provided. What I need is one more column at the end of his data that shows rank.

In my situation the ranking has to be grouped under each location and within each location,the category should be grouped and within each category the unit should be grouped and then look for seniority based on hire date.

If two people within the same unit have same hire date, then look for the offer date. If offer dates are same, then look for the seniority ranking.
I have attached my problem database. One table contains the data and another table contains the expected results.

I know this is a rare scenario. We are made to do this to satisfy the employee union.
 

Attachments

Looks like I am having incremental success. I managed to rank based on multiple fields, but there is no way around using union all to rank it within each unit.

I am attaching the completed database in case if it helps someone.

Also for someone to help me find a more elegant solution. The existing query works and gets me the data I want. But if there is an elegant solution, please do let me know.
 

Attachments

The rank function can be used in sql server, I only use Access as a front end. Using sql server as a back end has many advantages You can get SQL Server Express for free.
 
Unfortunately we needed a solution in access itself. The solution posted is still working. Thanks for checking out the post.
 
I don't know where you are on this issue. MStef gave you a database and I don't know if it helped.

Access does NOT have an intrinsic Rank function.

You may find something in Allen Browne's article at
http://allenbrowne.com/ranking.html
 
I have a working solution now. This issue is resolved.
 
Would you like to tell us what you did to resolve the issue?
It might save the next person a lot of time.

Glad you have it working.
 
Given below is the query that I am using to rank and insert the records into a table. I have nine locations I have separate query for each location which is pretty much find and replace the location name.

As I said earlier it's not the most elegant solution but it is working for me.


Code:
INSERT INTO UIAB_STAFF
SELECT *
FROM (SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='ALJ' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='ALJ' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='ALJ' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Board' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Board')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Board' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Board')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

UNION ALL 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

UNION ALL 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all

SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc

UNION ALL 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  
(Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc )  AS [%$##@_Alias];
 
Last edited:
Given below is the query that I am using to rank and insert the records into a table. I have nine locations I have separate query for each location which is pretty much find and replace the location name.

As I said earlier it's not the most elegant solution but it is working for me.


Code:
INSERT INTO UIAB_STAFF
SELECT *
FROM (SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='ALJ' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='ALJ' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='ALJ' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='ALJ')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Board' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Board')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Board' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Board')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
UNION ALL 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Admin' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Admin')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
UNION ALL 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Executive' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Executive')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='Hearing' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Hearing' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='Appeal' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Appeal' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
union all
 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='Front Office' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='Front Office' and EMP.ServiceEndDate is null 
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc
 
UNION ALL 
SELECT EMP.Item, EMP.RACF, EMP.First & " " & EMP.Middle & " " & EMP.Last AS Name, EMP.Email, EMP.Phone, EMP.Title, EMP.Category, EMP.UIAB_Role, EMP.Unit, EMP.Location, EMP.Status, EMP.NU, EMP.Supervisor , EMP.Hire, EMP.OfferDate, EMP.SSN_Digits,  (Select Count(emp.unit) from UIAB_STAFF_TBL2 Where (Location='Bond Street'  and Category='Project' and Unit='IT' and ServiceEndDate is null and ( [Hire] < [EMP].[Hire] or ([Hire] = [EMP].[Hire] and [offerdate] < [EMP].[OfferDate]) or ([Hire] = [EMP].[Hire] and [SSN_Digits] > [EMP].[SSN_Digits]))))+1 AS Seniority, EMP.Title_Hire, EMP.ServiceEndDate
FROM UIAB_STAFF_TBL2 AS EMP
WHERE (((EMP.Location)='Bond Street') AND ((EMP.[Category])='Project')) and EMP.Unit='IT' and EMP.ServiceEndDate is null
ORDER BY EMP.Location, EMP.Category, EMP.Unit, EMP.Hire, EMP.OfferDate, EMP.SSN_Digits desc )  AS [%$##@_Alias];
 

Users who are viewing this thread

Back
Top Bottom