0

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...

1
Contributor
1
Reply
9
Views
4 Years
Discussion Span
Last Post by ss125
0

Just to add...

I am using this in a procedure. But I cant use paramemters to classify the code. I tried that.. but my senior says that as an lack of codereusability...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.