Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    14

    Unanswered: Comparing column values among 2 or more rows

    Hello all,

    I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.

    Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.

    I'll try to explain the structure and purpose as best I can. I'm a SQL noob, so feel free to link me to any reading material that I should have learned already, or that explicitly solves my problem (I have bad google-fu, apparently).

    Three tables to note in bold, with notable fields in italics below them:
    EmployeeData
    HRID (identity)

    ResourceAllocation
    ID (identity)
    [Last Name] (linked to HRID)
    Project
    [Resource Start Date]
    [Resource End Date]
    [Percent Utilization]


    tblHCvalues
    RAID (linked to ResourceAllocation.ID)
    a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...

    And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have

    Here's how it works:
    • Each employee's data and unique HRID is in the EmployeeData table
    • An employee can be on one or multiple projects at any time
    • Those projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed above
    • Even though an employee might be on two projects, they can only count for headcount on one project
    • We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount
    • The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.
    • Run the rules to determine which ResourceAllocation record counts toward headcount
    • A stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want


    All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread.
    Here's the part I need help with, how do I write these rules:

    Here are the rules, and they should work for any number of multiple resource allocations for one employee:
    • Choose the ResourceAllocation with the greatest [Percent Utilization]
    • If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]
    • If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]
    • If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)


    Sounds simple right? I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right? Well, that's what I'm here for.

    Again, let me know if you have any other questions. I'll be checking back here often. Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think you will find the TOP directive and ROW_NUMBER() function will figure into the solution. I need to experiment a little to see if I can capture the essence of this. By the way, is ResourceAllocation really related to Employees by last name? What happens when you hire a couple Smiths?

  3. #3
    Join Date
    Jun 2012
    Posts
    14
    Quote Originally Posted by MCrowley View Post
    I think you will find the TOP directive and ROW_NUMBER() function will figure into the solution. I need to experiment a little to see if I can capture the essence of this. By the way, is ResourceAllocation really related to Employees by last name? What happens when you hire a couple Smiths?
    No, that's an unfortunate artifact of MS Access development. I probably should change the field names while I'm waiting on your experimentation This has an Access front end that still needs to be fully tied to this SQL backend once the automation is finished. That's probably the opportune time to shore all of that stuff up.

    The HRID is an alphanumeric string that is usually xx#######, and that's what goes into both the EmployeeData.HRID and ResourceAllocation.[Last Name] fields. The Employee's actual last and first name are also stored in separate fields in the EmployeeData table, so I use a join to pull them in whenever I need to display names next to their ResourceAllocations.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How does this do. Should work in SQL 2005 and up:
    Code:
    create table ResourceAllocation
    (ID int identity (1, 1),
    LastName varchar(100),
    Project varchar(100),
    ResourceStartDate date,
    ResourceEndDate date, 
    PercentUtilization int)
    
    insert into ResourceAllocation values 
    	('smith', 'big project 1', '2012-01-01', '2012-05-31', 50),
    	('smith', 'big project 2', '2012-02-01', '2012-05-31', 50),
    	('jones', 'big project 1', '2012-05-01', '2012-05-31', 100),
    	('jones', 'big project 4', '2012-02-01', '2012-04-30', 100),
    	('wilson', 'big project 3', '2012-01-01', '2012-05-31', 70),
    	('wilson', 'big project 4', '2012-02-01', '2012-04-30', 30)
    	
    with CTE
    as
    (select row_number() over (partition by lastname order by PercentUtilization desc, ResourceStartDate desc, ResourceEndDate desc) as place, ra.lastname, project, resourcestartdate, resourceenddate, percentutilization
    from resourceallocation ra)
    select *
    from cte
    where place = 1
    EDIT: one other thing I just realized. Don't obsess on the insert statement. SQL 2008 has that feature, and I am lazy at heart.
    Last edited by MCrowley; 06-29-12 at 15:10. Reason: Oops. Had an extra bit in there that was not necessary

  5. #5
    Join Date
    Jun 2012
    Posts
    14
    Quote Originally Posted by MCrowley View Post
    How does this do. Should work in SQL 2005 and up:
    Code:
    create table ResourceAllocation
    (ID int identity (1, 1),
    LastName varchar(100),
    Project varchar(100),
    ResourceStartDate date,
    ResourceEndDate date, 
    PercentUtilization int)
    
    insert into ResourceAllocation values 
    	('smith', 'big project 1', '2012-01-01', '2012-05-31', 50),
    	('smith', 'big project 2', '2012-02-01', '2012-05-31', 50),
    	('jones', 'big project 1', '2012-05-01', '2012-05-31', 100),
    	('jones', 'big project 4', '2012-02-01', '2012-04-30', 100),
    	('wilson', 'big project 3', '2012-01-01', '2012-05-31', 70),
    	('wilson', 'big project 4', '2012-02-01', '2012-04-30', 30)
    	
    with CTE
    as
    (select row_number() over (partition by lastname order by PercentUtilization desc, ResourceStartDate desc, ResourceEndDate desc) as place, ra.lastname, project, resourcestartdate, resourceenddate, percentutilization
    from resourceallocation ra)
    select *
    from cte
    where place = 1
    EDIT: one other thing I just realized. Don't obsess on the insert statement. SQL 2008 has that feature, and I am lazy at heart.
    I'm guessing the only special part of that is the first select statement? If so, would you mind explaining it just so I understand the magic?

    Here's my take:
    • The table and insert are just your test data (I have PLENTY of that )
    • CTE = Common Table Expressions? Is this necessary in the code for any other reason?
    • I didn't know about row_number(), but google says that it's a self-explanatory function
    • The partition by last name ensures that it will reorganize it by last name in the event that an employee's RAs aren't right next to each other
    • The order by ranks it in the orders we want (instead of IF statements)
    • Place ranks them in order, with the top (the one we want after ranking) being 1?
    • Then you select just the top record with the place = 1, and work with that result?

    If so, I'm going to go test it right now.

    EDIT: I'm 99% positive that's what I need. Only thing is the ResourceStartDate should be ASC, not DESC, so that we get the projects that started first.
    Last edited by Datech; 06-29-12 at 15:33. Reason: Extra comment

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes, the table and the insert are just test data. Feel free to embellish. I posted that, because there are a number of regulars here that will likely try their hands at this problem, but not post (they won't post for a variety of reasons, but mainly benign ones).

    Actually, "Place" is just the name of the column. You can run the contents of the CTE, and see what that returns. It will return everything, but with the ROW_NUMBER() function pretty much as you have guessed.

    Think of a CTE as a temporary view, or a subquery, but that subquery has to be used in the very next statement. It is possible to write the query in one go, but it may look a tad messier that way.

  7. #7
    Join Date
    Jun 2012
    Posts
    14
    Quote Originally Posted by MCrowley View Post
    Yes, the table and the insert are just test data. Feel free to embellish. I posted that, because there are a number of regulars here that will likely try their hands at this problem, but not post (they won't post for a variety of reasons, but mainly benign ones).

    Actually, "Place" is just the name of the column. You can run the contents of the CTE, and see what that returns. It will return everything, but with the ROW_NUMBER() function pretty much as you have guessed.

    Think of a CTE as a temporary view, or a subquery, but that subquery has to be used in the very next statement. It is possible to write the query in one go, but it may look a tad messier that way.
    Yep, realized the CTE part as I was testing it. The SELECT you wrote is perfect (minus the ASC/DESC thing). Unfortunately my table is only ending up with 65 records at the moment, as opposed to the ~170 it should be getting. I think that's a problem with my code though. I'll report back when it's fixed.

    Thanks SOO much. If that problem was this easy (that's a relative term), then I bet you guys would cringe at the rest of the code I've written. I'm sure I'll ask for help on improving it in the future though

    EDIT: Pretty sure my code is working right too. There should be ~170 ResourceAllocation entries, so of course there are going to be less in this new table that I insert them into when I filter it down with this SELECT. Durr. Now I've got to find a way to verify that data though...

    Either way, I'm pretty sure this is solved unless someone wants to chime in!
    Last edited by Datech; 06-29-12 at 15:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •