Hi
Please how can I use a "where clause" in the below stored procedure to retrieve columns and display in gridview? I have three textboxes on the form. One is for user to enter "regno" to retrieve record for a particular students, the remaining two textboxes are for "from date" and "to date" respectively so that user can select from date and to date from two calendar controls I have on the form. On selection date appears in the two textboxes so that records can be retrieved based on the selected date.
CREATE PROCEDURE [dbo].[GetAttendanceByHours]
AS
BEGIN
Select *,
ISNULL(NO_HRS_PRESENT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) PRSNT_PERC,
ISNULL(NO_HRS_ABSNT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) ABSNT_PERC
from
(
select
REGNO,
FIRSTNAME,
LASTNAME,
MAX(case when status = 'P' THEN CNT end) NO_HRS_PRESENT,
MAX(case WHEN STATUS = 'A' THEN CNT END) NO_HRS_ABSNT
from
(
select
REGNO,FIRSTNAME, LASTNAME,status,
count(status) CNT from AttendanceTable
group by regno,firstname, lastname, status
) A
GROUP BY regno,firstname, lastname
) tmp
END
