Click here to Skip to main content
15,299,443 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am using Sql Server 2005 and C# RDLC report in Windows Application

I am using the table as

1.

Table Attendance

SQL
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.

SQL
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]
Posted
Updated 16-Nov-12 23:17pm
v3
Comments
Member 10303148 23-May-14 6:16am
   
day off using web method with javascript

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900