show monthly attendance of an employee based on month

Question

emp_id |name|1 2 3 4 5 6 7 8 9............latst date of month. 001 |Rohon|L A P A A P P P P P......... 002 |Rabi |A A A A A A A A A A......

Ravi Kumar · Answer

create the table structure as fallows ---Master Table----- CREATE TABLE [dbo].[att_RegisterMain]( [Pk_RegisterMain] [numeric](18 0) IDENTITY(11) NOT NULL [CMonth] [int] NULL [CYear] [int] NULL CONSTRAINT [PK_RegisterMain] PRIMARY KEY CLUSTERED ( [Pk_RegisterMain] 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 ---------Child Table-------- CREATE TABLE [dbo].[att_RegisterAttendance]( [Pk_RegisterAttendance] [numeric](18 0) IDENTITY(11) NOT NULL [Fk_RegisterMain] [numeric](18 0) NULL [StaffName] [nvarchar](500) NULL [StaffId] [nvarchar](500) NULL [Day_1] [bit] NULL [Day_2] [bit] NULL [Day_3] [bit] NULL [Day_4] [bit] NULL [Day_5] [bit] NULL [Day_6] [bit] NULL [Day_7] [bit] NULL [Day_8] [bit] NULL [Day_9] [bit] NULL [Day_10] [bit] NULL [Day_11] [bit] NULL [Day_12] [bit] NULL [Day_13] [bit] NULL [Day_14] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_14] DEFAULT ((1)) [Day_15] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_15] DEFAULT ((1)) [Day_16] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_16] DEFAULT ((1)) [Day_17] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_17] DEFAULT ((1)) [Day_18] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_18] DEFAULT ((1)) [Day_19] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_19] DEFAULT ((1)) [Day_20] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_20] DEFAULT ((1)) [Day_21] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_21] DEFAULT ((1)) [Day_22] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_22] DEFAULT ((1)) [Day_23] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_23] DEFAULT ((1)) [Day_24] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_24] DEFAULT ((1)) [Day_25] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_25] DEFAULT ((1)) [Day_26] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_26] DEFAULT ((1)) [Day_27] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_27] DEFAULT ((1)) [Day_28] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_28] DEFAULT ((1)) [Day_29] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_29] DEFAULT ((1)) [Day_30] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_30] DEFAULT ((1)) [Day_31] [bit] NULL CONSTRAINT [DF_att_RegisterAttendance_31] DEFAULT ((1)) CONSTRAINT [PK_RegisterAttendance] PRIMARY KEY CLUSTERED ( [Pk_RegisterAttendance] 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 ALTER TABLE [dbo].[att_RegisterAttendance] WITH CHECK ADD CONSTRAINT [FK_att_RegisterAttendance_att_RegisterMain] FOREIGN KEY([Fk_RegisterMain]) REFERENCES [dbo].[att_RegisterMain] ([Pk_RegisterMain]) GO ALTER TABLE [dbo].[att_RegisterAttendance] CHECK CONSTRAINT [FK_att_RegisterAttendance_att_RegisterMain] GO