getting error in pivot stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting error in pivot stored procedure

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 08:47:08

Hi All,

This is my table and pivot storedprocedure.I am getting error when i run the stored procedure

My requirement is

I want to find employees who are present in first 3 weeks and missing in 4th week.

I am trying to get reports like this

EmpName 10/3/2010 10/4/2010 10/5/2010 10/6/2010 10/7/2010 10/8/2010 10/9/201

Naresh 17:00 14:00 ..........

CREATE TABLE TestTable(EmpNo int,EmpName varchar(20),
atndate DATETIME,
StartTime varchar(50),EndTime varchar(50),MatchingOff varchar(20),CampaignID int
)

SET IDENTITY_INSERT mytable ON
Insert into TestTable(EmpNo,EmpName,atndate,StartTime,EndTime,MatchingOff,CampaignID)

select '3','NareshNadimpalli','10/3/2010','0','0','Off','3' union all
select '3','NareshNadimpalli','10/4/2010','7:30 AM','6:00 PM','07:30 ','3' union all
select '3','NareshNadimpalli','10/5/2010','0','0','Off','3' union all
select '3','NareshNadimpalli','10/6/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/7/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/8/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/9/2010','7:30 AM','7:30 AM','07:30 ','3' union all

select '2','ALETARIDINGER','10/3/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/4/2010 12:00:00 AM','7:30 AM','6:00 PM','07:30 ','1' union all
select '2','ALETARIDINGER','10/5/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/6/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/7/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/8/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/9/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all

select '2','ALETARIDINGER','10/17/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/18/2010 12:00:00 AM','7:30 AM','6:00 PM','07:30 ','1' union all
select '2','ALETARIDINGER','10/19/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/20/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/21/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/22/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all

select '2','ALETARIDINGER','10/23/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1'

alter PROCEDURE [dbo].[Test](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME,@CampaignId int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Agents Scheduled as per Schedule' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + ']' +
'FROM
(Select empno,empname,atndate,starttime as EmpTime from TestTable
where atndate>=[' + CONVERT(VARCHAR,'9/26/2010',101) + '] and atndate<=[' + CONVERT(VARCHAR,'10/16/2010',101) + ']
and empno not in
(Select empno from TestTable where atndate between [' + CONVERT(VARCHAR,'10/17/2010',101) + '] and [' + CONVERT(VARCHAR,'10/23/2010',101) + ']
group by empno)
where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(EmpTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + ']' + ')
) AS PivotTable'
EXEC(@query)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 09:02:14

replace EXEC(@Query) with PRINT(@Query) and post the result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 09:17:58

Hi,

I am getting this result when i use print

SELECT 'Agents Scheduled as per Schedule' AS HeadCount, [09/26/2010], [10/03/2010], [10/10/2010]FROM
(Select empno,empname,atndate,startTime from TestTable
where atndate>=[9/26/2010] and atndate<=[10/16/2010]
and empno not in
(Select empno from TestTable where atndate between [10/17/2010] and [10/23/2010]
group by empno)
where CampaignID=1)
AS SourceTable
PIVOT
(
max(startTime)
FOR atndate IN ([09/26/2010], [10/03/2010], [10/10/2010])
) AS PivotTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 09:28:50

try this then


alter PROCEDURE [dbo].[Test](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME,@CampaignId int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Agents Scheduled as per Schedule' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + ']' +
' FROM
(Select empno,empname,atndate,starttime as EmpTime from TestTable
where atndate>=''' + CONVERT(VARCHAR,'9/26/2010',101) + ''' and atndate<=''' + CONVERT(VARCHAR,'10/16/2010',101) + ''' 
and empno not in
(Select empno from TestTable where atndate between [' + CONVERT(VARCHAR,'10/17/2010',101) + '] and [' + CONVERT(VARCHAR,'10/23/2010',101) + ']
group by empno)
and CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(EmpTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + ']' + ')
) AS PivotTable'
EXEC(@query)
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 09:32:45

Hi,

Small correction in the stored procedure.This is the actual stored procedure

You can use table with data which i posted to run this tsored procedure.

I gave gap near From but still getting error.

Error message

Msg 207, Level 16, State 1, Line 3
Invalid column name '9/26/2010'.
Msg 207, Level 16, State 1, Line 3
Invalid column name '10/2/2010'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '10/17/2010'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '10/23/2010'.

alter PROCEDURE [dbo].[Test](@date1 DATETIME,@CampaignID int)
AS
DECLARE @query VARCHAR(MAX)
Declare @date11 datetime
Declare @date12 datetime
Declare @date13 datetime
Declare @date14 datetime
Declare @date15 datetime
Declare @date16 datetime
Declare @date17 datetime
set @date11='9/26/2010'
set @date12='9/27/2010'
set @date13='9/28/2010'
set @date14='9/29/2010'
set @date15='9/30/2010'
set @date16='10/1/2010'
set @date17='10/2/2010'
BEGIN
SET @query = 'SELECT '+ CHAR(39) + 'Agents Scheduled as per Schedule' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +
'FROM
(Select empno,empname,atndate,startTime from TestTable
where atndate>=[' + CONVERT(VARCHAR,'9/26/2010',101) + '] and atndate<=[' + CONVERT(VARCHAR,'10/2/2010',101) + ']
and empno not in
(Select empno from TestTable where atndate between [' + CONVERT(VARCHAR,'10/17/2010',101) + '] and [' + CONVERT(VARCHAR,'10/23/2010',101) + ']
group by empno))
AS SourceTable
PIVOT
(
max(startTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')
) AS PivotTable'
exec(@query)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 10:19:02

See if this helps
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 10:46:48

Hi,

This stored procedure is not returning data nor error.Simply it is returning Column Headers.

Same logic i implemented in query below it is working when i include it in the stored procedure it is not working.

Select empname,atndate,starttime +'-'+Endtime as EmpTime from TestTable
where atndate>='09/26/2010' and atndate<='10/16/2010'
and empname not in
(Select empname from TestTable where atndate>= '10/17/2010' and atndate<='10/24/2010'
group by empname)

alter PROCEDURE [dbo].[Test](@date1 DATETIME,@CampaignID int)
AS
DECLARE @query VARCHAR(MAX)
Declare @date11 datetime
Declare @date12 datetime
Declare @date13 datetime
Declare @date14 datetime
Declare @date15 datetime
Declare @date16 datetime
Declare @date17 datetime
Declare @date18 datetime
Declare @date19 datetime
set @date11='9/26/2010'
set @date12='9/27/2010'
set @date13='9/28/2010'
set @date14='9/29/2010'
set @date15='9/30/2010'
set @date16='10/1/2010'
set @date17='10/16/2010'
set @date18='10/17/2010'
set @date19='10/24/2010'
BEGIN
SET @query = 'SELECT [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +
' FROM
(Select empname,atndate,startTime from TestTable where atndate>=' + CONVERT(VARCHAR,@date11,101) + ' and
atndate<=' + CONVERT(VARCHAR,@date17,101) + ' and empname not in
(Select empname from TestTable where atndate >=' + CONVERT(VARCHAR,@date18,101) + ' and atndate<=' + CONVERT(VARCHAR,@date19,101) + '
group by empname)
)
AS SourceTable
PIVOT
(
max(startTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')
) AS PivotTable'
exec(@query)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 10:56:06

are you sure you're having desired data in tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 11:03:28

Hi,

This is my data.

I am running this query i am getting result.

Select empname,atndate,starttime +'-'+Endtime as EmpTime from TestTable
where atndate>='09/26/2010' and atndate<='10/16/2010'
and empname not in
(Select empname from TestTable where atndate>= '10/17/2010' and atndate<='10/24/2010'
group by empname)

Same query i implemented in stored procedure i am not getting result nor errro.Simply i am getting column headers.

CREATE TABLE TestTable(EmpNo int,EmpName varchar(20),
atndate DATETIME,
StartTime varchar(50),EndTime varchar(50),MatchingOff varchar(20),CampaignID int
)

SET IDENTITY_INSERT mytable ON
Insert into TestTable(EmpNo,EmpName,atndate,StartTime,EndTime,MatchingOff,CampaignID)

select '3','NareshNadimpalli','10/3/2010','0','0','Off','3' union all
select '3','NareshNadimpalli','10/4/2010','7:30 AM','6:00 PM','07:30 ','3' union all
select '3','NareshNadimpalli','10/5/2010','0','0','Off','3' union all
select '3','NareshNadimpalli','10/6/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/7/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/8/2010','7:30 AM','7:30 AM','07:30 ','3' union all
select '3','NareshNadimpalli','10/9/2010','7:30 AM','7:30 AM','07:30 ','3' union all

select '2','ALETARIDINGER','10/3/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/4/2010 12:00:00 AM','7:30 AM','6:00 PM','07:30 ','1' union all
select '2','ALETARIDINGER','10/5/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/6/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/7/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/8/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/9/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all

select '2','ALETARIDINGER','10/17/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/18/2010 12:00:00 AM','7:30 AM','6:00 PM','07:30 ','1' union all
select '2','ALETARIDINGER','10/19/2010 12:00:00 AM','0','0','Off','1' union all
select '2','ALETARIDINGER','10/20/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/21/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all
select '2','ALETARIDINGER','10/22/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1' union all

select '2','ALETARIDINGER','10/23/2010 12:00:00 AM','7:30 AM','7:30 AM','07:30 ','1'

Stored Procedure

alter PROCEDURE [dbo].[Test](@date1 DATETIME,@CampaignID int)
AS
DECLARE @query VARCHAR(MAX)
Declare @date11 datetime
Declare @date12 datetime
Declare @date13 datetime
Declare @date14 datetime
Declare @date15 datetime
Declare @date16 datetime
Declare @date17 datetime
Declare @date18 datetime
Declare @date19 datetime
set @date11='9/26/2010'
set @date12='9/27/2010'
set @date13='9/28/2010'
set @date14='9/29/2010'
set @date15='9/30/2010'
set @date16='10/1/2010'
set @date17='10/16/2010'
set @date18='10/17/2010'
set @date19='10/24/2010'
BEGIN
SET @query = 'SELECT [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +
' FROM
(Select empname,atndate,startTime from TestTable where atndate>=' + CONVERT(VARCHAR,@date11,101) + ' and
atndate<=' + CONVERT(VARCHAR,@date17,101) + ' and empname not in
(Select empname from TestTable where atndate >=' + CONVERT(VARCHAR,@date18,101) + ' and atndate<=' + CONVERT(VARCHAR,@date19,101) + '
group by empname)
)
AS SourceTable
PIVOT
(
max(startTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')
) AS PivotTable'
exec(@query)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:06:33

please use query as suggested. you're missing '' before and after date parameters in sql string

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 11:19:34

Hi,

I am using quotation for dates i am getting error

Msg 207, Level 16, State 1, Line 2
Invalid column name '09/26/2010'.
Msg 207, Level 16, State 1, Line 3
Invalid column name '10/16/2010'.
Msg 207, Level 16, State 1, Line 4
Invalid column name '10/17/2010'.
Msg 207, Level 16, State 1, Line 4
Invalid column name '10/24/2010'.

alter PROCEDURE [dbo].[Test](@date1 DATETIME,@CampaignID int)
AS
DECLARE @query VARCHAR(MAX)
Declare @date11 datetime
Declare @date12 datetime
Declare @date13 datetime
Declare @date14 datetime
Declare @date15 datetime
Declare @date16 datetime
Declare @date17 datetime
Declare @date18 datetime
Declare @date19 datetime
set @date11='9/26/2010'
set @date12='9/27/2010'
set @date13='9/28/2010'
set @date14='9/29/2010'
set @date15='9/30/2010'
set @date16='10/1/2010'
set @date17='10/16/2010'
set @date18='10/17/2010'
set @date19='10/24/2010'
BEGIN
SET @query = 'SELECT [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +
' FROM
(Select empname,atndate,startTime from TestTable where atndate>=[' + CONVERT(VARCHAR,@date11,101) + '] and
atndate<=[' + CONVERT(VARCHAR,@date17,101) + '] and empname not in
(Select empname from TestTable where atndate >=[' + CONVERT(VARCHAR,@date18,101) + '] and atndate<=[' + CONVERT(VARCHAR,@date19,101) + ']
group by empname)
)
AS SourceTable
PIVOT
(
max(startTime)
FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')
) AS PivotTable'
exec(@query)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:25:14

still not the same i posted. please copy suggestion given at 11/18/2010 : 09:28:50 and execute

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 11:40:56

Hi Visakh,

Thank u very much.Its working.

Can u tell me how to add EmpName in select statement.

Here i need to add EmpName in the select statement.
SET @query = 'SELECT [' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:46:09

[code]SET @query = 'SELECT EmpName,[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101) + '], [' + CONVERT(VARCHAR,@date13,101) + '], [' + CONVERT(VARCHAR,@date14,101) + '], [' + CONVERT(VARCHAR,@date15,101) + '], [' + CONVERT(VARCHAR,@date16,101)+ '], [' + CONVERT(VARCHAR,@date17,101) + ']' +
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 11:53:44

Hi Visakh,

Thank u Very much.

One Final doubt right now i am displaying 1 week data but i have 3 weeks data can i use same storedprocedure for 3 weeks data i.e 21 dates instead 7 dates.

Here i need to pass 21 dates(First 3 week dates)

FOR atndate IN (' + '[' + CONVERT(VARCHAR,@date11,101) + '], [' + CONVERT(VARCHAR,@date12,101)+ '], [' + CONVERT(VARCHAR,@date13,101)+ '], [' + CONVERT(VARCHAR,@date14,101)+ '], [' + CONVERT(VARCHAR,@date15,101)+ '], [' + CONVERT(VARCHAR,@date16,101)+ '],[' + CONVERT(VARCHAR,@date17,101) + ']' + ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:57:02

yup. you can. but you need to pass all the 21 dates inside PIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 12:01:51

Hi Visakh,

I think if i use 21 dates in same stored procedure it will be difficult to display it in front end.Can i use seperate stored procedure for 3 weeks or else single stored procedure for all 3 weeks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:10:24

What determines dates you want to display? why do you need to send them all via params? cant you generate it within proc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 12:19:52

Hi,

How to do that within stored procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:26:10

whats are these dates? can you tell me how you determine what dates you want to pivot on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

naresh0407
Starting Member

30 Posts

Posted - 2010-11-18 : 12:32:15

Hi,

Here i am passsing 1 weeks dates insead i will pass 3 weeks dates.

Select empname,atndate,startTime from TestTable where atndate>=[' + CONVERT(VARCHAR,@date11,101) + '] and
atndate<=[' + CONVERT(VARCHAR,@date17,101) + '] and empname not in

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources