The Weird and The Wonderful

The Weird and The Wonderful forum is a place to post Coding Horrors, Worst Practices, and the occasional flash of brilliance.

We all come across code that simply boggles the mind. Lazy kludges, embarrassing mistakes, horrid workarounds and developers just not quite getting it. And then somedays we come across - or write - the truly sublime.

Post your Best, your worst, and your most interesting. But please - no programming questions . This forum is purely for amusement and discussions on code snippets. All actual programming questions will be removed.

Re: A very terrifying stored procedure: how can you solve this issue really ???

fjdiewornncalwe25-Oct-12 3:00

fjdiewornncalwe

25-Oct-12 3:00

wouldyoulikefrieswiththat?

I wasn't, now I am, then I won't be anymore.

Re: A very terrifying stored procedure: how can you solve this issue really ???

BobJanova24-Oct-12 22:22

BobJanova

24-Oct-12 22:22

Re: A very terrifying stored procedure: how can you solve this issue really ???

ZurdoDev25-Oct-12 2:03

ZurdoDev

25-Oct-12 2:03

Just COALESCE it. That fixes it every time.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: A very terrifying stored procedure: how can you solve this issue really ???

ENOTTY25-Oct-12 19:17

ENOTTY

25-Oct-12 19:17

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

SQL

USE [INVATTENDENCE] GO /****** Object: StoredProcedure [dbo].[SP_COST_OF_RESOURCE_MAN_POWER] Script Date: 10/25/2012 09:31:52 ******/

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
		,-- ets.DAY, ets.MONTH, ets.YEAR, DBO.RATE_SPECIFICATIONS_MAN.TOTAL_RATE,DBO.CONTRACT_TYPE.CONTRACT_TYPES,DBO.RATES.RATE_TYPE,RATE_SPECIFICATIONS_MAN.STATUTORY_DUE_PAYABLE_BY,DBO.RATE_SPECIFICATIONS_MAN.ADD_PF,DBO.RATE_SPECIFICATIONS_MAN.ADD_ESIC,DBO.DEDUCTION_DETAILS.ADVANCES,DBO.DEDUCTION_DETAILS.CANTEEN_FACILITY,DBO.DEDUCTION_DETAILS.OTHERS,DBO.DEDUCTION_DETAILS.PHONE_FACILITY,DBO.DEDUCTION_DETAILS.TRASPORT_FACILITY,ISSUES_FROM_STORES,dbo.RATE_SPECIFICATIONS_MAN.NO_OF_HR_BASIS

--order by dbo.MANPOWER_CATEGORY.MAN_CAT_NAME END

--- EXEC SP_COST_OF_RESOURCE_MAN_POWER 01,31,10,11,12,12,126,'','01-10-2012','31-11-2012','NARASIMHA',1
--- EXEC SP_COST_OF_RESOURCE_MAN_POWER 01,31,10,11,12,12,125,'','01-10-2012','31-11-2012','NARASIMHA',1

modified 26-Oct-12 1:28am.

Re: A very terrifying stored procedure: how can you solve this issue really ???

Brisingr Aerowing25-Oct-12 19:24

Brisingr Aerowing

25-Oct-12 19:24

I think computer viruses should count as life. I think it says something about human nature that the only form of life we have created so far is purely destructive. We've created life in our own image.
Stephen Hawking

Re: A very terrifying stored procedure: how can you solve this issue really ???

Peter_in_278025-Oct-12 20:30

Peter_in_2780

25-Oct-12 20:30

and that is why they invented TL;DR

Cheers,
Peter

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

Re: A very terrifying stored procedure: how can you solve this issue really ???

Gary Wheeler26-Oct-12 0:15

Gary Wheeler

26-Oct-12 0:15

For some inexplicable reason, that makes it worse.

Software Zen: delete this;

Re: A very terrifying stored procedure: how can you solve this issue really ???

Brisingr Aerowing25-Oct-12 19:23

Brisingr Aerowing

25-Oct-12 19:23

Bug of the day

Chris Maunder23-Oct-12 4:51

Chris Maunder

23-Oct-12 4:51

public int MyValue { get; set; }

public MyObject(int myValue, ...)
{
    ...
    MyValue = MyValue;
}

cheers,
Chris Maunder

The Code Project | Co-founder
Microsoft C++ MVP

Re: Bug of the day

fjdiewornncalwe23-Oct-12 5:04

fjdiewornncalwe

23-Oct-12 5:04

And don't anybody say they haven't done this before... Smile | :)

I wasn't, now I am, then I won't be anymore.

Re: Bug of the day

Ravi Bhavnani23-Oct-12 5:09

Ravi Bhavnani

23-Oct-12 5:09

Another case of DWIM vs. DWIS.

/ravi

My new year resolution: 2048 x 1536
Home | Articles | My .NET bits | Freeware
ravib(at)ravib(dot)com

Re: Bug of the day

Christoph Keller23-Oct-12 5:19

Christoph Keller

23-Oct-12 5:19

bad one

That's why I always let resharper generate the properties directly from the Ctor parameters. Never ever again Wink | ;)

Best regards and happy coding,
chris

New Url Un-Shortener available: http://dotnetcorner.ch/tools/unshortener/

Check out my website http://dotnetcorner.ch/ and my blog http://blog.dotnetcorner.ch/

Re: Bug of the day

Chris Meech23-Oct-12 5:24

Chris Meech

23-Oct-12 5:24

Some languages would let you know about something called an unreferenced parameter. Poke tongue | ;-P

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: Bug of the day

Sentenryu23-Oct-12 5:28

Sentenryu

23-Oct-12 5:28

Chris Meech wrote:
Some languages would let you know about something called an unreferenced
parameter.

some will call you a idiot if you try to assign a variable to itself Blush | :O

I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)

Re: Bug of the day

OriginalGriff23-Oct-12 8:15

OriginalGriff

23-Oct-12 8:15

Which isn't what it's doing: they are just symantic sugar for

SetProperty(GetProperty());

And although it is a really bad idea, I have seen code where

MyProperty = MyProperty;

Has a effect. Nasty, but it can be done.
(I even found myself coding it once, and had to quickly refactor a bunch of code to get rid of it)

Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water

Re: Bug of the day

Sentenryu23-Oct-12 8:37

Sentenryu

23-Oct-12 8:37

my bad, don't see it's a property...

but even then, i already received warnings about assigning a property to himself... maybe it was one of the statical analysers we had here for a while.

They usually were wrong about the code, so the architect took them away...

I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)

Re: Bug of the day

fjdiewornncalwe23-Oct-12 8:55

fjdiewornncalwe

23-Oct-12 8:55

OriginalGriff wrote:
Has a effect. Nasty, but it can be done.

The good old "Let's put a bunch of logic in a property setter" mentality. Smile | :)

Seen that too.

I wasn't, now I am, then I won't be anymore.

Re: Bug of the day

Sentenryu23-Oct-12 23:27

Sentenryu

23-Oct-12 23:27

That would make sense, but only if the logic is just to validate the value, and i can't think of a case where doing MyProp = MyProp should do something D'Oh! | :doh:

Converselly, i had to port a system, writen in vb 6, where all the properties had a explicit check for this, because otherwise this case would screw everything D'Oh! | :doh:

Oh that moments when you catch yourself thinking if the original coder had too much or too little cafeine in their blood...

I'm brazilian and english (well, human languages in general) aren't my best skill, so, sorry by my english. (if you want we can speak in C# or VB.Net =p)

Re: Bug of the day

BobJanova23-Oct-12 23:55

BobJanova

23-Oct-12 23:55

I have actually written that at some point. I can't remember exactly why but it was something like triggering the data binding notification process. The line got a comment to say why I was doing it, though!

Re: Bug of the day

Jan Steyn24-Oct-12 21:48

Jan Steyn

24-Oct-12 21:48

I always call a specific databinding method. Exactly because of the confusion it could cause, experienced first hand from code that I took over on one of my supported systems.

Message Removed

22-Oct-12 18:39

Aadhar Joshi

22-Oct-12 18:39

Message Removed

What was the most interesting, funny or silly Code you have ever read?

Member 858314522-Oct-12 0:36

Member 8583145

22-Oct-12 0:36

At the Company i am working in the Intellicence gave me These two methods.

Microcernel.Create<iprinter>().
1. PrintLabel()
2. PrintLable()

Both methods contained the same oneliner.
this._Label();

Re: What was the most interesting, funny or silly Code you have ever read?

LloydA11122-Oct-12 0:58

LloydA111

22-Oct-12 0:58

I'm not sure, but I've always thought it would be funny to leave this in some code and see people's faces:

C++

if(10 != 10){
	abort();
}

=====
\ | /
 \|/
  |
  |-----|
  |     |
  |_    |
   _)   | /
   _)  __/_
   _)  ____
  |    /|
  |   / |
  |     |
  |-----|
  |
=====
 ===
  =

Re: What was the most interesting, funny or silly Code you have ever read?

Rage22-Oct-12 1:41

Rage

22-Oct-12 1:41

You need to combine that with an adapted vague comment:

C++

// Perform t53-i check on the v3-23 kernel.
// Abort if failure.

~RaGE();

I think words like 'destiny' are a way of trying to find order where none exists. - Christian Graus
Do not feed the troll ! - Common proverb

Re: What was the most interesting, funny or silly Code you have ever read?

Member 460889823-Oct-12 18:27

Member 4608898

23-Oct-12 18:27

I've seen that in older Fortran programs on compilers for systems that didn't have comparison against constants in their assembly language. They saved zero in a particular location and all comparisons were done against that location. Occasionally, someone would unknowingly pass 0 into a routine in the parameter position where the value was changed by the routine. That is when zero stopped becoming zero and was something completely different. It read something like

if ((0+1) .ne. 1) call abend

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.