Insert values into single record

  • Hi..There are two tables employee and Shiftscheduler which to include where...since yourtable i am not get it exactly

  • Hi,

    Almost done with it..please check some bugs still

    ALTER PROCEDURE Emp_Shift1

    (

    @empcode INT,

    @FromDate datetime,

    @EndDate datetime

    )

    AS

    BEGIN

    --DROP TABLE #t

    CREATE TABLE #t(

    [Date] date,

    [Empid] INT,

    [shift] int,

    [shifttype] int

    )

    INSERT INTO #t

    SELECT

    SS.EndDate,

    SS.[Shift],

    SS.ShiftType,

    e.empid

    FROM

    dbo.ShiftScheduler SS,dbo.Emploee e WHERE e.empcode=SS.Emp_Code

    AND

    e.empid = @empcode

    and FromDate >= @FromDate

    and EndDate <= @EndDate

    UNION ALL

    SELECT

    @EndDate,

    [Shift],

    ShiftType,

    empid

    FROM

    dbo.Emploee

    WHERE

    empid = @empcode

    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Empid] ORDER BY [Date]) AS row_num

    INTO #tmp

    FROM #t

    Declare @sql varchar(max);

    set @sql= STUFF((select ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shift] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shift')

    + ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shifttype] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shifttype')

    from #tmp

    GROUP BY row_num

    ORDER BY row_num

    FOR XML PATH('')), 1, 1, '');

    set @sql= 'select [Empid]

    , ' + @sql + '

    from #tmp

    Group by [Empid]';

    -- print @sql

    exec(@sql);

    END

    output is

    Empidd1shiftd1shifttype

    1 1 2190

    2 4 2190

    Problem here is empid coming in d1shifttype column.. also i want all the shift details in single column like d1shift,d1shiftype,d2shift,d2shifttype,d3shidt,d3shifttype

    Please help

    For your reference added some DDL

    CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    INSERT INTO dbo.Employee values('1/1/2015','1/4/2015',1,1,3321)

    INSERT INTO dbo.Employee values('1/7/2015 ','1/8/2015 ',1,1,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)

  • your sample data set up doesnt work......plus I would suggest you extend it to include all possible variants ,

    CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    INSERT INTO dbo.Employee values('1/1/2015','1/4/2015',1,1,3321)

    INSERT INTO dbo.Employee values('1/7/2015 ','1/8/2015 ',1,1,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    CREATE TABLE dbo.Emploee(FromDate DATETIME,Shift INT,ShiftType INT ,empid VARCHAR(50))

    INSERT INTO dbo.Emploee values('1/1/2015','1/4/2015',1,1,3321)

    INSERT INTO dbo.Emploee values('1/7/2015 ','1/8/2015 ',1,1,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)

  • ganapathy.arvindan (5/11/2016)


    CREATE TABLE dbo.ShiftScheduler(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    CREATE TABLE dbo.Emploee(FromDate DATETIME,Shift INT,ShiftType INT ,empid VARCHAR(50))

    INSERT INTO dbo.Emploee values('1/1/2015','1/4/2015',1,1,3321)

    INSERT INTO dbo.Emploee values('1/7/2015 ','1/8/2015 ',1,1,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/5/2015','1/6/2015',2,4,3321)

    INSERT INTO dbo.ShiftScheduler VALUES('1/9/2015','1/10/2015',2,4,3321)

    did you run this code....I get

    Msg 213, Level 16, State 1, Line 3

    Column name or number of supplied values does not match table definition.

    you are trying to insert two dates where only one date (Fromdate) is in the tables.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Totally this is dummy(created same has source)

    CREATE TABLE dbo.ShiftScheduler1(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    CREATE TABLE dbo.Employee(FromDate DATETIME,Shift INT,ShiftType INT ,Emp_Code VARCHAR(50))

    INSERT INTO dbo.Employee values('1/1/2015',1,1,3321)

    INSERT INTO dbo.Employee values('1/7/2015',1,1,3321)

    INSERT INTO dbo.ShiftScheduler1 VALUES('1/5/2015',2,4,3321)

    INSERT INTO dbo.ShiftScheduler1 VALUES('1/9/2015',2,4,3321)

  • Hi,

    Add the necessary column in the create statement.

    Change the order of the insert and then use the below statement.

    Declare @sql varchar(max);

    set @sql= STUFF((select ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shift] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shift')

    + ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shifttype] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shifttype')

    from #tmp

    GROUP BY row_num

    ORDER BY row_num

    FOR XML PATH('')), 1, 1, '');

    set @sql= 'select [Empid]

    , ' + @sql + '

    from #tmp

    Group by [Empid]';

    -- print @sql

    exec(@sql);

    Im getting the o/p as below.

    Empidd1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttyped5shiftd5shifttyped6shiftd6shifttype

    3321112424112424

  • Hi,

    ALTER PROCEDURE Emp_Shift1

    (

    @empcode INT,

    @FromDate datetime,

    @EndDate datetime

    )

    AS

    BEGIN

    --DROP TABLE #t

    CREATE TABLE #t(

    [Empid] INT,

    [Date] date,

    [shift] int,

    [shifttype] int

    )

    INSERT INTO #t

    SELECT

    e.empid,

    SS.EndDate,

    SS.[Shift],

    SS.ShiftType

    FROM

    dbo.ShiftScheduler SS,Ihsmaster_delop.dbo.Emploee e WHERE e.empcode=SS.Emp_Code

    AND

    e.empid = @empcode

    and FromDate >= @FromDate

    and EndDate <= @EndDate

    UNION ALL

    SELECT

    empid,

    @EndDate,

    [Shift],

    ShiftType

    FROM

    Ihsmaster_delop.dbo.Emploee

    WHERE

    empid = @empcode

    SELECT empid,shift,shifttype, ROW_NUMBER() OVER(PARTITION BY [Empid] ORDER BY [Date]) AS row_num

    INTO #tmp

    FROM #t

    Declare @sql varchar(max);

    set @sql= STUFF((select ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shift] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shift')

    + ', MAX(case when row_num = ' + CONVERT(varchar, row_num) + ' then [shifttype] end ) as ' + QUOTENAME('d' + CONVERT(varchar, row_num) + 'shifttype')

    from #tmp

    GROUP BY row_num

    ORDER BY row_num

    FOR XML PATH('')), 1, 1, '');

    set @sql= 'select [Empid]

    , ' + @sql + '

    from #tmp

    Group by [Empid]';

    -- print @sql

    exec(@sql);

    END

    getting output like this....

    Empidd1shiftd1shifttyped2shiftd2shifttype

    2190 2 4 1 1

    Actually only one record is from 2 and 4 type From date and End Date

    Emp_Code ShiftShiftType FromDate EndDate

    2190 2 4 2016-04-01 00:00:00.0002016-04-03 00:00:00.000

    So the output should be like this

    Empidd1shiftd1shifttyped2shiftd2shifttype d3shiftd3shifttype d4shiftd4shifttype d5shiftd5shifttype

    2190 2 4 2 4 2 4 1 1 1 1

    Please help

  • Empidd1shiftd1shifttyped2shiftd2shifttype d3shiftd3shifttype d4shiftd4shifttype d5shiftd5shifttype

    21902 4 2 4 2 4 1 1 1 1

    is this expected output to be in separate columns for each result...or is it just one single column?

    if its one single column what is this being used for, becasue I cannot see how it is going to be useful if someone has to read it?

    also, it would really make it a whole lot easier if you could tie your sample data names with the procedure you keep posting...I for one am not going to spend time altering column and tables names for you.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Empid d1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype

    1255 21 2 1 1 1 2 1

    for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype

  • ganapathy.arvindan (5/11/2016)


    Empid d1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype

    1255 21 2 1 1 1 2 1

    for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype

    is this one column output...or is empid, d1shift,d1shiftype etc in separate columns...

    eg

    +---------------------------------------------------------------------------------------------------------------------+

    ¦ ¦ Empid ¦ d1shift ¦ d1shifttype ¦ d2shift ¦ d2shifttype ¦ d3shift ¦ d3shifttype ¦ d4shift ¦ d4shifttype ¦

    ¦------+-------+---------+-------------------+---------+-------------+----------+-------------+---------+-------------¦

    ¦ 1255 ¦ 2 ¦ 1 ¦ 2 ¦ 1 ¦ 1 ¦ 1 ¦ 2 ¦ 1 ¦ ¦

    +---------------------------------------------------------------------------------------------------------------------+

    by not formatting your expected results it is not easy to determine

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Yes exactly...same format

  • ganapathy.arvindan (5/11/2016)


    Yes exactly...same format

    I can't believe that this thread is still going strong. Had you taken the time to provide source data, DDL and expected results, as described in the link in my signature, this problem would have been solved by now.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • ganapathy.arvindan (5/11/2016)


    Empid d1shiftd1shifttyped2shiftd2shifttyped3shiftd3shifttyped4shiftd4shifttype

    1255 21 2 1 1 1 2 1

    for the remaining days it should display Null or 0..starting from d5shift d5shifttype d6shift d6shifttype ..................d30shift d30shifftype

    I think that if step thro your code and run this part

    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Empid] ORDER BY [Date]) AS row_num

    --INTO #tmp

    FROM #t

    you will start to understand why you are not getting the remaining days you are looking for.

    Can I ask why you are using 'D1, D2 etc' rather than actual dates...i would have thought that it would be far easier for end user.....possibly???

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Yes Dates would be easier but 01012016shift 01012016shifttype looks not pretty..for the understanding i need all the d1shift d1shifttype d2shift d2shifttype......d30shift d30shifttype..if i deosn't have value then it should display has 0

    Thanks

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply