How to insert the records by group

  • 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

  • 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.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • 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

  • 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

  • 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]

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

  • 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.

  • select GROUP_ID, EMP_ID, NTILE(4) OVER (partition by [GROUP_ID] order by [EMP_ID]) AS GROUPLIST

    from [dbo].[EMP_REC]

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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