SQL Clone
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-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 664
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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41354 Visits: 38567
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-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 664
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-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41354 Visits: 38567
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-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 Visits: 664
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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

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

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