not able to insert data for selected day.

  • Hi All,

    My aim is that, i want to insert data into table for selected day only among the selected range of date. I am able to select particular days data in temporary table but while inserting into database table it is not inserting properly.

    I have written below SP by taking reference from this forum only but data is not going properly. somewhere i am doing mistake.

    CREATE PROCEDURE [dbo].[Usp_InsertStudWeekly]

    @StudID INT,

    @StartDate datetime,

    @EndDate datetime,

    @StartTime varchar(20),

    @EndTime varchar(20),

    @DayName varchar(100) ,

    @flag bit

    AS

    BEGIN

    SET NOCOUNT ON;

    IF OBJECT_ID('TempDB..#test','U') IS NOT NULL

    drop table #test

    create table #test

    (AdvisorID int, startdate datetime, enddate datetime, starttime datetime, endtime datetime,flag bit

    )

    insert into #test

    SELECT @StudID ,@StartDate, @EndDate, @StartTime, @EndTime,0

    --select 1, '2012-01-03 00:00:00', '2012-01-06 00:00:00', '2012-01-03 07:00:00', '2012-01-03 08:00:00'

    INSERT INTO dbo.tblStudent

    select StudID,

    dateadd(day, b.n-1, startdate) as startdate

    ,Tstart = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)), 0),7)

    , TsEnd = RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, starttime)), 0),7)

    --, datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)))

    ,0 as flag

    from #test

    cross join Tally A

    cross join Tally B

    where

    a.N >= 1 and a.N <= datediff(mi, starttime, endtime)/15 and

    b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1 and

    datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime))) in (@DayName)

    END

    --exec Usp_InsertStudWeekly 1,'2013-05-01 00:00:00', '2013-05-31 00:00:00', '2013-05-01 10:00:00', '2013-05-31 14:00:00','Monday',false

  • Could you please provide the DDL (CREATE TABLE statement) for the target table of the procedure and the expected results based on the sample data input provided for the procedure.

    I am thinking that you may have over thought what was needed but need to know what you are trying to accomplish to be sure. You have provided a good start but there are just a few things missing that if provided will offer us a clearer picture.

  • Thanks Lynn,

    i want to insert data in below table.

    CREATE TABLE [dbo].[tblstudentPlaner](

    [PlanerID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

    [StudID] [int] NOT NULL,

    [EffectiveDate] [datetime] NULL,

    [StartTime] [varchar](20) NULL,

    [EndTime] [varchar](20) NULL,

    [Flag] [bit] NULL,

    CONSTRAINT [PK_tblstudentPlaner] PRIMARY KEY CLUSTERED

    (

    [PlanerID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

  • One step closer when we needed two. I still need to see what your expected results are when the procedure you are working on runs with the sample input provided. I am still not sure what it is the procedure is actually supposed to do here.

  • Hi Lynn,

    Forgot to explain how to add data.

    i want to add data for particular day selected by user. Example if user want to add data for Only Monday or friday etc.

    I am able to find day using below query. but not able to add in database table.

    IF OBJECT_ID('TempDB..#test','U') IS NOT NULL

    drop table #test

    create table #test

    (studentid int,

    studentname varchar(20),

    startdate datetime,

    enddate datetime,

    starttime datetime, endtime datetime

    )

    insert into #test

    select 1, 'john', '2013-05-01 00:00:00', '2013-05-31 00:00:00', '2013-05-01 10:00:00', '2013-05-31 14:00:00'

    select studentid, studentname, dateadd(day, b.n-1, startdate)

    , RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)),0),7) as starttime

    , RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, starttime)),0),7) as endtime

    , datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)))

    from #test

    cross join Tally A

    cross join Tally B

    where

    a.N >= 1 and a.N <= datediff(mi, starttime, endtime)/15 and

    b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1

  • abhas (5/3/2013)


    Hi Lynn,

    Forgot to explain how to add data.

    i want to add data for particular day selected by user. Example if user want to add data for Only Monday or friday etc.

    I am able to find day using below query. but not able to add in database table.

    IF OBJECT_ID('TempDB..#test','U') IS NOT NULL

    drop table #test

    create table #test

    (studentid int,

    studentname varchar(20),

    startdate datetime,

    enddate datetime,

    starttime datetime, endtime datetime

    )

    insert into #test

    select 1, 'john', '2013-05-01 00:00:00', '2013-05-31 00:00:00', '2013-05-01 10:00:00', '2013-05-31 14:00:00'

    select studentid, studentname, dateadd(day, b.n-1, startdate)

    , RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)),0),7) as starttime

    , RIGHT(CONVERT(VARCHAR(100),dateadd(day, b.N-1, DATEADD(mi, a.N*15, starttime)),0),7) as endtime

    , datename(dw,dateadd(day, b.N-1, DATEADD(mi, (a.N-1)*15, starttime)))

    from #test

    cross join Tally A

    cross join Tally B

    where

    a.N >= 1 and a.N <= datediff(mi, starttime, endtime)/15 and

    b.N >= 1 and b.N <= DATEDIFF(day, startdate,enddate) + 1

    What would work better, create a series of insert into statements that can be used to populate a copy of the target table with what the expected output from the procedure would be. Word descriptions are good, an actually "picture" of the results would be better. Plus, this copy of the table can then be used to validate the results.

Viewing 6 posts - 1 through 5 (of 5 total)

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