Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

not able to insert data for selected day. Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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
Post #1449167
Posted Friday, May 3, 2013 7:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 23,009, Visits: 31,501
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.



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)
Post #1449182
Posted Friday, May 3, 2013 7:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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]
Post #1449207
Posted Friday, May 3, 2013 7:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 23,009, Visits: 31,501
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.



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)
Post #1449213
Posted Friday, May 3, 2013 8:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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
Post #1449220
Posted Friday, May 3, 2013 10:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 23,009, Visits: 31,501
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.



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)
Post #1449299
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse