Attendance Report generation - SQL Server Forums

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

Attendance Report generation

Author

Topic

surezh
Starting Member

32 Posts

Posted - 2009-01-04 : 23:45:47

i have created a databse table in MSSQL 2000 like this

[empcode] [leave_date] [type] [reason]
100 2008-12-29 00:00:00.000 T Tour
100 2008-12-30 00:00:00.000 T Tour
101 2008-12-31 00:00:00.000 CL Casual Leave
102 2009-01-01 00:00:00.000 R Restricted holiday
100 2009-01-02 00:00:00.000 T Tour

This table contains only leave details....
but i need to create monthly attendance report such as below

empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 .............
100 P P P P S P T CL P P P S P P T ............
101 P T R R S R R T CL P P S P P P..............
102 P P P P S P P P P T T S CL P P P............

P-present
T-Tour
CL- causal leave
R- Restricted holiday
S-sunday

is there any way in SQL query to get the report like that.....

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 02:32:48

SELECT empCode,
MAX(CASE WHEN DATEPART(DAY, leave_date) = 1 THEN [type] ELSE NULL END) AS [1],
MAX(CASE WHEN DATEPART(DAY, leave_date) = 2 THEN [type] ELSE NULL END) AS [2],
...
MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END) AS [31]
FROM Table1
GROUP BY empCode
ORDER BY empCode

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 04:54:03

CREATE TABLE #Temp
(
[empcode] int,
[leave_date] datetime,
[type] char(2),
[reason] varchar(50)
)
DECLARE @Start datetime,@End datetime,@DList varchar(2000),@Sql varchar(max)


INSERT INTO #Temp
SELECT 100, '2008-12-29 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 100, '2008-12-30 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2008-12-31 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-01 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-02 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2009-01-10 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-11 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-12 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2009-01-13 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-12 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-08 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 100, '2009-01-12 00:00:00.000', 'T', 'Tour'  

SELECT @Start=MIN(leave_date),@End=MAX(leave_date)
FROM #Temp

SELECT DATEADD(dd,number,@Start) AS Date INTO #Date
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@Start)<=@End



SELECT @DList=LEFT(dl.Dates,LEN(dl.Dates)-1)
FROM
(SELECT CAST(Date as varchar(11)) + ','
 FROM #Date
 FOR XML PATH('')
)dl(Dates)

SET @Sql='SELECT *
FROM
(
SELECT d.Date,t.empcode,t.type
FROM #Date d
LEFT JOIN #Temp t
ON t.leave_date=d.Date
)t
PIVOT (MAX(type) FOR Date IN ([' +  REPLACE(@DList,',','],[')+ ']))p
WHERE empcode IS NOT NULL'

EXEC(@Sql)

DROP TABLE #Temp

DROP TABLE #Date


output
----------------------------------
empcode	Dec 29 2008	Dec 30 2008	Dec 31 2008	Jan  1 2009	Jan  2 2009	Jan  3 2009	Jan  4 2009	Jan  5 2009	Jan  6 2009	Jan  7 2009	Jan  8 2009	Jan  9 2009	Jan 10 2009	Jan 11 2009	Jan 12 2009	Jan 13 2009
100	T 	T 	NULL	NULL	T 	NULL	NULL	NULL	NULL	NULL	T 	NULL	NULL	NULL	T 	NULL
101	NULL	NULL	CL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	CL	NULL	NULL	CL
102	NULL	NULL	NULL	R 	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	R 	R 	NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 05:25:04

And as with almost all dynamic SQL, beware of SQL injection.

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 05:58:08

quote:

Originally posted by visakh16

CREATE TABLE #Temp
(
[empcode] int,
[leave_date] datetime,
[type] char(2),
[reason] varchar(50)
)
DECLARE @Start datetime,@End datetime,@DList varchar(2000),@Sql varchar(max)


INSERT INTO #Temp
SELECT 100, '2008-12-29 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 100, '2008-12-30 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2008-12-31 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-01 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-02 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2009-01-10 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-11 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-12 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2009-01-13 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-12 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-08 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 100, '2009-01-12 00:00:00.000', 'T', 'Tour'  

SELECT @Start=MIN(leave_date),@End=MAX(leave_date)
FROM #Temp

SELECT DATEADD(dd,number,@Start) AS Date INTO #Date
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@Start)<=@End



SELECT @DList=LEFT(dl.Dates,LEN(dl.Dates)-1)
FROM
(SELECT CAST(Date as varchar(11)) + ','
 FROM #Date
 FOR XML PATH('')
)dl(Dates)

SET @Sql='SELECT *
FROM
(
SELECT d.Date,t.empcode,t.type
FROM #Date d
LEFT JOIN #Temp t
ON t.leave_date=d.Date
)t
PIVOT (MAX(type) FOR Date IN ([' +  REPLACE(@DList,',','],[')+ ']))p
WHERE empcode IS NOT NULL'

EXEC(@Sql)

DROP TABLE #Temp

DROP TABLE #Date


output
----------------------------------
empcode	Dec 29 2008	Dec 30 2008	Dec 31 2008	Jan  1 2009	Jan  2 2009	Jan  3 2009	Jan  4 2009	Jan  5 2009	Jan  6 2009	Jan  7 2009	Jan  8 2009	Jan  9 2009	Jan 10 2009	Jan 11 2009	Jan 12 2009	Jan 13 2009
100	T 	T 	NULL	NULL	T 	NULL	NULL	NULL	NULL	NULL	T 	NULL	NULL	NULL	T 	NULL
101	NULL	NULL	CL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	CL	NULL	NULL	CL
102	NULL	NULL	NULL	R 	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	R 	R 	NULL

I am using MSSql 2000.....
PIVOT function is not working.... I am getting error message as below....

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'max'.
Server: Msg 170, Level 15, State 1, Line 39
Line 39: Incorrect syntax near 'XML'.
Server: Msg 137, Level 15, State 1, Line 53
Must declare the variable '@Sql'.
Server: Msg 137, Level 15, State 1, Line 53
Must declare the variable '@Sql'.

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 06:02:58

quote:
Originally posted by surezh

i have created a databse table in MSSQL 2000 like this

[empcode] [leave_date] [type] [reason]
100 2008-12-29 00:00:00.000 T Tour
100 2008-12-30 00:00:00.000 T Tour
101 2008-12-31 00:00:00.000 CL Casual Leave
102 2009-01-01 00:00:00.000 R Restricted holiday
100 2009-01-02 00:00:00.000 T Tour

This table contains only leave details....
but i need to create monthly attendance report such as below

empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 .............
100 P P P P S P T CL P P P S P P T ............
101 P T R R S R R T CL P P S P P P..............
102 P P P P S P P P P T T S CL P P P............

P-present
T-Tour
CL- causal leave
R- Restricted holiday
S-sunday

is there any way in SQL query to get the report like that.....

Thanks

i have to get those data between two given dates and sunday should be shown as 'S' and rest of the leave days should shown as 'P'

Thanks

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 06:08:58

quote:
Originally posted by Peso

SELECT empCode,
MAX(CASE WHEN DATEPART(DAY, leave_date) = 1 THEN [type] ELSE NULL END) AS [1],
MAX(CASE WHEN DATEPART(DAY, leave_date) = 2 THEN [type] ELSE NULL END) AS [2],
...
MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END) AS [31]
FROM Table1
GROUP BY empCode
ORDER BY empCode

E 12°55'05.63"
N 56°04'39.26"

i have to get those data between two given dates and sunday should be shown as 'S' and rest of the leave days should shown as 'P'

Then it also has to show the statics such as

empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 P CL R

100 P P P P S P T CL P P P S P P T 10 1 0
101 P T R R S R R T CL P P S P P P 8 1 2
102 P P P P S P P P P T T S CL P P P 10 1 0

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 06:10:16

[code]SELECT empCode,
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 01 THEN [type] ELSE NULL END), 'P') AS [01],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 02 THEN [type] ELSE NULL END), 'P') AS [02],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 03 THEN [type] ELSE NULL END), 'P') AS [03],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 04 THEN [type] ELSE NULL END), 'P') AS [04],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 05 THEN [type] ELSE NULL END), 'P') AS [05],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 06 THEN [type] ELSE NULL END), 'P') AS [06],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 07 THEN [type] ELSE NULL END), 'P') AS [07],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 08 THEN [type] ELSE NULL END), 'P') AS [08],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 09 THEN [type] ELSE NULL END), 'P') AS [09],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 10 THEN [type] ELSE NULL END), 'P') AS [10],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 11 THEN [type] ELSE NULL END), 'P') AS [11],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 12 THEN [type] ELSE NULL END), 'P') AS [12],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 13 THEN [type] ELSE NULL END), 'P') AS [13],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 14 THEN [type] ELSE NULL END), 'P') AS [14],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 15 THEN [type] ELSE NULL END), 'P') AS [15],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 16 THEN [type] ELSE NULL END), 'P') AS [16],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 17 THEN [type] ELSE NULL END), 'P') AS [17],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 18 THEN [type] ELSE NULL END), 'P') AS [18],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 19 THEN [type] ELSE NULL END), 'P') AS [19],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 20 THEN [type] ELSE NULL END), 'P') AS [20],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 21 THEN [type] ELSE NULL END), 'P') AS [21],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 22 THEN [type] ELSE NULL END), 'P') AS [22],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 23 THEN [type] ELSE NULL END), 'P') AS [23],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 24 THEN [type] ELSE NULL END), 'P') AS [24],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 25 THEN [type] ELSE NULL END), 'P') AS [25],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 26 THEN [type] ELSE NULL END), 'P') AS [26],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 27 THEN [type] ELSE NULL END), 'P') AS [27],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 28 THEN [type] ELSE NULL END), 'P') AS [28],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 29 THEN [type] ELSE NULL END), 'P') AS [29],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 30 THEN [type] ELSE NULL END), 'P') AS [30],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END), 'P') AS [31]
FROM Table1
WHERE leave_date >= '2009-01-01'
AND leave_date < '2009-02-01'
GROUP BY empCode
ORDER BY empCode[/code]

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 06:19:00

quote:

Originally posted by Peso

SELECT		empCode,
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 01 THEN [type] ELSE NULL END), 'P') AS [01],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 02 THEN [type] ELSE NULL END), 'P') AS [02],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 03 THEN [type] ELSE NULL END), 'P') AS [03],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 04 THEN [type] ELSE NULL END), 'P') AS [04],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 05 THEN [type] ELSE NULL END), 'P') AS [05],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 06 THEN [type] ELSE NULL END), 'P') AS [06],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 07 THEN [type] ELSE NULL END), 'P') AS [07],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 08 THEN [type] ELSE NULL END), 'P') AS [08],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 09 THEN [type] ELSE NULL END), 'P') AS [09],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 10 THEN [type] ELSE NULL END), 'P') AS [10],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 11 THEN [type] ELSE NULL END), 'P') AS [11],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 12 THEN [type] ELSE NULL END), 'P') AS [12],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 13 THEN [type] ELSE NULL END), 'P') AS [13],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 14 THEN [type] ELSE NULL END), 'P') AS [14],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 15 THEN [type] ELSE NULL END), 'P') AS [15],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 16 THEN [type] ELSE NULL END), 'P') AS [16],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 17 THEN [type] ELSE NULL END), 'P') AS [17],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 18 THEN [type] ELSE NULL END), 'P') AS [18],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 19 THEN [type] ELSE NULL END), 'P') AS [19],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 20 THEN [type] ELSE NULL END), 'P') AS [20],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 21 THEN [type] ELSE NULL END), 'P') AS [21],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 22 THEN [type] ELSE NULL END), 'P') AS [22],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 23 THEN [type] ELSE NULL END), 'P') AS [23],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 24 THEN [type] ELSE NULL END), 'P') AS [24],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 25 THEN [type] ELSE NULL END), 'P') AS [25],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 26 THEN [type] ELSE NULL END), 'P') AS [26],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 27 THEN [type] ELSE NULL END), 'P') AS [27],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 28 THEN [type] ELSE NULL END), 'P') AS [28],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 29 THEN [type] ELSE NULL END), 'P') AS [29],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 30 THEN [type] ELSE NULL END), 'P') AS [30],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END), 'P') AS [31]
FROM		Table1
WHERE		leave_date >= '2009-01-01'
		AND leave_date < '2009-02-01'
GROUP BY	empCode
ORDER BY	empCode

E 12°55'05.63"
N 56°04'39.26"

this code contains fixed dates i.e from 01 to 31
i want choose the two dates..

i also want the statics such as P=20,CL=2,R=2,T=5,S=4 for each employee......

please help me.....

Thak you for the reply Peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 06:37:21

[code]CREATE PROCEDURE dbo.uspTwitSP
(
@FromDate DATETIME,
@ToDate DATETIME
AS

SET NOCOUNT ON

SELECT empCode,
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 01 THEN [type] ELSE NULL END), 'P') AS [01],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 02 THEN [type] ELSE NULL END), 'P') AS [02],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 03 THEN [type] ELSE NULL END), 'P') AS [03],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 04 THEN [type] ELSE NULL END), 'P') AS [04],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 05 THEN [type] ELSE NULL END), 'P') AS [05],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 06 THEN [type] ELSE NULL END), 'P') AS [06],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 07 THEN [type] ELSE NULL END), 'P') AS [07],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 08 THEN [type] ELSE NULL END), 'P') AS [08],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 09 THEN [type] ELSE NULL END), 'P') AS [09],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 10 THEN [type] ELSE NULL END), 'P') AS [10],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 11 THEN [type] ELSE NULL END), 'P') AS [11],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 12 THEN [type] ELSE NULL END), 'P') AS [12],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 13 THEN [type] ELSE NULL END), 'P') AS [13],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 14 THEN [type] ELSE NULL END), 'P') AS [14],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 15 THEN [type] ELSE NULL END), 'P') AS [15],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 16 THEN [type] ELSE NULL END), 'P') AS [16],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 17 THEN [type] ELSE NULL END), 'P') AS [17],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 18 THEN [type] ELSE NULL END), 'P') AS [18],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 19 THEN [type] ELSE NULL END), 'P') AS [19],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 20 THEN [type] ELSE NULL END), 'P') AS [20],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 21 THEN [type] ELSE NULL END), 'P') AS [21],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 22 THEN [type] ELSE NULL END), 'P') AS [22],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 23 THEN [type] ELSE NULL END), 'P') AS [23],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 24 THEN [type] ELSE NULL END), 'P') AS [24],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 25 THEN [type] ELSE NULL END), 'P') AS [25],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 26 THEN [type] ELSE NULL END), 'P') AS [26],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 27 THEN [type] ELSE NULL END), 'P') AS [27],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 28 THEN [type] ELSE NULL END), 'P') AS [28],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 29 THEN [type] ELSE NULL END), 'P') AS [29],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 30 THEN [type] ELSE NULL END), 'P') AS [30],
COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END), 'P') AS [31],
1 + DATEDIFF(DAY, @FromDate, @ToDate) - SUM(CASE WHEN [type] IN ('CL', 'R', 'T', 'S' THEN 1 ELSE 0 END) AS P,
SUM(CASE WHEN [type] = 'CL' THEN 1 ELSE 0 END) AS CL,
SUM(CASE WHEN [type] = 'R' THEN 1 ELSE 0 END) AS R,
SUM(CASE WHEN [type] = 'T' THEN 1 ELSE 0 END) AS T,
SUM(CASE WHEN [type] = 'S' THEN 1 ELSE 0 END) AS S
INTO #Temp
FROM Table1
WHERE leave_date >= @FromDate
AND leave_date < DATEADD(DAY, 1, @ToDate)
GROUP BY empCode
ORDER BY empCode

DECLARE @Day TINYINT,
@SQL VARCHAR(1000)

SET @Day = 1

WHILE @Day <= 31
BEGIN
IF @Day BETWEEN DATEPART(DAY, @FromDate) AND DATEPART(DAY, @ToDate)
SET @SQL = ''
ELSE
SET @SQL = 'ALTER TABLE #Temp DROP COLUMN [' + REPLACE(STR(@Day, 2), ' ', '0') + ']'

EXEC (@SQL)

SET @Day = @Day + 1
END

SELECT *
FROM #Temp

DROP TABLE #Temp[/code]

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 06:51:39

quote:

Originally posted by Peso

CREATE PROCEDURE dbo.uspTwitSP
(
	@FromDate DATETIME,
	@ToDate DATETIME
AS

SET NOCOUNT ON

SELECT		empCode,
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 01 THEN [type] ELSE NULL END), 'P') AS [01],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 02 THEN [type] ELSE NULL END), 'P') AS [02],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 03 THEN [type] ELSE NULL END), 'P') AS [03],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 04 THEN [type] ELSE NULL END), 'P') AS [04],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 05 THEN [type] ELSE NULL END), 'P') AS [05],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 06 THEN [type] ELSE NULL END), 'P') AS [06],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 07 THEN [type] ELSE NULL END), 'P') AS [07],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 08 THEN [type] ELSE NULL END), 'P') AS [08],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 09 THEN [type] ELSE NULL END), 'P') AS [09],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 10 THEN [type] ELSE NULL END), 'P') AS [10],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 11 THEN [type] ELSE NULL END), 'P') AS [11],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 12 THEN [type] ELSE NULL END), 'P') AS [12],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 13 THEN [type] ELSE NULL END), 'P') AS [13],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 14 THEN [type] ELSE NULL END), 'P') AS [14],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 15 THEN [type] ELSE NULL END), 'P') AS [15],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 16 THEN [type] ELSE NULL END), 'P') AS [16],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 17 THEN [type] ELSE NULL END), 'P') AS [17],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 18 THEN [type] ELSE NULL END), 'P') AS [18],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 19 THEN [type] ELSE NULL END), 'P') AS [19],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 20 THEN [type] ELSE NULL END), 'P') AS [20],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 21 THEN [type] ELSE NULL END), 'P') AS [21],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 22 THEN [type] ELSE NULL END), 'P') AS [22],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 23 THEN [type] ELSE NULL END), 'P') AS [23],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 24 THEN [type] ELSE NULL END), 'P') AS [24],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 25 THEN [type] ELSE NULL END), 'P') AS [25],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 26 THEN [type] ELSE NULL END), 'P') AS [26],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 27 THEN [type] ELSE NULL END), 'P') AS [27],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 28 THEN [type] ELSE NULL END), 'P') AS [28],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 29 THEN [type] ELSE NULL END), 'P') AS [29],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 30 THEN [type] ELSE NULL END), 'P') AS [30],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END), 'P') AS [31],
		1 + DATEDIFF(DAY, @FromDate, @ToDate) - SUM(CASE WHEN [type] IN ('CL', 'R', 'T', 'S' THEN 1 ELSE 0 END) AS P,
		SUM(CASE WHEN [type] = 'CL' THEN 1 ELSE 0 END) AS CL,
		SUM(CASE WHEN [type] = 'R' THEN 1 ELSE 0 END) AS R,
		SUM(CASE WHEN [type] = 'T' THEN 1 ELSE 0 END) AS T,
		SUM(CASE WHEN [type] = 'S' THEN 1 ELSE 0 END) AS S
INTO		#Temp
FROM		Table1
WHERE		leave_date >= @FromDate
		AND leave_date < DATEADD(DAY, 1, @ToDate)
GROUP BY	empCode
ORDER BY	empCode

DECLARE	@Day TINYINT,
	@SQL VARCHAR(1000)

SET	@Day = 1

WHILE @Day <= 31
	BEGIN
		IF @Day BETWEEN DATEPART(DAY, @FromDate) AND DATEPART(DAY, @ToDate)
			SET	@SQL = ''
		ELSE
			SET	@SQL = 'ALTER TABLE #Temp DROP COLUMN [' + REPLACE(STR(@Day, 2), ' ', '0') + ']'

		EXEC	(@SQL)

		SET	@Day = @Day + 1
	END

SELECT	*
FROM	#Temp

DROP TABLE	#Temp

E 12°55'05.63"
N 56°04'39.26"

In this code i can select the date between for leave_date only....
i want generate a attendance report for the period between two dates..
for example attendance report between '21/12/2008' to '20/01/2009'

please help me to find out the solution..
Thanks for the reply PESO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 06:58:58

The your best bet is to use this code

ALTER PROCEDURE dbo.uspTwitSP
(
	@FromDate DATETIME,
	@ToDate DATETIME
AS

SET NOCOUNT ON

SELECT		empCode,
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 01 THEN [type] ELSE NULL END), 'P') AS [01],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 02 THEN [type] ELSE NULL END), 'P') AS [02],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 03 THEN [type] ELSE NULL END), 'P') AS [03],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 04 THEN [type] ELSE NULL END), 'P') AS [04],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 05 THEN [type] ELSE NULL END), 'P') AS [05],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 06 THEN [type] ELSE NULL END), 'P') AS [06],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 07 THEN [type] ELSE NULL END), 'P') AS [07],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 08 THEN [type] ELSE NULL END), 'P') AS [08],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 09 THEN [type] ELSE NULL END), 'P') AS [09],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 10 THEN [type] ELSE NULL END), 'P') AS [10],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 11 THEN [type] ELSE NULL END), 'P') AS [11],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 12 THEN [type] ELSE NULL END), 'P') AS [12],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 13 THEN [type] ELSE NULL END), 'P') AS [13],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 14 THEN [type] ELSE NULL END), 'P') AS [14],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 15 THEN [type] ELSE NULL END), 'P') AS [15],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 16 THEN [type] ELSE NULL END), 'P') AS [16],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 17 THEN [type] ELSE NULL END), 'P') AS [17],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 18 THEN [type] ELSE NULL END), 'P') AS [18],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 19 THEN [type] ELSE NULL END), 'P') AS [19],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 20 THEN [type] ELSE NULL END), 'P') AS [20],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 21 THEN [type] ELSE NULL END), 'P') AS [21],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 22 THEN [type] ELSE NULL END), 'P') AS [22],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 23 THEN [type] ELSE NULL END), 'P') AS [23],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 24 THEN [type] ELSE NULL END), 'P') AS [24],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 25 THEN [type] ELSE NULL END), 'P') AS [25],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 26 THEN [type] ELSE NULL END), 'P') AS [26],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 27 THEN [type] ELSE NULL END), 'P') AS [27],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 28 THEN [type] ELSE NULL END), 'P') AS [28],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 29 THEN [type] ELSE NULL END), 'P') AS [29],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 30 THEN [type] ELSE NULL END), 'P') AS [30],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END), 'P') AS [31],
		1 + DATEDIFF(DAY, @FromDate, @ToDate) - SUM(CASE WHEN [type] IN ('CL', 'R', 'T', 'S' THEN 1 ELSE 0 END) AS P,
		SUM(CASE WHEN [type] = 'CL' THEN 1 ELSE 0 END) AS CL,
		SUM(CASE WHEN [type] = 'R' THEN 1 ELSE 0 END) AS R,
		SUM(CASE WHEN [type] = 'T' THEN 1 ELSE 0 END) AS T,
		SUM(CASE WHEN [type] = 'S' THEN 1 ELSE 0 END) AS S
FROM		Table1
WHERE		leave_date >= @FromDate
		AND leave_date < DATEADD(DAY, 1, @ToDate)
GROUP BY	empCode
ORDER BY	empCode

and only use the colums you need in your presentation layer.

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 07:19:36

quote:

Originally posted by Peso

The your best bet is to use this code

ALTER PROCEDURE dbo.uspTwitSP
(
	@FromDate DATETIME,
	@ToDate DATETIME
AS

SET NOCOUNT ON

SELECT		empCode,
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 01 THEN [type] ELSE NULL END), 'P') AS [01],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 02 THEN [type] ELSE NULL END), 'P') AS [02],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 03 THEN [type] ELSE NULL END), 'P') AS [03],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 04 THEN [type] ELSE NULL END), 'P') AS [04],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 05 THEN [type] ELSE NULL END), 'P') AS [05],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 06 THEN [type] ELSE NULL END), 'P') AS [06],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 07 THEN [type] ELSE NULL END), 'P') AS [07],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 08 THEN [type] ELSE NULL END), 'P') AS [08],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 09 THEN [type] ELSE NULL END), 'P') AS [09],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 10 THEN [type] ELSE NULL END), 'P') AS [10],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 11 THEN [type] ELSE NULL END), 'P') AS [11],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 12 THEN [type] ELSE NULL END), 'P') AS [12],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 13 THEN [type] ELSE NULL END), 'P') AS [13],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 14 THEN [type] ELSE NULL END), 'P') AS [14],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 15 THEN [type] ELSE NULL END), 'P') AS [15],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 16 THEN [type] ELSE NULL END), 'P') AS [16],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 17 THEN [type] ELSE NULL END), 'P') AS [17],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 18 THEN [type] ELSE NULL END), 'P') AS [18],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 19 THEN [type] ELSE NULL END), 'P') AS [19],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 20 THEN [type] ELSE NULL END), 'P') AS [20],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 21 THEN [type] ELSE NULL END), 'P') AS [21],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 22 THEN [type] ELSE NULL END), 'P') AS [22],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 23 THEN [type] ELSE NULL END), 'P') AS [23],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 24 THEN [type] ELSE NULL END), 'P') AS [24],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 25 THEN [type] ELSE NULL END), 'P') AS [25],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 26 THEN [type] ELSE NULL END), 'P') AS [26],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 27 THEN [type] ELSE NULL END), 'P') AS [27],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 28 THEN [type] ELSE NULL END), 'P') AS [28],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 29 THEN [type] ELSE NULL END), 'P') AS [29],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 30 THEN [type] ELSE NULL END), 'P') AS [30],
		COALESCE(MAX(CASE WHEN DATEPART(DAY, leave_date) = 31 THEN [type] ELSE NULL END), 'P') AS [31],
		1 + DATEDIFF(DAY, @FromDate, @ToDate) - SUM(CASE WHEN [type] IN ('CL', 'R', 'T', 'S' THEN 1 ELSE 0 END) AS P,
		SUM(CASE WHEN [type] = 'CL' THEN 1 ELSE 0 END) AS CL,
		SUM(CASE WHEN [type] = 'R' THEN 1 ELSE 0 END) AS R,
		SUM(CASE WHEN [type] = 'T' THEN 1 ELSE 0 END) AS T,
		SUM(CASE WHEN [type] = 'S' THEN 1 ELSE 0 END) AS S
FROM		Table1
WHERE		leave_date >= @FromDate
		AND leave_date < DATEADD(DAY, 1, @ToDate)
GROUP BY	empCode
ORDER BY	empCode

and only use the colums you need in your presentation layer.

E 12°55'05.63"
N 56°04'39.26"

this code shows result as

empCode P CL R T S
21937 26 0 1 4 0

the day 21 to 20(next month) was deleted

Thank for the reply Peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 07:23:40

[code]ALTER PROCEDURE dbo.uspTwitSP
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS

SET NOCOUNT ON

CREATE TABLE #Aggregates
(
RowText VARCHAR(50),
ColumnText VARCHAR(50),
CellData MONEY
)

INSERT #Aggregates
(
RowText,
ColumnText,
CellData
)
SELECT e.empCode,
CONVERT(VARCHAR(10), d.theDate, 101),
COALESCE(t1.[type], 'P')
FROM (
SELECT empCode
FROM Table1
WHERE leave_date >= @FromDate
AND leave_date < DATEADD(DAY, 1, @ToDate)
GROUP BY empCode
) AS e
CROSS JOIN (
SELECT DATEADD(DAY, Number, @FromDate) AS theDate
FROM master..spt_values
WHERE Type = 'P'
AND Number <= DATEDIFF(DAY, @FromDate, @ToDate)
) AS d
LEFT JOIN Table1 AS t1 ON t1.empCode = e.empCode
AND t1.leave_date = d.theDate

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)

CREATE TABLE #Columns
(
ColumnIndex INT IDENTITY (0, 1),
ColumnText VARCHAR(50)
)

INSERT #Columns
(
ColumnText
)
SELECT DISTINCT ColumnText
FROM #Aggregates WITH (INDEX (IX_Aggregates))
ORDER BY CAST(ColumnText AS DATETIME)

CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)

CREATE TABLE #Rows
(
RowText VARCHAR(50)
)

INSERT #Rows
(
RowText
)
SELECT DISTINCT RowText
FROM #Aggregates WITH (INDEX (IX_Aggregates))

CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)

DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@SQL VARCHAR(1000)

SELECT @ColumnIndex = 0,
@MaxColumnIndex = MAX(ColumnIndex)
FROM #Columns

WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnIndex

SET @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(2) NOT NULL'
EXEC (@SQL)

SET @SQL = 'UPDATE r
SET r.' + QUOTENAME(@ColumnText) + ' = a.CellData
FROM #Rows AS r
INNER JOIN #Aggregates AS a WITH (INDEX (IX_Aggregates)) ON a.RowText = r.RowText
INNER JOIN #Columns AS c WITH (INDEX (IX_Columns)) ON c.ColumnText = a.ColumnText
WHERE c.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
EXEC (@SQL)

SET @ColumnIndex = @ColumnIndex + 1
END

SELECT r.*,
a.*
FROM #Rows AS r
INNER JOIN (
SELECT RowText,
SUM(CASE WHEN CellData = 'P' THEN 1 ELSE 0 END) AS P,
SUM(CASE WHEN CellData = 'CL' THEN 1 ELSE 0 END) AS CL,
SUM(CASE WHEN CellData = 'R' THEN 1 ELSE 0 END) AS R,
SUM(CASE WHEN CellData = 'T' THEN 1 ELSE 0 END) AS T,
SUM(CASE WHEN CellData = 'S' THEN 1 ELSE 0 END) AS S
FROM #Aggregates WITH (INDEX (IX_Aggregates))
GROUP BY RowText
) AS a ON a.RowText = r.RowText
ORDER BY r.RowText

DROP TABLE #Rows,
#Aggregates,
#Columns[/code]

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 07:47:30

quote:

Originally posted by Peso

ALTER PROCEDURE dbo.uspTwitSP
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
AS

SET NOCOUNT ON

CREATE TABLE	#Aggregates
		(
			RowText VARCHAR(50),
			ColumnText VARCHAR(50),
			CellData MONEY
		)

INSERT		#Aggregates
		(
			RowText,
			ColumnText,
			CellData
		)
SELECT		e.empCode,
		CONVERT(VARCHAR(10), d.theDate, 101),
		COALESCE(t1.[type], 'P')
FROM		(
			SELECT		empCode
			FROM		Table1
			WHERE		leave_date >= @FromDate
					AND leave_date < DATEADD(DAY, 1, @ToDate)
			GROUP BY	empCode
		) AS e
CROSS JOIN	(
			SELECT	DATEADD(DAY, Number, @FromDate) AS theDate
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number <= DATEDIFF(DAY, @FromDate, @ToDate)
		) AS d
LEFT JOIN	Table1 AS t1 ON t1.empCode = e.empCode
			AND t1.leave_date = d.theDate

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)

CREATE TABLE	#Columns
		(
			ColumnIndex INT IDENTITY (0, 1),
			ColumnText VARCHAR(50)
		)

INSERT		#Columns
		(
			ColumnText
		)
SELECT DISTINCT	ColumnText
FROM		#Aggregates WITH (INDEX (IX_Aggregates))
ORDER BY	CAST(ColumnText AS DATETIME)

CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE	#Rows 
		(
			RowText VARCHAR(50)
		)
 
INSERT		#Rows
		(
			RowText
		)
SELECT DISTINCT	RowText
FROM		#Aggregates WITH (INDEX (IX_Aggregates))

CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)

DECLARE	@ColumnIndex INT,
	@MaxColumnIndex INT,
	@ColumnText VARCHAR(50),
	@SQL VARCHAR(1000)

SELECT	@ColumnIndex = 0,
	@MaxColumnIndex = MAX(ColumnIndex)
FROM	#Columns

WHILE @ColumnIndex <= @MaxColumnIndex
	BEGIN
		SELECT	@ColumnText = ColumnText
		FROM	#Columns
		WHERE	ColumnIndex = @ColumnIndex

		SET	@SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(2) NOT NULL'
		EXEC	(@SQL)
 
		SET	@SQL = 'UPDATE		r
				SET		r.' + QUOTENAME(@ColumnText) + ' = a.CellData
				FROM		#Rows AS r
				INNER JOIN	#Aggregates AS a WITH (INDEX (IX_Aggregates)) ON a.RowText = r.RowText
				INNER JOIN	#Columns AS c WITH (INDEX (IX_Columns)) ON c.ColumnText = a.ColumnText 
				WHERE		c.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
		EXEC	(@SQL)
 
		SET	@ColumnIndex = @ColumnIndex + 1 
	END

SELECT		r.*,
		a.*
FROM		#Rows AS r
INNER JOIN	(
			SELECT		RowText,
					SUM(CASE WHEN CellData = 'P' THEN 1 ELSE 0 END) AS P,
					SUM(CASE WHEN CellData = 'CL' THEN 1 ELSE 0 END) AS CL,
					SUM(CASE WHEN CellData = 'R' THEN 1 ELSE 0 END) AS R,
					SUM(CASE WHEN CellData = 'T' THEN 1 ELSE 0 END) AS T,
					SUM(CASE WHEN CellData = 'S' THEN 1 ELSE 0 END) AS S
			FROM		#Aggregates WITH (INDEX (IX_Aggregates))
			GROUP BY	RowText
		) AS a ON a.RowText = r.RowText
ORDER BY	r.RowText

DROP TABLE	#Rows,
		#Aggregates,
		#Columns

E 12°55'05.63"
N 56°04'39.26"

I am Receiving an error as below

Server: Msg 260, Level 16, State 1, Procedure uspTwitSP, Line 17
Disallowed implicit conversion from data type varchar to data type money, table 'tempdb.dbo.#Aggregates_________________________________________________________________________________________________________0001000001CB', column 'CellData'. Use the CONVERT function to run this query.

which i cannot solve please correct the code....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 07:53:09

[code]CREATE TABLE #Aggregates
(
RowText VARCHAR(50),
ColumnText VARCHAR(50),
CellData VARCHAR(2)
)[/code]

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 08:00:29

quote:
Originally posted by Peso

CREATE TABLE #Aggregates
(
RowText VARCHAR(50),
ColumnText VARCHAR(50),
CellData VARCHAR(2)
)

E 12°55'05.63"
N 56°04'39.26"

again it shows error like this

Server: Msg 145, Level 15, State 1, Procedure uspTwitSP, Line 50
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

please help me find the solution....

Thanks for the reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 08:34:01

quote:

Originally posted by surezh

quote:

Originally posted by visakh16

CREATE TABLE #Temp
(
[empcode] int,
[leave_date] datetime,
[type] char(2),
[reason] varchar(50)
)
DECLARE @Start datetime,@End datetime,@DList varchar(2000),@Sql varchar(max)


INSERT INTO #Temp
SELECT 100, '2008-12-29 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 100, '2008-12-30 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2008-12-31 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-01 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-02 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2009-01-10 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-11 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-12 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 101, '2009-01-13 00:00:00.000', 'CL', 'Casual Leave' UNION ALL
SELECT 102, '2009-01-12 00:00:00.000', 'R', 'Restricted holiday' UNION ALL
SELECT 100, '2009-01-08 00:00:00.000', 'T', 'Tour' UNION ALL
SELECT 100, '2009-01-12 00:00:00.000', 'T', 'Tour'  

SELECT @Start=MIN(leave_date),@End=MAX(leave_date)
FROM #Temp

SELECT DATEADD(dd,number,@Start) AS Date INTO #Date
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@Start)<=@End



SELECT @DList=LEFT(dl.Dates,LEN(dl.Dates)-1)
FROM
(SELECT CAST(Date as varchar(11)) + ','
 FROM #Date
 FOR XML PATH('')
)dl(Dates)

SET @Sql='SELECT *
FROM
(
SELECT d.Date,t.empcode,t.type
FROM #Date d
LEFT JOIN #Temp t
ON t.leave_date=d.Date
)t
PIVOT (MAX(type) FOR Date IN ([' +  REPLACE(@DList,',','],[')+ ']))p
WHERE empcode IS NOT NULL'

EXEC(@Sql)

DROP TABLE #Temp

DROP TABLE #Date


output
----------------------------------
empcode	Dec 29 2008	Dec 30 2008	Dec 31 2008	Jan  1 2009	Jan  2 2009	Jan  3 2009	Jan  4 2009	Jan  5 2009	Jan  6 2009	Jan  7 2009	Jan  8 2009	Jan  9 2009	Jan 10 2009	Jan 11 2009	Jan 12 2009	Jan 13 2009
100	T 	T 	NULL	NULL	T 	NULL	NULL	NULL	NULL	NULL	T 	NULL	NULL	NULL	T 	NULL
101	NULL	NULL	CL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	CL	NULL	NULL	CL
102	NULL	NULL	NULL	R 	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	R 	R 	NULL

You should have SQL 2005 or later if you need to use PIVOT. then try this soln instead

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 08:42:48

quote:

Originally posted by surezh

quote:

Originally posted by Peso

ALTER PROCEDURE dbo.uspTwitSP
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
AS

SET NOCOUNT ON

CREATE TABLE	#Aggregates
		(
			RowText VARCHAR(50),
			ColumnText VARCHAR(50),
			CellData MONEY
		)

INSERT		#Aggregates
		(
			RowText,
			ColumnText,
			CellData
		)
SELECT		e.empCode,
		CONVERT(VARCHAR(10), d.theDate, 101),
		COALESCE(t1.[type], 'P')
FROM		(
			SELECT		empCode
			FROM		Table1
			WHERE		leave_date >= @FromDate
					AND leave_date < DATEADD(DAY, 1, @ToDate)
			GROUP BY	empCode
		) AS e
CROSS JOIN	(
			SELECT	DATEADD(DAY, Number, @FromDate) AS theDate
			FROM	master..spt_values
			WHERE	Type = 'P'
				AND Number <= DATEDIFF(DAY, @FromDate, @ToDate)
		) AS d
LEFT JOIN	Table1 AS t1 ON t1.empCode = e.empCode
			AND t1.leave_date = d.theDate

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)

CREATE TABLE	#Columns
		(
			ColumnIndex INT IDENTITY (0, 1),
			ColumnText VARCHAR(50)
		)

INSERT		#Columns
		(
			ColumnText
		)
SELECT DISTINCT	ColumnText
FROM		#Aggregates WITH (INDEX (IX_Aggregates))
ORDER BY	CAST(ColumnText AS DATETIME)

CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE	#Rows 
		(
			RowText VARCHAR(50)
		)
 
INSERT		#Rows
		(
			RowText
		)
SELECT DISTINCT	RowText
FROM		#Aggregates WITH (INDEX (IX_Aggregates))

CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)

DECLARE	@ColumnIndex INT,
	@MaxColumnIndex INT,
	@ColumnText VARCHAR(50),
	@SQL VARCHAR(1000)

SELECT	@ColumnIndex = 0,
	@MaxColumnIndex = MAX(ColumnIndex)
FROM	#Columns

WHILE @ColumnIndex <= @MaxColumnIndex
	BEGIN
		SELECT	@ColumnText = ColumnText
		FROM	#Columns
		WHERE	ColumnIndex = @ColumnIndex

		SET	@SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(2) NOT NULL'
		EXEC	(@SQL)
 
		SET	@SQL = 'UPDATE		r
				SET		r.' + QUOTENAME(@ColumnText) + ' = a.CellData
				FROM		#Rows AS r
				INNER JOIN	#Aggregates AS a WITH (INDEX (IX_Aggregates)) ON a.RowText = r.RowText
				INNER JOIN	#Columns AS c WITH (INDEX (IX_Columns)) ON c.ColumnText = a.ColumnText 
				WHERE		c.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
		EXEC	(@SQL)
 
		SET	@ColumnIndex = @ColumnIndex + 1 
	END

SELECT		r.*,
		a.*
FROM		#Rows AS r
INNER JOIN	(
			SELECT		RowText,
					SUM(CASE WHEN CellData = 'P' THEN 1 ELSE 0 END) AS P,
					SUM(CASE WHEN CellData = 'CL' THEN 1 ELSE 0 END) AS CL,
					SUM(CASE WHEN CellData = 'R' THEN 1 ELSE 0 END) AS R,
					SUM(CASE WHEN CellData = 'T' THEN 1 ELSE 0 END) AS T,
					SUM(CASE WHEN CellData = 'S' THEN 1 ELSE 0 END) AS S
			FROM		#Aggregates WITH (INDEX (IX_Aggregates))
			GROUP BY	RowText
		) AS a ON a.RowText = r.RowText
ORDER BY	r.RowText

DROP TABLE	#Rows,
		#Aggregates,
		#Columns

E 12°55'05.63"
N 56°04'39.26"

I cannot find the solution still it shows the error

Server: Msg 145, Level 15, State 1, Procedure uspTwitSP, Line 50
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Please help me.....

Thank For the Replies Peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 08:58:41

[code]INSERT #Columns
(
ColumnText
)
SELECT y.ColumnText
FROM (
SELECT DISTINCT ColumnText,
CAST(ColumnText AS DATETIME) AS theOrder
FROM #Aggregates WITH (INDEX (IX_Aggregates))
) AS d
ORDER BY y.theOrder[/code]

E 12°55'05.63"
N 56°04'39.26"

surezh
Starting Member

32 Posts

Posted - 2009-01-05 : 09:10:39

quote:

Originally posted by Peso

INSERT		#Columns
		(
			ColumnText
		)
SELECT		y.ColumnText
FROM		(
			SELECT DISTINCT	ColumnText,
					CAST(ColumnText AS DATETIME) AS theOrder
			FROM		#Aggregates WITH (INDEX (IX_Aggregates))
		) AS d
ORDER BY	y.theOrder

E 12°55'05.63"
N 56°04'39.26"

Now it shows like this.....

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/01/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/02/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/03/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/04/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/05/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/06/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/07/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/08/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/09/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/10/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/11/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/12/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/13/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/14/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/15/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/16/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/17/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/18/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/19/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/20/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/21/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/22/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/23/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/24/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/25/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/26/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/27/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/28/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/29/2008'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '12/30/2008'.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/01/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/02/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/03/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/04/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/05/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/06/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/07/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/08/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/09/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/10/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/11/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/12/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/13/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/14/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/15/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/16/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/17/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/18/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/19/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/20/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/21/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/22/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/23/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/24/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/25/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/26/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/27/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/28/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/29/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.
Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column '12/30/2008' cannot be added to table '#Rows' because it does not allow nulls and does not specify a DEFAULT definition.

I am confused please help me Peso
I would be grateful if you help me to solve this problem...

once again Thanks for your kind Replies Peso

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources