08-15-12, 18:20 #1Registered User
- Join Date
- Jun 2012
Unanswered: Aggregating One table and putting results in another table
I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Note: some of the descriptions below are for me to keep track of what I need to do. Sorry for the extraneous information, but just focus on the help request at the end and any details you need to solve it.
Table 1: tblResourceAllocation
- Fields (Description):
- ID (Unique)
- LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)
- Project (Unique name, can have multiple entries in this table)
- Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)
- ResourceStartDate (datetime)
- ResourceEndDate (datetime)
- ServiceID [I](this is what I want to aggregate)
- Status_FC [I]
Table 2: tblGRFM
- ID (unique)
- Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)
- IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)
- US_State_Id (look up State from Projects table then ID from tblStateCodes)
- Project_name (same as tblResourceAllocation.Project)
- Status (same as tblResourceAllocation.Status_FC)
- Id_operlst (same as tblResourceAllocation.ServiceID)
- Start (earliest start date of relevant entries)
- end (latest end date of relevant entries)
- Nb_ress (total number of ResourceAllocation entries that match Id_operlst)
- Cadence (explanation below)
Now here are the specifics. If you have tips on how to do one piece but not another, I welcome it anyway! This entire thing is a learning experience for me
- I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2
- I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
- I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
- Same goes for the other fields, which obviously adds more complexity
- Within those calculations I also need to calculate the Start and end date for table 2
- Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
Cadence (This might be a hard one):
- I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
- I have 3 resources on a project
- Resource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)
- Resource 2: 1/7/2013-1/27/2013 (Week 2-4)
- Resource 3: 1/7/2013-1/27/2013 (Week 2-4)
- tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013
- Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
- Any ideas?
That's everything I can think of right now. Thanks for looking everyone. Any and all help will be welcome, so solve what you can or point me in the right direction. I'm pretty new to SQL and terminology, so my Google-Fu isn't what it should be with questions like this. Ask any and all necessary questions as well.
08-16-12, 14:32 #2Registered User
- Join Date
- Jun 2012
So, for the main task of counting the similar records in one table and putting the result into another, I'm thinking of the following:
- Have a cursor going through the table of serviceID's and saving the current one as a variable.
- Do a COUNT of table 1 where ServiceID = varserviceID
- Insert into table 2 the return of the count, and fill in the other info I need to go along with it (mainly which serviceID I counted)
I should be able to throw other things in there to get the Owner and other parameters to differentiate themselves, but I guess I'll think through that as I'm coding. Is that the appropriate start to this portion of the problem?
08-16-12, 16:26 #3Registered User
- Join Date
- Jun 2012
So here's the code for getting the aggregation and doing the dates in the format I need. Amazing how powerful the OVER clause can be...
INSERT INTO tblGRFM ( OpFc_Pm ,OpFc_IdClient ,OpFc_US_State_Id ,OpFc_Project_name ,OpFc_Status ,OpFc_Id_operlst ,OpFc_Nb_ress ,OpFc_Start ,OpFc_end ,PM_UPI ) SELECT DISTINCT ED.Full_Name ,PR.Account ,PR.State ,RA.Project ,RA.Status_FC ,RA.ServiceID ,COUNT(RA.[Last Name]) OVER (PARTITION BY ED.Full_Name, PR.Account, PR.State, RA.Project, RA.Status_FC, RA.ServiceID) --AS CountPerServiceID ,CONVERT(varchar,(MIN(RA.[Resource Start Date]) OVER (PARTITION BY ED.Full_Name, PR.Account, PR.State, RA.Project, RA.Status_FC, RA.ServiceID)),101) --AS Start ,CONVERT(varchar,(MAX(RA.[Resource End Date]) OVER (PARTITION BY ED.Full_Name, PR.Account, PR.State, RA.Project, RA.Status_FC, RA.ServiceID)), 101) --As End_Date ,RA.Owner FROM ResourceAllocation AS RA JOIN EmployeeData AS ED ON RA.Owner = ED.HRID JOIN Projects AS PR ON RA.Project = PR.SOW WHERE ((RA.ServiceID IS NOT NULL) AND (RA.Project IS NOT NULL))
Last edited by Datech; 08-16-12 at 17:20.