Here it is, in all its gory. A prime candidate for the use of inline TVFs, if there ever was one.
At least that would give you the chance to argue about the correctness without going mad.
And I'm not even mentioning the stringified params which should be DATETIME plus two other DATETIMEs split up as INTs.
Curiously, in this guys world the default length for VARCHAR seems to be 100, not 50 (probably "just to make sure").
USE [INVATTENDENCE] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
ALTER PROC [dbo].[SP_COST_OF_RESOURCE_MAN_POWER] (
@IN_FDAY INT
,@IN_TDAY INT
,@IN_FMONTH INT
,@IN_TMONTH INT
,@IN_FYEAR INT
,@IN_TYEAR INT
,@IN_CONTRACTOR_ID INT
,@IN_TYPE VARCHAR(100)
,@IN_FROM_DATE VARCHAR(100)
,@IN_TO_DATE VARCHAR(100)
,@IN_CONTRACTOR_NAME VARCHAR(100)
,@IN_SITEID INT
)
AS
BEGIN
SELECT DISTINCT @IN_FROM_DATE AS FROM_DATE
,@IN_TO_DATE AS TO_DATE
,@IN_CONTRACTOR_NAME AS CONTRACTOR_NAME
,dbo.CONTRACTOR_SPECIFICATIONS.CONTRACTOR_NAME
,dbo.MANPOWER_CATEGORY.MAN_CAT_NAME
,SUM(CAST(CASE
WHEN (
ets.FIRST_SHIFT = 0
OR ets.FIRST_SHIFT IS NULL
)
THEN 0
ELSE ets.FIRST_SHIFT
END AS DECIMAL(10, 1)) + CAST(CASE
WHEN (
ets.SECOND_SHIFT = 0
OR ets.SECOND_SHIFT IS NULL
)
THEN 0
ELSE ets.SECOND_SHIFT
END AS DECIMAL(10, 1)) + CAST(CASE
WHEN (
ets.THIRD_SHIFT = 0
OR ets.THIRD_SHIFT IS NULL
)
THEN 0
ELSE ets.THIRD_SHIFT
END AS DECIMAL(10, 1))) AS hrs_deployed
,CONVERT(DECIMAL(10, 1), (
SUM(CAST(CASE
WHEN (
ets.FIRST_SHIFT IS NULL
OR ets.FIRST_SHIFT = 0
)
THEN 0
ELSE ets.FIRST_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.SECOND_SHIFT IS NULL
OR ets.SECOND_SHIFT = 0
)
THEN 0
ELSE ets.SECOND_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.THIRD_SHIFT IS NULL
OR ets.THIRD_SHIFT = 0
)
THEN 0
ELSE ets.THIRD_SHIFT
END AS DECIMAL(10, 1)))
) / convert(DECIMAL(10, 2), dbo.RATE_SPECIFICATIONS_MAN.NO_OF_HR_BASIS)) AS days_worked
,convert(DECIMAL(10, 1), (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.STATUTORY_DUE_PAYABLE_BY = 'Contractor'
THEN (
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
OR DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE = 0
)
THEN 0
ELSE (
(
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
) / 30.0
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
(
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
OR DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE = 0
)
THEN 0
ELSE (
(
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
) / 30.0
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
) - (0)
)
END
)) AS RATE_DAY
,convert(DECIMAL(10, 1), (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.STATUTORY_DUE_PAYABLE_BY = 'Contractor'
THEN (
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
OR DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE = 0
)
THEN 0
ELSE (
(
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
) / 30.0
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
(
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
OR DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE = 0
)
THEN 0
ELSE (
(
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
) / 30.0
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
) - (0)
)
END
) * (
CONVERT(DECIMAL(10, 1), (
SUM(CAST(CASE
WHEN (
ets.FIRST_SHIFT IS NULL
OR ets.FIRST_SHIFT = 0
)
THEN 0
ELSE ets.FIRST_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.SECOND_SHIFT IS NULL
OR ets.SECOND_SHIFT = 0
)
THEN 0
ELSE ets.SECOND_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.THIRD_SHIFT IS NULL
OR ets.THIRD_SHIFT = 0
)
THEN 0
ELSE ets.THIRD_SHIFT
END AS DECIMAL(10, 1)))
) / convert(DECIMAL(10, 2), dbo.RATE_SPECIFICATIONS_MAN.NO_OF_HR_BASIS))
)
)) AS TOTAL_COST_TO_CONTRACTOR
,convert(DECIMAL(10, 2), (
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) IS NULL
THEN 0
ELSE (
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) / 30
)
END
)
ELSE (
CASE
WHEN (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) IS NULL
THEN 0
ELSE (
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
)
)
END
)
END
)
ELSE (
CASE
WHEN (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) IS NULL
THEN 0
ELSE (
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
)
)
END
)
END
)) AS ST_DUE_BY_FPONM
,CONVERT(DECIMAL(10, 2), (
(
convert(DECIMAL(10, 2), (
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) IS NULL
THEN 0
ELSE (
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) / 30
)
END
)
ELSE (
CASE
WHEN (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) IS NULL
THEN 0
ELSE (
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
)
)
END
)
END
)
ELSE (
CASE
WHEN (
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
) IS NULL
THEN 0
ELSE (
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_PF IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_PF / 100))
END
) + (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC IS NULL
THEN 0
ELSE ((dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE) * (DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC / 100))
END
)
)
)
END
)
END
))
) * (
CONVERT(DECIMAL(10, 1), (
SUM(CAST(CASE
WHEN (
ets.FIRST_SHIFT IS NULL
OR ets.FIRST_SHIFT = 0
)
THEN 0
ELSE ets.FIRST_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.SECOND_SHIFT IS NULL
OR ets.SECOND_SHIFT = 0
)
THEN 0
ELSE ets.SECOND_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.THIRD_SHIFT IS NULL
OR ets.THIRD_SHIFT = 0
)
THEN 0
ELSE ets.THIRD_SHIFT
END AS DECIMAL(10, 1)))
) / convert(DECIMAL(10, 2), dbo.RATE_SPECIFICATIONS_MAN.NO_OF_HR_BASIS))
)
)) AS DUES_PAYBLE_BY_FPONM
,CONVERT(DECIMAL(10, 2), (
(
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.ISSUES_FROM_STORES = 0
OR DBO.DEDUCTION_DETAILS.ISSUES_FROM_STORES IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.ISSUES_FROM_STORES)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.ADVANCES = 0
OR DBO.DEDUCTION_DETAILS.ADVANCES IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.ADVANCES)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY = 0
OR DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.PHONE_FACILITY = 0
OR DBO.DEDUCTION_DETAILS.PHONE_FACILITY IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.PHONE_FACILITY)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY = 0
OR DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.OTHERS = 0
OR DBO.DEDUCTION_DETAILS.OTHERS IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.OTHERS)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
)
)) AS DEDUCTIONS
,convert(DECIMAL(10, 1), (
(
(
(
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.STATUTORY_DUE_PAYABLE_BY = 'Contractor'
THEN (
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
OR DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE = 0
)
THEN 0
ELSE (
(
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
) / 30.0
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
(
CASE
WHEN DBO.CONTRACT_TYPE.CONTRACT_TYPES = 'RATE'
THEN (
CASE
WHEN DBO.RATES.RATE_TYPE = 'MONTHLY'
THEN (
CASE
WHEN (
DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
OR DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE = 0
)
THEN 0
ELSE (
(
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
) / 30.0
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
)
ELSE (
CASE
WHEN DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE IS NULL
THEN 0
ELSE (
CASE
WHEN (
SELECT avg(cast((DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE) AS DECIMAL(10, 1)))
FROM dbo.RATE_SPECIFICATIONS_MAN
WHERE CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.FROM_DATE AS DATE), 105) >= @IN_FROM_DATE
AND CONVERT(VARCHAR(10), CAST(dbo.RATE_SPECIFICATIONS_MAN.TO_DATE AS DATE), 105) <= @IN_TO_DATE
) = 0
THEN 0
ELSE cast(DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE AS DECIMAL(10, 1))
END
)
END
)
END
) - (0)
)
END
) * (
CONVERT(DECIMAL(10, 1), (
SUM(CAST(CASE
WHEN (
ets.FIRST_SHIFT IS NULL
OR ets.FIRST_SHIFT = 0
)
THEN 0
ELSE ets.FIRST_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.SECOND_SHIFT IS NULL
OR ets.SECOND_SHIFT = 0
)
THEN 0
ELSE ets.SECOND_SHIFT
END AS DECIMAL(10, 1))) + SUM(CAST(CASE
WHEN (
ets.THIRD_SHIFT IS NULL
OR ets.THIRD_SHIFT = 0
)
THEN 0
ELSE ets.THIRD_SHIFT
END AS DECIMAL(10, 1)))
) / convert(DECIMAL(10, 2), dbo.RATE_SPECIFICATIONS_MAN.NO_OF_HR_BASIS))
)
)
) - (
CONVERT(DECIMAL(10, 2), (
CONVERT(DECIMAL(10, 2), (
(
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.ISSUES_FROM_STORES = 0
OR DBO.DEDUCTION_DETAILS.ISSUES_FROM_STORES IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.ISSUES_FROM_STORES)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.ADVANCES = 0
OR DBO.DEDUCTION_DETAILS.ADVANCES IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.ADVANCES)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY = 0
OR DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.PHONE_FACILITY = 0
OR DBO.DEDUCTION_DETAILS.PHONE_FACILITY IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.PHONE_FACILITY)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY = 0
OR DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
) + (
cast(CASE
WHEN (
DBO.DEDUCTION_DETAILS.OTHERS = 0
OR DBO.DEDUCTION_DETAILS.OTHERS IS NULL
)
THEN 0
ELSE (
SELECT SUM(DBO.DEDUCTION_DETAILS.OTHERS)
FROM DBO.DEDUCTION_DETAILS
WHERE (
DBO.DEDUCTION_DETAILS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
(substring(convert(VARCHAR, DBO.DEDUCTION_DETAILS.YEAR), 3, 2)) BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND (DBO.DEDUCTION_DETAILS.TYPE = 'EQUIPMENT')
AND (DBO.DEDUCTION_DETAILS.CONTRACTOR_ID = @IN_CONTRACTOR_ID)
)
END AS DECIMAL(10, 2))
)
))
))
)
)) AS TOTAL
FROM dbo.EMPLOYEE_SPECIFICATIONS AS es
INNER JOIN dbo.MANPOWER_CATEGORY ON es.CATEGORY = dbo.MANPOWER_CATEGORY.MAN_CAT_NAME
INNER JOIN dbo.CONTRACTOR_SPECIFICATIONS ON es.CONTRACTOR_ID = dbo.CONTRACTOR_SPECIFICATIONS.CONTRACTOR_ID
LEFT JOIN DBO.DEDUCTION_DETAILS ON DBO.CONTRACTOR_SPECIFICATIONS.CONTRACTOR_ID = DBO.DEDUCTION_DETAILS.CONTRACTOR_ID
LEFT JOIN dbo.RATE_SPECIFICATIONS_MAN ON dbo.MANPOWER_CATEGORY.MAN_CAT_ID = dbo.RATE_SPECIFICATIONS_MAN.MAN_CAT_ID
LEFT JOIN dbo.CONTRACT_TYPE ON dbo.RATE_SPECIFICATIONS_MAN.CONTRACT_TYPE_ID = dbo.CONTRACT_TYPE.CONTRACT_TYPE_ID
LEFT JOIN dbo.RATES ON dbo.RATE_SPECIFICATIONS_MAN.RATE_ID = dbo.RATES.RATE_ID
LEFT OUTER JOIN dbo.EMP_TIME_SHEET AS ets ON dbo.CONTRACTOR_SPECIFICATIONS.CONTRACTOR_ID = ets.CONTRACTOR_ID
AND es.EMP_ID = ets.EMP_ID
WHERE (
(
ets.DAY BETWEEN @IN_FDAY
AND @IN_TDAY
)
AND (
ETS.MONTH BETWEEN @IN_FMONTH
AND @IN_TMONTH
)
AND (
ETS.YEAR BETWEEN @IN_FYEAR
AND @IN_TYEAR
)
AND DBO.CONTRACTOR_SPECIFICATIONS.CONTRACTOR_ID = @IN_CONTRACTOR_ID
AND es.SITE_ID = @IN_SITEID
AND (
DBO.CONTRACTOR_SPECIFICATIONS.SERVICE_DETAILS = 'MANPOWER'
OR DBO.CONTRACTOR_SPECIFICATIONS.SERVICE_DETAILS = 'BOTH'
)
)
GROUP BY dbo.MANPOWER_CATEGORY.MAN_CAT_NAME
,dbo.CONTRACTOR_SPECIFICATIONS.CONTRACTOR_NAME
,dbo.RATE_SPECIFICATIONS_MAN.BASE_RATE
,
modified 26-Oct-12 1:28am.
|