I Have 2 properties dtp_Year & dtp_Quarter
than which i select Quarter Value 1 than get correct data
This My Code What i Worng In Code Tell Me And send me right Code
private DataTable QuarterlyDayWiseAttendanceReport() { SqlCommand cmd = new SqlCommand("UIPKGREPORTING_spFetchQuarterlyDayWiseAttendanceReport", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@inVolunteerID", SqlDbType.Int).Value = 0; cmd.Parameters.AddWithValue("@inType", drp_Type.Text); cmd.Parameters.AddWithValue("@inShift", drp_Shift.Text); cmd.Parameters.AddWithValue("@inFromDateTime", SqlDbType.Int).Value = Convert.ToInt32(dtp_Year.Text =="yyyy"); cmd.Parameters.AddWithValue("@inToDateTime", dtp_ToDate.Value); cmd.Parameters.Add("@inPoints", SqlDbType.Int).Value = -1; cmd.Parameters.Add("@inQuarter", SqlDbType.Int).Value = Convert.ToInt32(dtp_Quarter.Text =="1"); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet dt = new DataSet(); sda.Fill(dt, "abc"); dataGridView1.DataSource = dt.Tables["abc"].DefaultView; return dt.Tables["abc"]; }
Store Proc Code
USE [BiometricDB] GO /****** Object: StoredProcedure [dbo].[UIPKGREPORTING_spFetchQuarterlyDayWiseAttendanceReport] Script Date: 04/30/2022 15:49:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================================================ -- Author: Nazim Nizar Ali -- Create date: 10-Jan-2016 -- Description: returns volunteer quarterly day wise attendance report -- ================================================ ALTER PROCEDURE [dbo].[UIPKGREPORTING_spFetchQuarterlyDayWiseAttendanceReport] @inVolunteerID int, @inType varchar(100), @inShift varchar(100), @inFromDateTime datetime, @inToDateTime datetime, @inPoints int, @inQuarter int AS DECLARE @varYear int, @varQuarterMonth1 int, @varQuarterMonth2 int, @varQuarterMonth3 int BEGIN SET NOCOUNT ON; SET @varYear = DATEPART(YYYY, @inFromDateTime); if (@inQuarter = 1) begin SET @varQuarterMonth1 = 1; SET @varQuarterMonth2 = 2; SET @varQuarterMonth3 = 3; end else if (@inQuarter = 2) begin SET @varQuarterMonth1 = 4; SET @varQuarterMonth2 = 5; SET @varQuarterMonth3 = 6; end else if (@inQuarter = 3) begin SET @varQuarterMonth1 = 7; SET @varQuarterMonth2 = 8; SET @varQuarterMonth3 = 9; end else begin SET @varQuarterMonth1 = 10; SET @varQuarterMonth2 = 11; SET @varQuarterMonth3 = 12; end select ROW_NUMBER() over (order by pos.sortorder, temp.VolunteerName) as SNo, pos.positionname as Position, temp.VolunteerName as "Volunteer Name", temp.M1Days as "M1 Morning Days", temp.E1Days as "M1 Evening Days", temp.Ex1Days as "M1 Extra Days", temp.Total1Days as "M1 Total Days", temp.M2Days as "M2 Morning Days", temp.E2Days as "M2 Evening Days", temp.Ex2Days as "M2 Extra Days", temp.Total2Days as "M2 Total Days", temp.M3Days as "M3 Morning Days", temp.E3Days as "M3 Evening Days", temp.Ex3Days as "M3 Extra Days", temp.Total3Days as "M3 Total Days", temp.MTotal as "Morning Total Days", temp.ETotal as "Evening Total Days", temp.ExTotal as "Extra Total Days", temp.GrandTotal as "Grand Total", temp.TotalDuration as "Total Duration" from (select volunteer.volunteerpositionid as Position, volunteer.displayfullname as VolunteerName, COUNT(CASE when attendance.shift = 'Morning' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as M1Days, COUNT(CASE when attendance.shift = 'Evening' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as E1Days, COUNT(CASE when attendance.shift = 'Special' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as Ex1Days, COUNT(CASE when DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth1 then attendance.points end) as Total1Days, COUNT(CASE when attendance.shift = 'Morning' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as M2Days, COUNT(CASE when attendance.shift = 'Evening' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as E2Days, COUNT(CASE when attendance.shift = 'Special' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as Ex2Days, COUNT(CASE when DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth2 then attendance.points end) as Total2Days, COUNT(CASE when attendance.shift = 'Morning' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as M3Days, COUNT(CASE when attendance.shift = 'Evening' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as E3Days, COUNT(CASE when attendance.shift = 'Special' and DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as Ex3Days, COUNT(CASE when DATEPART(YYYY, attendance.dutydatetime) = @varYear and DATEPART(MM, attendance.dutydatetime) = @varQuarterMonth3 then attendance.points end) as Total3Days, COUNT(CASE when attendance.shift = 'Morning' then attendance.points end) as MTotal, COUNT(CASE when attendance.shift = 'Evening' then attendance.points end) as ETotal, COUNT(CASE when attendance.shift = 'Special' then attendance.points end) as ExTotal, COUNT(attendance.points) as GrandTotal, --SUM(isnull(CAST(duration/60.0 as DECIMAL(16, 2)), 0)) as TotalDuration CAST( ( SUM( isnull( duration, 0 ) )/60 ) + ( ( SUM( isnull( duration, 0 ) )%60 )/100.0 ) as DECIMAL(16, 2) ) as TotalDuration from tblvolunteer volunteer left outer join tblattendancelog attendance on attendance.volunteerid = volunteer.volunteerid and attendance.type = 'Present' --where (tblattendancelog.volunteerid = @inVolunteerID or @inVolunteerID = 0) --and (type = @inType or @inType = '< All >') --and (shift = @inShift or @inShift = '< All >') and (attendance.dutydatetime between @inFromDateTime and @inToDateTime) --and (points = @inPoints or @inPoints = -1) where volunteer.isresign = 0 group by volunteer.volunteerpositionid, volunteer.displayfullname ) temp inner join tblvolunteerposition pos on temp.Position = pos.volunteerpositionid; END