May 23, 2012 at 11:24 pm
Table 1 contains the below fields
GROUP_ID, Emp_id and emp name
Table 2 contains the below fields
EMpid, GroupList, GROUP_ID
Scenario is to insert the Grouplist and Group id in to table 2
Condition:
1)GroupList – Each grouplist (per GROUP_ID will have (TOTAL_CNT / 4) records in it (rounded up to nearest whole value)
Count the total no of records in table 1 group by GROUP_ID
E.G. TOTAL_CNT = 175
TOTAL_CNT / 4 = 43.75 – Round up to 44.
Record #1 to Record # 44 – GroupList will be 1.
Record #45 through Record #89 – GroupList will be 2.
Record #90 through Record #133 – GroupList will be 3.
Record #134 through Record #175 – GroupList will be 4
May 23, 2012 at 11:34 pm
Please have a look at the link in my signature and post sample data as shown in it. People will find it a lot easier to work on your requirement if they have some ready to use sample data.
May 24, 2012 at 3:28 am
Please find the table queries and data
/* Create tables */
CREATE TABLE [dbo].[EMP_REC](
[GROUP_ID] [varchar](10) NULL,
[EMP_ID] [varchar](10) NULL,
[EMP_NAME] [varchar](35) NULL
)
CREATE TABLE [dbo].[EMP_SUMMARY](
[GROUP_ID] [varchar](10) NULL,
[EMP_ID] [varchar](10) NULL,
[GROUPLIST] [varchar](35) NULL
)
/* Insert tables */
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1201','RAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1202','XRAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1203','XXRAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1204','XXXRAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1205','SXXRAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1206','PXXRAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('101','1217','TXXRAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('102','1221','XXSAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('102','1225','XXXSAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('102','1228','RXXSAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('102','1230','RXXSAM')
INSERT INTO [dbo].[EMP_REC]
([GROUP_ID]
,[EMP_ID]
,[EMP_NAME])
VALUES
('102','1238','RXDXSAM')
Need to insert the values to the table [dbo].[EMP_SUMMARY] as per the below condition
Condition:
1)GroupList – Each grouplist (per GROUP_ID will have (TOTAL_CNT / 4) records in it (rounded up to nearest whole value)
Count the total no of records in table 1 group by GROUP_ID
E.G. TOTAL_CNT = 175
TOTAL_CNT / 4 = 43.75 – Round up to 44.
Record #1 to Record # 44 – GroupList will be 1.
Record #45 through Record #89 – GroupList will be 2.
Record #90 through Record #133 – GroupList will be 3.
Record #134 through Record #175 – GroupList will be 4
May 24, 2012 at 3:52 am
Expected result :
GROUP_IDEMP_IDGROUPLIST
101 12011
101 12021
101 12032
101 12042
101 12053
101 12063
101 12174
102 12211
102 12252
102 12283
102 12304
102 12384
May 24, 2012 at 4:09 am
insert into [dbo].[EMP_SUMMARY]
select GROUP_ID, EMP_ID, NTILE(4) OVER (ORDER BY [GROUP_ID], [EMP_ID]) AS GROUPLIST
from [dbo].[EMP_REC]
May 24, 2012 at 4:59 am
Thanks for your prompt Reply.
But thing is that we need to split the total records into 4 batch as per each GROUP_ID.
In our example, total records for GROUP_ID = 101 is 7
Divide the records by 4.
7/ 4 =1.75
Roundoff the count. So total =2
Record #1 to Record #2 – GROUP_ID will be 1.
Record #3 to Record #4 – GROUP_ID will be 2.
Record #5 to Record #6 – GROUP_ID will be 3.
Record #7 – GROUP_ID will be 4.
"GROUP
_ID"EMP_IDGROUPLIST
10112011
10112021
10112032
10112042
10112053
10112063
10112174
then we need to insert the GROUP_ID =102 as the same way above.
May 24, 2012 at 5:01 am
Sorry for the mistake
Please read it as
Record #1 to Record #2 – GROUPLIST will be 1.
Record #3 to Record #4 – GROUPLIST will be 2.
Record #5 to Record #6 – GROUPLIST will be 3.
Record #7 – GROUPLIST will be 4.
May 24, 2012 at 5:22 am
select GROUP_ID, EMP_ID, NTILE(4) OVER (partition by [GROUP_ID] order by [EMP_ID]) AS GROUPLIST
from [dbo].[EMP_REC]
May 24, 2012 at 6:19 am
Thanks for your Prompt Reply.
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply