Create Monthly Attendance Report

Question

1.60/5 (3 votes)

See more:

Task is to generate a Monthly Report
Table: tblAttendance

Code Name AttendanceDate Status
---- ---- -------------- ------
A001 Abc Jan-01-2013 Present
B001 Xyz Jan-01-2013 Present
A001 Abc Jan-02-2013 Absent
B001 Xyz Jan-02-2013 Late
A001 Abc Jan-03-2013 Late
.
.

Now I have to create Monthly Report in Format given below

Jan, 2013
Code Name 1 2 3 .. 29 30 31
---- ---- - - - .. -  -  -
A001 Abc  P A L .. L  P  A 
B001 Xyz  P L L .. L  P  A 
.
.

Problem: Not getting any ideas to design the above report

Posted 5-Mar-13 1:21am

pryashrma

Updated 18-Apr-17 3:22am

v4

Add a Solution

Comments

Devang Vaja 5-Mar-13 7:49am

You can do it with html reports easily
tbl=new htmltable()
tr=new htmltableRow()
tc=new htmltablecell()
tc.bgcolor="lightgreen"
tc.innertext=ds.tables(0).rows(i)("Att")
tr.cells.add(tc)
tbl.rows.add(tr)

and also main thing is You can customize each cell in loop
But you have to develop logic for that as per your requirement
i have made this report customize as if employee is in half leave i have shown his cell half green,sundays as red...
You can work it out..
SHUBH CODING:)

pryashrma 5-Mar-13 7:54am

i don't want to use html reports... but your idea of customizing cell is great! Thanks

Devang Vaja 5-Mar-13 7:57am

Your welcome..:)

1 solution

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)

Tharaka MTR · Accepted Answer · 2013-03-05T06:58:00

Solution 1

for this I recommend you to use the CROSSTAB query. In SQL PIVOT table.
http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[^]

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

try your self. If you have any question feel free to ask.

-------------------------

Problem with your PIVOT aggregation. you can use something line MAX([Status])

SQL

SELECT [EmpCode], [ProjectName],[1] , [2],[3],[4],[5]
FROM 
(SELECT Day([AttendanceDate]) as d1, [EmpCode],[ProjectName],[AttendanceDate] , [Status]
FROM [tebs].[dbo].[View_Attendance]) p
PIVOT(
MAX([Status])
FOR d1 IN (  [1] , [2],[3],[4],[5]) ) AS pvt
ORDER BY 
pvt.[EmpCode],  pvt.[ProjectName];

Posted 5-Mar-13 6:58am

Tharaka MTR

Updated 5-Mar-13 21:15pm

v2

Comments

pryashrma 5-Mar-13 23:59pm

Thanks for the suggestion, I tried but sql express is throwing following error:Incorrect syntax near the keyword 'FOR'.

I have no idea for resolving the error

Query:

SELECT [EmpCode], [ProjectName],[1] , [2],[3],[4],[5]
FROM
(SELECT Day([AttendanceDate]) as d1, [EmpCode],[ProjectName],[AttendanceDate] , [Status]
FROM [tebs].[dbo].[View_Attendance]) p
PIVOT(
[Status]
FOR d1 IN ( [1] , [2],[3],[4],[5]) ) AS pvt
ORDER BY
pvt.[EmpCode], pvt.[ProjectName];

Could u plz help!

Tharaka MTR 6-Mar-13 2:52am

What is your SQL express version? 2005? 2008? etc..

Tharaka MTR 6-Mar-13 3:16am

I have updated my answer by modifying your query. please check.

pryashrma 6-Mar-13 5:35am

Thanks for replying!

Problem not yet resolved, now the query is generating n rows for n attendance dates for each employee... Any suggestions...
EmpCode ProjectName 1 2 3 4 5
------- ----------- - - - - -
A100 p1 NULL NULL P NULL NULL
A100 p1 NULL NULL NULL P NULL
A100 p1 NULL NULL NULL NULL P
A100 p1 NULL NULL NULL NULL NULL
A101 p3 NULL NULL P NULL NULL
A101 p3 NULL NULL NULL P NULL
A101 p3 NULL NULL NULL NULL L
A102 p3 NULL NULL P NULL NULL
A102 p3 NULL NULL NULL P NULL
A102 p3 NULL NULL NULL NULL A
.
.

and I more request.... could u plz explain why ' max(status) ' was used????

Member 13427569 4-Oct-17 3:23am

In your code remove [AttendanceDate] near select statement-->(SELECT Day([AttendanceDate]) as d1, [EmpCode],[ProjectName] , [Status]
FROM [tebs].[dbo].[View_Attendance]) it has to be like above

Tharaka MTR 6-Mar-13 6:06am

for the quick answer max(status):
you need to provide a aggregation function for the PIVOT query.. you can use MAX, MIN for this..

In your PIVOT query you want to use the STRING rows display as a column. so, the best aggregation function for this is MAX or MIN

pryashrma 6-Mar-13 23:41pm

got it!

Tharaka MTR 6-Mar-13 6:08am

if you can please provide the sample data script..

pryashrma 6-Mar-13 23:52pm

USE [dbAttendance]
GO

/****** Object: Table [dbo].[tblAttendance] Script Date: 03/07/2013 10:14:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblAttendance](
[AttendanceId] [bigint] IDENTITY(1,1) NOT NULL,
[EmpCode] [varchar](50) NOT NULL,
[ProjectName] [varchar](50) NOT NULL,
[Status] [char](1) NOT NULL,
[AttendanceDate] [date] NOT NULL,
[AttendanceBy] [varchar](50) NOT NULL,
[DOE] [datetime] NOT NULL,
[ApprovedBy] [varchar](50) NULL,
[ApprovedOn] [datetime] NULL,
[ApprovedFlag] [char](1) NOT NULL,
[Comments] [nvarchar](1500) NULL,
CONSTRAINT [PK_tblAttendance] PRIMARY KEY CLUSTERED
(
[AttendanceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Present-P; Absent-A; Leave-L; Tour-T; Holiday-H; Sunday-S;' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAttendance', @level2type=N'COLUMN',@level2name=N'Status'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Pending-P; Approved-A; Rejected-R' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAttendance', @level2type=N'COLUMN',@level2name=N'ApprovedFlag'
GO

CHill60 18-Apr-17 10:11am

"Any suggestions" - post a question using the Ask a Question link at the top of the page. Be sure to include your code and a clear description of the problem. Sample data is always a good idea too