Search With Filters

SELECT
        (
            SELECT COUNT(*) FROM Employee_Assiduity AS EASS
                LEFT JOIN Assiduity_Types AS ET
                    ON ET.Code = EASS.Assiduity_Code
                WHERE EASS.Employee_No = E.Employee_No
                        AND ET.Blocked = 0
                        AND ET.[TYPE] = 0
        ),

        E.Employee_No,
        E.Alias AS Name,
        E.Sharepoint_User,
        E.Income_Category AS 'Income_Category',
        (E.First_Name + ISNULL(' ' + E.Middle_Name, '') + ISNULL(' ' + E.Last_Name, '')) AS LongName,
        ISNULL(GC_Cat.[Description], '') AS Category,
        ISNULL(OSH_Dept.[Description], ISNULL(OSH_Zona.[Description],'')) AS 'Department',
        E.Department AS 'Department_Code',
        ISNULL(OSH_Sector.[Description], ISNULL(OSH_AreaCarga.[Description], '')) AS 'Sector',
        E.Sector AS 'Sector_Code',
        ISNULL(OSH_Area.[Description], ISNULL(OSH_TI.[Description], '')) AS 'Area',
        E.Area AS 'Area_Code',
        ISNULL(OSH_Area.ID, ISNULL(OSH_TI.ID, '')) AS 'Area_ID',
        ISNULL(OSH_Sector.Parent_ID, ISNULL(OSH_AreaCarga.Parent_ID, '')) AS 'Sector_ParentID'

FROM Employee AS E
        --CATEGORIA
    LEFT JOIN General_Codes AS GC_Cat
        ON GC_Cat.Code = E.Category_Code AND GC_Cat.[TYPE] = 'CA'

        --VIAGENS
    LEFT JOIN Organization_Structure_Header AS OSH_Dept
        ON OSH_Dept.ERP_No = E.Department AND OSH_Dept.Department_Code = 'DPT'
    LEFT JOIN Organization_Structure_Header AS OSH_Area
        ON OSH_Area.ERP_No = E.Area AND OSH_Area.Department_Code = 'AREA'AND OSH_Area.[Parent_ID] = OSH_Dept.ID
    LEFT JOIN Organization_Structure_Header AS OSH_Sector
        ON OSH_Sector.ERP_No = E.Sector AND OSH_Sector.Department_Code = 'SECT' AND OSH_Sector.[Parent_ID] = OSH_Area.ID

        --CARGA
    LEFT JOIN Organization_Structure_Header AS OSH_Zona
        ON OSH_Zona.ERP_No = E.Department AND OSH_Zona.Department_Code = 'ZONA'
    LEFT JOIN Organization_Structure_Header AS OSH_TI
        ON OSH_TI.ERP_No = E.Area AND OSH_TI.Department_Code = 'TIPO DE INSTALACAO'AND OSH_TI.[Parent_ID] = OSH_Zona.ID
    LEFT JOIN Organization_Structure_Header AS OSH_AreaCarga
        ON OSH_AreaCarga.ERP_No = E.Sector AND OSH_AreaCarga.Department_Code = 'AREA' AND OSH_AreaCarga.[Parent_ID] = OSH_TI.ID

        --LOCAL RESIDENCIA
    LEFT JOIN Employee_Address AS EA
        ON EA.ID = E.Address_ID_1

WHERE   (LOWER(First_Name) LIKE '%REPLACETHIS%'
        OR LOWER(Middle_Name) LIKE '%REPLACETHIS%'
        OR LOWER(Last_Name) LIKE '%REPLACETHIS%'
        OR LOWER(E.Employee_No) = 'REPLACETHIS'
        OR LOWER(Alias) LIKE '%REPLACETHIS%'
        OR LOWER(Sharepoint_User) LIKE '%REPLACETHIS%'

        OR LOWER(EA.[LOCAL]) LIKE LOWER('%REPLACETHIS%')
        OR LOWER(ISNULL(OSH_Dept.[Description] + ' ' + OSH_Sector.[Description], OSH_Zona.[Description] + ' ' + OSH_TI.[Description])) LIKE LOWER('%REPLACETHIS%')
        OR LOWER(ISNULL(OSH_Sector.[Description], OSH_TI.[Description])) LIKE LOWER('%REPLACETHIS%')
        OR LOWER(ISNULL(OSH_Sector.[Description], OSH_TI.[Description])) LIKE LOWER('%REPLACETHIS%')
        OR LOWER(GC_Cat.[Description]) LIKE LOWER('%REPLACETHIS%')
        OR LOWER(E.Employment_Date) LIKE LOWER('%REPLACETHIS%')
        OR LOWER(E.Antiquity_Date) LIKE LOWER('%REPLACETHIS%')

            --AGE RANGE
        OR CONVERT(INT,CONVERT(VARCHAR(30),ISNULL(DATEDIFF(YEAR, E.Birth_Date, GETDATE()), '')) )
            BETWEEN CONVERT(INT,NULLIF(SUBSTRING('REPLACETHIS', 1, CHARINDEX(' e', LOWER('REPLACETHIS'))), ''))
                    AND CONVERT(INT,NULLIF(REPLACE(SUBSTRING(REPLACE(LOWER('REPLACETHIS'), 'e ', 'x'), CHARINDEX('x', REPLACE(LOWER('REPLACETHIS'), 'e ', 'x')),5), 'x', ''), SUBSTRING('REPLACETHIS', 1, 4)))
            --AGE SINGLE
        OR CONVERT(VARCHAR(30),ISNULL(DATEDIFF(YEAR, E.Birth_Date, GETDATE()), '')) = LOWER('REPLACETHIS')

            --EMPLOYEMENT DATE RANGE
        OR DATEPART(YEAR,E.Employment_Date)
            BETWEEN NULLIF(SUBSTRING('REPLACETHIS', 1, CHARINDEX(' e', LOWER('REPLACETHIS'))), '')
                    AND NULLIF(REPLACE(SUBSTRING(REPLACE(LOWER('REPLACETHIS'), 'e ', 'x'), CHARINDEX('x', REPLACE(LOWER('REPLACETHIS'), 'e ', 'x')),5), 'x', ''), SUBSTRING('REPLACETHIS', 1, 4))
            --EMPLOYEMENT DATE SINGLE
        OR CONVERT(VARCHAR(30),DATEPART(YEAR,E.Employment_Date)) = LOWER('REPLACETHIS')

        OR LOWER(E.Income_Category) LIKE LOWER('%REPLACETHIS%')

            --GROSS REMUNERATION RANGE
        OR CONVERT(DECIMAL(9,2),(E.GrossRemuneration + E.AllowanceFailures + E.ShoppingSubsidy + E.TransportSubsidy))
            BETWEEN NULLIF(SUBSTRING('REPLACETHIS', 1, CHARINDEX(' e', LOWER('REPLACETHIS'))), '')
                    AND NULLIF(REPLACE(SUBSTRING(REPLACE(LOWER('REPLACETHIS'), 'e ', 'x'), CHARINDEX('x', REPLACE(LOWER('REPLACETHIS'), 'e ', 'x')),5), 'x', ''), SUBSTRING('REPLACETHIS', 1, 4))
            --GROSS REMUNERATION SINGLE
        OR CONVERT(VARCHAR(30),CONVERT(DECIMAL(9,2),(E.GrossRemuneration + E.AllowanceFailures + E.ShoppingSubsidy + E.TransportSubsidy))) LIKE LOWER('%REPLACETHIS%')

            --EMPLOYEMENT "TIME" RANGE
        OR CONVERT(INT,CONVERT(VARCHAR(30),ISNULL(DATEDIFF(YEAR, E.Employment_Date, GETDATE()), '')))
            BETWEEN CONVERT(INT,NULLIF(SUBSTRING('REPLACETHIS', 1, CHARINDEX(' e', LOWER('REPLACETHIS'))), ''))
                    AND CONVERT(INT,NULLIF(REPLACE(SUBSTRING(REPLACE(LOWER('REPLACETHIS'), 'e ', 'x'), CHARINDEX('x', REPLACE(LOWER('REPLACETHIS'), 'e ', 'x')),5), 'x', ''), SUBSTRING('REPLACETHIS', 1, 4)))
            --EMPLOYEMENT "TIME" SINGLE
        OR CONVERT(VARCHAR(30),ISNULL(DATEDIFF(YEAR, E.Employment_Date, GETDATE()), '')) = LOWER('REPLACETHIS')

            --ASSIDUITY RANGE
        OR (
                SELECT COUNT(*) FROM Employee_Assiduity AS EASS
                    LEFT JOIN Assiduity_Types AS ET
                        ON ET.Code = EASS.Assiduity_Code
                    WHERE EASS.Employee_No = E.Employee_No
                            AND ET.Blocked = 0
                            AND ET.[TYPE] = 0
            )
            BETWEEN CONVERT(INT,NULLIF(SUBSTRING('REPLACETHIS', 1, CHARINDEX(' e', LOWER('REPLACETHIS'))), ''))
                    AND CONVERT(INT,NULLIF(REPLACE(SUBSTRING(REPLACE(LOWER('REPLACETHIS'), 'e ', 'x'), CHARINDEX('x', REPLACE(LOWER('REPLACETHIS'), 'e ', 'x')),5), 'x', ''), SUBSTRING('REPLACETHIS', 1, 4)))
            --ASSIDUITY SINGLE
        OR CONVERT(VARCHAR(30),(
                SELECT COUNT(*) FROM Employee_Assiduity AS EASS
                    LEFT JOIN Assiduity_Types AS ET
                        ON ET.Code = EASS.Assiduity_Code
                    WHERE EASS.Employee_No = E.Employee_No
                            AND ET.Blocked = 0
                            AND ET.[TYPE] = 0
            )) = LOWER('REPLACETHIS')
        AND NULLIF('REPLACETHIS', '') IS NOT NULL)


ORDER BY Name