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