Generate Monthly Attendance

Question

1.00/5 (1 vote)

See more:

Code Name AttendanceDate Status
---- ---- -------------- ------
A001 Abc Jan-01-2013 P
B001 Xyz Jan-01-2013 P
A001 Abc Jan-02-2013 A
B001 Xyz Jan-02-2013 L
A001 Abc Jan-03-2013 L

P: Present
A: Absent
L: Late

Generate Report like this for every month..

Code Name 01 02 03 04 ...... 31
A001 abc P A L
B001 xyz P L

Thanks in advance..

Posted 17-Mar-14 19:52pm

chetna2810

Add a Solution

Comments

syed shanu 18-Mar-14 1:59am

Try using Pivot query

chetna2810 18-Mar-14 2:07am

I try to do so like this.. But the problem is the column names are not fixed. I want to fix them like dates 01, 02 , 03 and so on..

DECLARE @Start datetime,@End datetime,@DList varchar(2000),@Sql varchar(max)

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 * from #Date

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.typee
FROM #Date d
LEFT JOIN Temp t
ON t.leave_date=d.Date
)t
PIVOT (MAX(typee) FOR Date IN ([' + REPLACE(@DList,',','],[')+ ']))p
WHERE empcode IS NOT NULL'

EXEC(@Sql)

and even unable to use datepart also

syed shanu 18-Mar-14 2:17am

Chk this
DECLARE @Start datetime ='2014-03-01'
,@End datetime='2014-03-31',
@DList varchar(2000),
@Sql varchar(max)

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

you get all days between start date and end date. now add this in tep table and join this with your main table and create pivot query

chetna2810 18-Mar-14 2:28am

I am doing the same and creating a temp table "#date". Like this
2008-12-29 00:00:00.000
2008-12-30 00:00:00.000
2008-12-31 00:00:00.000
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
2009-01-11 00:00:00.000
2009-01-12 00:00:00.000
2009-01-13 00:00:00.000

when i am creating @DList Like
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
here i am converting dates to pass in to pivot..
if i use datepart then pivot is not takeing those dates like 01, 02 .. and so on.

syed shanu 18-Mar-14 2:32am

Why you convert in Dec 29 2008 this format you can use the same format 2008-12-29 00:00:00.000 in your pivot query and final result if you want to display the Dec 29 2008 format

chetna2810 18-Mar-14 2:37am

I tried that also by doing so..
SELECT @DList=LEFT(dl.Dates,LEN(dl.Dates)-1)
FROM
(SELECT
convert(varchar,date,101) + ','
FROM #Date
FOR XML PATH('')
)dl(Dates)

but the column names are like
12/29/2008,12/30/2008 ..
they are fixed..

syed shanu 18-Mar-14 2:40am

Run this
DECLARE @Start datetime ='2014-03-01'
,@End datetime='2014-03-31',
@DList varchar(2000),
@Sql varchar(max)

SELECT DATEADD(dd,number,@Start) AS Date ,CONVERT(VARCHAR(2),DATEADD(dd,number,@Start),6)days FROM
master..spt_values WHERE
type='p' AND
DATEADD(dd,number,@Start)<=@End

syed shanu 18-Mar-14 2:41am

Do you want Date number from 01 to 31 like this DECLARE @Start datetime ='2014-03-01'
,@End datetime='2014-03-31',
@DList varchar(2000),
@Sql varchar(max)

SELECT DATEADD(dd,number,@Start) AS Date ,CONVERT(VARCHAR(2),DATEADD(dd,number,@Start),6)d FROM
master..spt_values WHERE
type='p' AND
DATEADD(dd,number,@Start)<=@End

chetna2810 18-Mar-14 2:50am

Sir,
@DList showing the result like this
29,30,31,01,02,03,04,05,06,07,08,09,10,11,12,13
but main table showing no result only columns are creating and getting a error "Conversion failed when converting datetime from character string."

syed shanu 18-Mar-14 2:56am

can you give me your table structuer this table Code Name AttendanceDate Status

chetna2810 18-Mar-14 3:02am

Table sturcture
CREATE TABLE [dbo].[Temp](
[code] [char](4) NULL,
[name] [varchar](50) null,
[AttendanceDate] [datetime] NULL,
[Status] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reason] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

syed shanu 18-Mar-14 3:06am

Ok give me some time i will make query and paste here till that keep trying

chetna2810 18-Mar-14 3:11am

thnx sir..i also trying..

chetna2810 18-Mar-14 4:06am

Sir,
status P/A is not showing there.. and if there are lots of employees then query will take time to execute..

chetna2810 19-Mar-14 3:34am

Hello Sir,
i got the solution from your previous comment "CONVERT(VARCHAR(2),DATEADD(dd,number,@Start),6)d"
Thanks for this..
Also tell me how can i found sunday and show it on report..??
Thanks in Advance.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2014-03-17T21:48:00

Hi,
As i mention i have tried to make you output check this from your table i have added a new columname as relation_key
check the query belo

SQL

--Table Script
CREATE TABLE [dbo].[Temp]( [code] [char](4) NULL, [name] [varchar](50) null, [AttendanceDate] [datetime] NULL, [Status] [char](2) 
COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [reason] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,relation_key int ) ON [PRIMARY]

insert into Temp values('B001', 'Bbc', '2014-03-05 00:00:00.000', 'P','',1)
insert into Temp values('A001', 'Abc', '2014-03-05 00:00:00.000', 'P','',1)

pivot query.

DECLARE @Start datetime ='2014-03-01'
,@End datetime='2014-03-31',
@DList varchar(2000),
@Sql varchar(max) 


IF OBJECT_ID('tempdb..#TEMP_Everyday') IS NOT NULL                                                        
    DROP TABLE #TEMP_Everyday   

SELECT DATEADD(dd,a.number,@Start) AS Date ,CONVERT(VARCHAR(2),DATEADD(dd,a.number,@Start),6) as d ,1 as relation_key
into #TEMP_Everyday
FROM 
master..spt_values a

 WHERE 
type='p' AND 
DATEADD(dd,a.number,@Start)<=@End 
----select * from Temp
----select * from #TEMP_Everyday

IF OBJECT_ID('tempdb..#TEMP_final') IS NOT NULL                                                        
    DROP TABLE #TEMP_final 
    
select distinct a.Code ,a.name,
case when a.AttendanceDate=b.date then a.status else '' END as Status
,a.reason,b.date,b.d
into #TEMP_final
from
#TEMP_Everyday b left outer join 
temp a 
ON b.relation_key=a.relation_key
group by a.Code , a.AttendanceDate,a.status,a.name,a.reason,b.date,b.d


--select distinct Code,name,status,reason,date,d 
--from 
--#TEMP_final
--group by 
--Code,name,status,reason,date,d 


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(d) 
                    from #TEMP_Everyday
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Code,name,' + @cols + ' 
            from #TEMP_final
            pivot 
            (
                min(status)
                for status in (' + @cols + ')
            ) p '

execute(@query)

Note here the data is now not matched you can filter and match the data as you needed

syed shanu · Answer 2 · 2014-03-18T22:00:00

Hi,
here iam adding sql query to display ever week sunday date with in from and to date for example Start date as '2014-01-01' and end date as '2014-04-04' this query will return every week sunday as output date :
"

VB

SQL

      Declare   @FromDate            VARCHAR(20)  = '2014-01-01'                                               
   Declare   @ToDate            VARCHAR(20)  =  '2014-04-04'  
                                        
IF OBJECT_ID('tempdb..#TEMP_EveryWk_Snday') IS NOT NULL                                                        
    DROP TABLE #TEMP_EveryWk_Snday                                                     
                                                        
 DECLARE @TOTALCount INT      
                                                
    Select  @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);--DATEDIFF(DD,@FromDate,@ToDate);                                                    
                 
                                                     
   WITH d AS                                                     
            (                                                    
              SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()                                                     
                OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))                                                    
              FROM sys.all_objects                           
            )                                                    
                                                          
         SELECT  distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE))WkStartSundays                                           
                                                         
 into #TEMP_EveryWk_Snday                                                  
    FROM d           
select WkStartSundays,DATENAME(dw,WkStartSundays) Day_Name from   #TEMP_EveryWk_Snday

Generate Monthly Attendance

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0