Utilise Robvolk's crosstab code, but fail, help!

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

SQL Server 2000 Forums

SQL Server Development (2000)

Utilise Robvolk's crosstab code, but fail, help!

Author

Topic

raffiq_eddy
Starting Member

12 Posts

Posted - 2006-07-03 : 02:34:07

Hi,

I found & try to utilise excelent code by Robvolk, but fail! (http://www.sqlteam.com/item.asp?ItemID=2955) -- I want the output like this:


        |Apr|Apr|Apr|Apr|Apr|Apr|Apr|Apr|May|May|May|May|
StaffNo |23 |24 |25 |26 |27 |28 |29 |30 |01 |02 |03 |04..
---------------------------------------------------------
  1     |AL |AL |AL |AL |   |  ...  |EL |EL |EL |
  2     |   |MC |MC |   |   |
  3     |   |   |   |EL |EL |

I've tried as following


EXECUTE crosstab 
'SELECT TblLeaveDetail.StaffNo,
        TblLeaveDetail.LeaveType
FROM TblLeaveDetail,
     TblParameter,
     TblCalendar
WHERE (TblLeaveDetail.StartDate BETWEEN TblParameter.DateFrom AND TblParameter.DateTo) OR
      (TblLeaveDetail.EndDate   BETWEEN TblParameter.DateFrom AND TblParameter.DateTo)
GROUP BY LeaveDetail.StaffNo,
         LeaveDetail.LeaveType',
'max(TblLeaveDetail.LeaveType)',
'CalDate',
'TblCalendar'

But it produced error as following:


Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

The data was from table "TblLeaveDetail"


StaffNo | StartDate  | EndDate    | LeaveType |
1       | 23/04/2006 | 26/04/2006 | AL        |
2       | 24/04/2006 | 25/04/2006 | MC        |
3       | 26/04/2006 | 27/04/2006 | EL        |
1       | 30/04/2006 | 02/05/2006 | EL        |

I used table "TblCalendar" to produce the pivot column, it contains info as following:


CalDate
4/1/2006
4/2/2006
4/3/2006
4/4/2006
..
..
5/1/2006
5/2/2006
5/3/2006
5/4/2006
..
..

Parameter:
Date From '4/24/2006' to '5/23/2006'

Can anyone help me on this?

TIA
Regards.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-03 : 03:07:29

follow the code,

you closed the string before including the other part of the string...

quote:

LeaveDetail.LeaveType',
'max(TblLeaveDetail.LeaveType)',
[CalDate],
[TblCalendar]'

--------------------
keeping it simple...

raffiq_eddy
Starting Member

12 Posts

Posted - 2006-07-03 : 04:47:51

I try to fix it, but still the same, I don't think that was the problem

quote:
Originally posted by jen

follow the code,

you closed the string before including the other part of the string...

[quote]
LeaveDetail.LeaveType',
'max(TblLeaveDetail.LeaveType)',
[CalDate],
[TblCalendar]'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 06:05:40

[code]EXECUTE crosstab
'SELECT TblLeaveDetail.StaffNo,
TblLeaveDetail.LeaveType
FROM TblLeaveDetail,
TblParameter,
TblCalendar
WHERE (TblLeaveDetail.StartDate BETWEEN TblParameter.DateFrom AND TblParameter.DateTo) OR
(TblLeaveDetail.EndDate BETWEEN TblParameter.DateFrom AND TblParameter.DateTo)
GROUP BY TblLeaveDetail.StaffNo,
TblLeaveDetail.LeaveType',
'max(TblLeaveDetail.LeaveType)',
'CalDate',
'TblCalendar'[/code]

If you still have problem, I suggest you post your table DDL with some sample data. It is a bit hard for us to help without the table DDL

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-03 : 21:43:08

why don't you try to execute the dsql as tsql and then if the query works, build your dsql from there

--------------------
keeping it simple...

raffiq_eddy
Starting Member

12 Posts

Posted - 2006-07-04 : 00:55:32

OK, I'll try to prepare it soon

quote:
Originally posted by khtan

If you still have problem, I suggest you post your table DDL with some sample data. It is a bit hard for us to help without the table DDL

KH

raffiq_eddy
Starting Member

12 Posts

Posted - 2006-07-05 : 01:44:41

khtan, sorry for the delay, I hope this is what u need


if exists (select * from sysobjects where id = object_id(N'[dbo].[TblCalendar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TblCalendar]
GO

CREATE TABLE [TblCalendar]
( [CalDate] datetime NOT NULL,
  PRIMARY KEY  ([CalDate])
)
GO

Declare @year int, @dt smalldatetime
Set @year=2006
Set @dt = Cast(@year As char(4)) + '-01-01'

While Year(@dt) = @year
 Begin
  Insert TblCalendar Values(@dt)
  Set @dt = @dt + 1
 End 

if exists (select * from sysobjects where id = object_id(N'[dbo].[TblLeaveDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TblLeaveDetail]
GO

CREATE TABLE [TblLeaveDetail] (
	[LeaveDetailNo] [int],
	[StaffNo] [int] NOT NULL ,
	[LeaveTypeNo] [int] NOT NULL ,
	[StartDate] [datetime] NOT NULL ,
	[EndDate] [datetime] NOT NULL,
  PRIMARY KEY  ([LeaveDetailNo])
)
go

INSERT INTO TblLeaveDetail VALUES (1, 1, 1, '23-04-2006', '26-04-2006');
INSERT INTO TblLeaveDetail VALUES (2, 1, 2, '30-04-2006', '02-05-2006');
INSERT INTO TblLeaveDetail VALUES (3, 2, 3, '24-04-2006', '25-04-2006');
INSERT INTO TblLeaveDetail VALUES (4, 3, 2, '26-04-2006', '27-04-2006');

if exists (select * from sysobjects where id = object_id(N'[dbo].[TblParameter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TblParameter]
GO

CREATE TABLE [TblParameter] (
	[DateFrom] [datetime] NULL ,
	[DateTo] [datetime] NULL 
)
go

INSERT INTO TblParameter VALUES ('24-04-2006', '23-05-2006');

quote:
Originally posted by khtan

If you still have problem, I suggest you post your table DDL with some sample data. It is a bit hard for us to help without the table DDL

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 02:23:07

You hit the limit if varchar(8000) use in the Stored Procedure to form the Dynamic SQL.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 02:24:08

Are you using any Reporting Tools ? Why not do this in your front end Reporting Tool

raffiq_eddy
Starting Member

12 Posts

Posted - 2006-07-05 : 02:54:17

I used cyrstal report 7 (sorry, this going to be a off topic)... but the report has limitation, that why I decided to try robvolk's code as alternative to it!

but look like I back to square 1, hmm....