Attendance Register Reports

See more:

C#

SQL

I am using Sql Server 2005 and C# RDLC report in Windows Application

I am using the table as

1.

Table Attendance

USE [ses_final]
GO
/****** Object:  Table [dbo].[Attendance]    Script Date: 11/17/2012 13:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Attendance](
	[Attnd_Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Emp_Id] [int] NOT NULL,
	[Attnd_Date] [smalldatetime] NOT NULL,
	[Attnd_Day]  AS (CONVERT([varchar](2),[Attnd_Date],(113))),
	[Attnd_Status] [char](1) COLLATE Latin1_General_CI_AI NULL CONSTRAINT [DF_Attendance_Attnd_Status]  DEFAULT ((0)),
	[Remark] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
	[JustTheDate]  AS (dateadd(day,datediff(day,(0),[attnd_date]),(0))),
 CONSTRAINT [pk_attendance] PRIMARY KEY CLUSTERED 
(
	[Emp_Id] ASC,
	[Attnd_Date] 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

and Stored Procedure for report Attendance register to show the full month

with 1- 31 column with sign of P , L , A

i am using the pivot for the same.

USE [ses_final]
GO
/****** Object:  StoredProcedure [dbo].[GetAttandanceSheet]    Script Date: 11/17/2012 13:14:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[GetAttandanceSheet]
(
@Month_Name1 varchar(30),
@Month_Name2 varchar(30)
)
AS
Begin

Select emp_id,emp_firstname,Month_Name,[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
From
(Select at.emp_id,em.emp_firstname,DATENAME(Month, at.Attnd_Date) as Month_Name, at.Attnd_Day,at.Attnd_Status
From Attendance as at inner join ses_emp_details as em on at.Emp_Id=em.emp_id
Where (DATENAME(Month, at.Attnd_Date)>=@Month_Name1)  AND (DATENAME(Month, at.Attnd_Date)<=@Month_Name2)
)
as att
Pivot
(
max(Attnd_Status) For Attnd_Day In
([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) 
as PvT



End

My question is to show the Hoilday & Sat Sunday in register by default in report as marked as X & H


Please Guide me

[edit]code block added[/edit]