Hello friends,
I am using sql server 2005 as a backend.
I want to filter the datagrid based on fromdate,todate,companyname,employee name.
I have done the date filter's....
The problem is with the other two filters.
The actual problem is..
The application has to filter based on company only if the user provides input in the front end.
the same goes to the employee name...
I got stuck here.
Please help me out.
This is my code which i Have tried so far...
SELECT
TR.EMPLOYEECODE,
CASE WHEN TR.GENDER = 'M' THEN 'MALE' ELSE 'FEMALE' END AS GENDER,
TR.EMPLOYEENAME,
EMP.COMPANYNAME,
TR.PANNO,
CASE
WHEN TR.KYCSTATUS = 'Y'
THEN 'YES'
WHEN TR.KYCSTATUS = 'N'
THEN 'NO'
END AS [KYCSTATUS],
TR.COSTCODE,
TR.LOCATION,
TR.CORREMAIL,
TR.CORRMOBILE,
dbo.FNGETDATE(TR.ENROLMENTDATE) REGISTEREDDATE,
ONE.SCHEMENAME1,
ONE.[1] AS [SCHEME1 AMOUNT],
ISNULL(TWO.SCHEMENAME2, '') SCHEMENAME2,
ISNULL(CAST(TWO.[2] AS VARCHAR), '') AS [SCHEME2 AMOUNT],
ISNULL(THREE.SCHEMENAME3, '') SCHEMENAME3,
ISNULL(CAST(THREE.[3] AS VARCHAR), '') AS [SCHEME3 AMOUNT],
ISNULL(Four.SCHEMENAME4, '') SCHEMENAME4,
ISNULL(CAST(Four.[4] AS VARCHAR), '') AS [SCHEME4 AMOUNT],
SUM(SH.EMPLOYEEAMOUNT)AS [TOTAL MONTHLY EMPLOYEE CONTRIBUTION AMOUNT]
FROM TRN_ENROLMENT TR
JOIN VW_EMPLOYEE EMP
ON EMP.EMPLOYEESEQ=TR.EMPLOYEESEQ
JOIN TRN_ENROLMENTSCHEME SH
ON TR.ENROLMENTSEQ=SH.ENROLMENTSEQ
LEFT JOIN(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,SCHEMENAME1, [1] from
(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,sch.SCHEMENAME as SCHEMENAME1, EMPLOYEEAMOUNT,
DENSE_RANK() OVER (PARTITION BY employeeseq ORDER BY enschemeseq DESC) AS Ranks
from TRN_ENROLMENTSCHEME
join CMN_MST_SCHEME sch
on sch.SCHEMESEQ=EMPLOYEESCHEMESEQ
where
RECORDSTATUS <> 'D')src
pivot
(sum(employeeamount)
for ranks in ([1], [2], [3], [4])
)as pivottable
where [1] is not null)ONE
ON ONE.ENROLMENTSEQ = TR.ENROLMENTSEQ
LEFT JOIN(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,SCHEMENAME2 ,[2] from
(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,sch2.SCHEMENAME as SCHEMENAME2, EMPLOYEEAMOUNT,
DENSE_RANK() OVER (PARTITION BY employeeseq ORDER BY enschemeseq DESC) AS Ranks
from TRN_ENROLMENTSCHEME
join CMN_MST_SCHEME sch2
on sch2.SCHEMESEQ=EMPLOYEESCHEMESEQ
where
RECORDSTATUS <> 'D')src
pivot
(sum(employeeamount)
for ranks in ([1], [2], [3], [4])
)as pivottable
where [2] is not null)TWO
ON TWO.ENROLMENTSEQ = TR.ENROLMENTSEQ
LEFT JOIN(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,SCHEMENAME3, [3] from
(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,sch3.SCHEMENAME as SCHEMENAME3, EMPLOYEEAMOUNT,
DENSE_RANK() OVER (PARTITION BY employeeseq ORDER BY enschemeseq DESC) AS Ranks
from TRN_ENROLMENTSCHEME
join CMN_MST_SCHEME sch3
on sch3.SCHEMESEQ=EMPLOYEESCHEMESEQ
where
RECORDSTATUS <> 'D')src
pivot
(sum(employeeamount)
for ranks in ([1], [2], [3], [4])
)as pivottable
where [3] is not null)THREE
ON THREE.ENROLMENTSEQ = TR.ENROLMENTSEQ
LEFT JOIN(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,SCHEMENAME4 ,[4] from
(select ENROLMENTSEQ, EMPLOYEESCHEMESEQ,sch4.SCHEMENAME as SCHEMENAME4, EMPLOYEEAMOUNT,
DENSE_RANK() OVER (PARTITION BY employeeseq ORDER BY enschemeseq DESC) AS Ranks
from TRN_ENROLMENTSCHEME
join CMN_MST_SCHEME sch4
on sch4.SCHEMESEQ=EMPLOYEESCHEMESEQ
where
RECORDSTATUS <> 'D')src
pivot
(sum(employeeamount)
for ranks in ([1], [2], [3], [4])
)as pivottable
where [4] is not null)Four
ON Four.ENROLMENTSEQ = TR.ENROLMENTSEQ
--ADDED TO DATE FILTER
WHERE dbo.FNGETDATE(TR.ENROLMENTDATE) >= '18-Sep-2013' and dbo.FNGETDATE(TR.ENROLMENTDATE) < '30-Sep-2013'
and
TR.EMPLOYEENAME=''
and
COMPANYNAME=''
--ADD
group by TR.EMPLOYEECODE,
TR.EMPLOYEENAME,
CASE WHEN TR.GENDER = 'M' THEN 'MALE' ELSE 'FEMALE' END,
EMP.COMPANYNAME,
TR.PANNO,
TR.ENROLMENTDATE,
CASE
WHEN TR.KYCSTATUS = 'Y'
THEN 'YES'
WHEN TR.KYCSTATUS = 'N'
THEN 'NO'
END,
TR.COSTCODE,
TR.LOCATION,
TR.CORREMAIL,
TR.CORRMOBILE,
ONE.SCHEMENAME1,
ONE.[1],
TWO.SCHEMENAME2,
TWO.[2],
THREE.SCHEMENAME3,
THREE.[3],
Four.SCHEMENAME4,
Four.[4]
ORDER BY EMP.COMPANYNAME, TR.EMPLOYEENAME
Thanks in advance...