Attendance Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Attendance Query

Author

Topic

bekeer020
Starting Member

24 Posts

Posted - 2011-11-03 : 13:48:40

Dear All
I have Attendance Table like the following:

client_ID-State-Att_Date
-------------------------
C1-1-11/1/2011
C2-2-11/1/2011
C3-1-11/1/2011
C1-2-11/2/2011
C2-2-11/2/2011
C3-1-11/2/2011
.....
.....

State:1 Att - 2 Absent

I want to create query in stored procedure , also i will pass saturday date and i want the result like this

I want the result 28 columns :first column name is saturday date which passed from user, and second column name next date and ..

here example for column names that i want:
11/1/2011-11/2/2011-11/3/2011-........-11/28/2011

and i want to check the state and date from table and put the state under the date

here this example

suppose if user pass 11/1/2011 and the final result like this

Client_ID-11/1/2011-11/2/2011-11/3/2011-........-11/28/2011
C1-1-2-.....
C2-2-2-.....
C3-1-1-.....

and here photo like what i want

Note:date in columns name not fixed, it will changed depend in passed value

Can any one help me to do like this quert in stored procedure

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 13:55:12

use below logc

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

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

bekeer020
Starting Member

24 Posts

Posted - 2011-11-03 : 14:06:08

Can you try visakh16 to help me because i do not have more experience in dynamic Pivote

Please visakh16 help me to solve this problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 14:07:35

first try it yourself based on link explanation. then if you face any issues we will help you out

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

bekeer020
Starting Member

24 Posts

Posted - 2011-11-03 : 14:24:14

first try sql tell me :

Invalid column name 'state'.

my code

select

* from
(
select ID_Number, date as pivot_col from Attendance_Registration
) as t
pivot
(
Max(state) for pivot_col in ([1],[30])
) as p

can you help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:44:47

[code]
select

* from
(
select ID_Number, date as pivot_col,state from Attendance_Registration
) as t
pivot
(
Max(state) for pivot_col in ([11/1/2011],[11/2/2011],...)
) as p
[/code]

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

bekeer020
Starting Member

24 Posts

Posted - 2011-11-04 : 06:38:55

Dear Visakh16 thank you for help me

but in your example the columns name is fixed i want it Dynamic depends in passed date
if user pass 4/4/2011 the first column 4/4/2011 and 4/5/2011 .. 5/3/2011 like this

in the first i want to prepare columns name.

I try this code but this show all the dates in table how to show only 30 columns depends on above example

=====
DECLARE @listCol VARCHAR(2000)

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(CONVERT(nvarchar,date, 103))

FROM dbo.Attendance_Registration

ORDER BY '],[' + ltrim(CONVERT(nvarchar,date, 103))

FOR XML PATH('')

), 1, 2, '') + ']'

--set @listCol2=@listCol+']'
print @listCol
=====

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 07:59:30

add a parameter and filter 30 days based on it
some thing like


DECLARE @Date datetime

set @Date='2011-11-01'
DECLARE @listCol VARCHAR(2000)


SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(CONVERT(nvarchar,date, 103))

FROM dbo.Attendance_Registration
WHERE date BETWEEN @Date AND DATEADD(dd,30,@Date)
ORDER BY '],[' + ltrim(CONVERT(nvarchar,date, 103))

FOR XML PATH('')

), 1, 2, '') + ']'

--set @listCol2=@listCol+']'
print @listCol

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

bekeer020
Starting Member

24 Posts

Posted - 2011-11-04 : 13:01:51

Dear Visakh16 thank you again

when i try this code every think good
====
DECLARE @listCol VARCHAR(2000)
DECLARE @Da2 datetime

DECLARE @query VARCHAR(4000)

set @Da2='1/1/2010'
SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(CONVERT(varchar,date,101))

FROM dbo.Attendance_Registration
WHERE date BETWEEN @Da2 AND DATEADD(dd,100,@Da2)

ORDER BY '],[' + ltrim(CONVERT(varchar,date, 101))

FOR XML PATH('')

), 1, 2, '') + ']'

print @listCol

====

but when i add cretirea in second part

=====
DECLARE @listCol VARCHAR(2000)
DECLARE @Da2 datetime

DECLARE @query VARCHAR(4000)

set @Da2='1/1/2010'
SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(CONVERT(varchar,date,101))

FROM dbo.Attendance_Registration
WHERE date BETWEEN @Da2 AND DATEADD(dd,100,@Da2)

ORDER BY '],[' + ltrim(CONVERT(varchar,date, 101))

FOR XML PATH('')

), 1, 2, '') + ']'

--set @listCol2=@listCol+']'
print @listCol

set @query='SELECT * FROM

(SELECT ID_Number, date , state,Group_ID

FROM Attendance_Registration
WHERE date BETWEEN '+@Da2+' AND '+DATEADD(dd,100,@Da2)+'
) src

PIVOT (MAX(state) FOR date

IN ('+@listCol+']'+')) AS pvt'

EXECUTE (@query)

=====

show me following error:

[01/06/2010],[03/06/2010]
Msg 241, Level 16, State 1, Line 23
Conversion failed when converting datetime from character string.

can you try it in any table in your DB,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 13:10:19

[code]
....
WHERE date BETWEEN '+CONVERT(varchar(11),@Da2,121)+' AND '+CONVERT(varchar(11),DATEADD(dd,100,@Da2),121)+'
....
[/code]

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

bekeer020
Starting Member

24 Posts

Posted - 2011-11-04 : 14:20:24

DECLARE @listCol VARCHAR(2000)
DECLARE @Da2 datetime

DECLARE @query VARCHAR(4000)
set @Da2=''
set @Da2='1/1/2010'
SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(CONVERT(varchar,date,101))

FROM dbo.Attendance_Registration
WHERE date BETWEEN @Da2 AND DATEADD(dd,100,@Da2)

ORDER BY '],[' + ltrim(CONVERT(varchar,date, 101))

FOR XML PATH('')

), 1, 2, '') + ']'

--set @listCol2=@listCol+']'
print @listCol

set @query='SELECT * FROM

(SELECT ID_Number, date , state,Group_ID

FROM Attendance_Registration
WHERE date BETWEEN '+CONVERT(varchar(11),@Da2,101)+' AND '+CONVERT(varchar(11),DATEADD(dd,100,@Da2),101)+'

) src

PIVOT (MAX(state) FOR date

IN ('+@listCol+']'+')) AS pvt'

EXECUTE (@query)

=======================================

Msg 105, Level 15, State 1, Line 12
Unclosed quotation mark after the character string '03/06/2010])) AS pvt'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '03/06/2010])) AS pvt'.

create table from three columns(ID_number,date,state) and apply the above code and tell me why this error

bekeer020
Starting Member

24 Posts

Posted - 2011-11-04 : 16:19:15

dear visakh16 i reach to 95% for solution

when i execute the code with out criteria the code executed and show all the data rights.

but when i add the criteria after where clause (no data Retrieve and no result. why)
Note the criteria in green color

========

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
Declare @gropid varchar
DECLARE @IDList table (ID datetime)

declare @fo int
declare @i datetime
set @gropid='c1_11TEST_20111_11'
set @i = '10/1/2011'
set @fo = 1
INSERT INTO @IDList values (@i)
while @fo <=30
begin
INSERT INTO @IDList values ( CONVERT(varchar(11),DATEADD

(dd,@fo,@i),101) )
set @fo=@fo+1
end

SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + ltrim(CONVERT(nvarchar,ID,

111))

FROM @IDList

ORDER BY '],[' + ltrim(CONVERT(nvarchar,ID,

111))

FOR XML PATH('')

), 1, 2, '') + ']'
print @listCol

set @query='SELECT * FROM

(SELECT ID_Number, Date, state,Group_ID

FROM dbo.Attendance_Registration
where group_id='''+ @gropid+'''
) src

PIVOT (SUM(state) FOR Date

IN ('+@listCol+')) AS pvt'

execute(@query)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:36:47

use print(@Query) instead of exec and post the result

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

bekeer020
Starting Member

24 Posts

Posted - 2011-11-06 : 05:10:34

Declare @gropid varchar(400)
DECLARE @IDList table (ID datetime)

the problem from varchar variable when i add length every think right

Thank you Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-06 : 05:34:45

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources