Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


not able to insert data for selected day.


not able to insert data for selected day.

Author
Message
abhas
abhas
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 662
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
      Wink

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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
abhas
abhas
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 662
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]
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
abhas
abhas
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 662
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
      Wink

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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
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
      Wink

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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search