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: Is silly bad?

Stefan_Lang26-Oct-12 3:51

Stefan_Lang

26-Oct-12 3:51

I am aware that main returns an int, but in most cases it is used as a bool (if at all). And that is my whole point. I've seen lots of programs that return various different return codes, but I've seen very few script files calling C-programs that actually check any other return value than 0.

Re: Is silly bad?

Paulo Zemek30-Oct-12 4:43

Paulo Zemek

30-Oct-12 4:43

In that particular case, it is not a boolean... it is an error code.
Or, if you want, a boolean that says: Error!

So, a value of true = it has errors. A value of false is: There are no errors.
There is no reason to say that 0 is TRUE. That's a bad practice, independent if it works.

Re: Is silly bad?

BlackMilan29-Oct-12 23:09

BlackMilan

29-Oct-12 23:09

Stefan_Lang wrote:
In any case pretty much all the previous programmers came from old style C and
weren't used to the type bool just yet.

Yes, there was no data type, but it was clearly defined: Zero was false, everything else true. So any data type could be a boolean.

This was very common for C programmers:

C++

char b = x > y;

Don't think, the Earth was a disc before .NET!

But I've seen something like this from many native VB programmers:

bool b;
if (x > y)
    b = true;
else
    b = false;

Re: Is silly bad?

Stefan_Lang30-Oct-12 0:13

Stefan_Lang

30-Oct-12 0:13

I've started programming in C around 1985, C++ in 1986. And I've seen my share of #define TRUE 0 or equivalent. It may not have been the norm, and it clearly wasn't sensible when you think about it, but then not many people bothered to think about coding sense at university as long as it worked. (and it does as long as you don't try to directly assign conditions)

Anyway, this was just one possible reason I considered for the code example I gave in my original posting - it is not as such related to the topic, just what I consider a possible cause.

Re: Is silly bad?

goodsport30-Oct-12 15:41

goodsport

30-Oct-12 15:41

In the old days, I'd do this

#define FALSE (1 == 0)
#define TRUE (!FALSE)

or, conversely (but settled on the former for some reason),

#define TRUE (1 == 1)
#define FALSE (!TRUE)

Comparing these to the result of a logical operation would always work, by definition, but, since I prefer the "short form", these were usually just assigned to variables instead of compared.

Re: Is silly bad?

Member 460889831-Oct-12 21:20

Member 4608898

31-Oct-12 21:20

I've seen the following written by people who've been programming for over 25 years. Not everyone understands terse code. Even less understand what macros were originally made for.

C++

bool flag;
if (a > b)
   flag = true;
else
   flag = false;

Re: Is silly bad?

Paulo Zemek30-Oct-12 3:23

Paulo Zemek

30-Oct-12 3:23

I don't know if this is C++ or C#, but my experience with beginner programmers is that the ? and : operators are the hardest ones to understand.

I am pretty sure that many people that I already worked will be sure that:

bool flag = (a > b);

Will be true if A is greater than B.

With the ? and :, they will think if it is really putting true when A is greater than B, or if it is exactly the opposite.

So I will say that it is not only a matter of readibility, but a matter on how used people is the the construction itself. I personally never user : and ? operators.

Re: Is silly bad?

Fabio Franco30-Oct-12 10:43

Fabio Franco

30-Oct-12 10:43

I agree.

Today I'm very trained to immediately understand the ternary operator's meaning. But, many are not so comfortable and it could confuse even more.

To me assigning the comparison is as easy as the ternary operator form, but I believe more people would have a harder time to understand the ternary operator form than the opposite.

If someone really wants it to be more clear than that, he can:

bool flag;
if (a > b)
   flag = true;
else
   flag = false;

It can't get more readable than that (ok, maybe it can, but I can't think of any right now).

To alcohol! The cause of, and solution to, all of life's problems - Homer Simpson
----
Our heads are round so our thoughts can change direction - Francis Picabia

Re: Is silly bad?

lucien6431-Oct-12 5:13

lucien64

31-Oct-12 5:13

You may get it wrong, and because of that you need to "correct" the code and even ask the programmer to fix it. I would do so!

_____________________
I'm a nobody and nobody is perfect!

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

devenv.exe24-Oct-12 20:07

devenv.exe

24-Oct-12 20:07

http://forums.asp.net/p/1853860/5190798.aspx/1?Getting+last+record+duplicate+how+can+i+solve+this+issue+[^]

"Coming soon"

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

HaBiX24-Oct-12 20:29

HaBiX

24-Oct-12 20:29

The code is unreadable - please use formatting and actually write what the issue is..

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

devenv.exe24-Oct-12 20:32

devenv.exe

24-Oct-12 20:32

Actually, it isn't me asking the question over there, so the code isn't mine. The first time i looked at that code, i just closed my eyes.

"Coming soon"

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

OriginalGriff24-Oct-12 21:32

OriginalGriff

24-Oct-12 21:32

The cure for that is obvious: "get a new job"

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

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/

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