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

[devenv exe]

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"

[OriginalGriff]

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

[BobJanova]

:omg:

[ZurdoDev]

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.

[fjdiewornncalwe]

wouldyoulikefrieswiththat?

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

[ENOTTY]

Here it is, in all its gory. A prime candidate for the use of inline TVFs, if there ever was one. At least that would give you the chance to argue about the correctness without going mad. And I'm not even mentioning the stringified params which should be DATETIME plus two other DATETIMEs split up as INTs. Curiously, in this guys world the default length for VARCHAR seems to be 100, not 50 (probably "just to make sure").

USE [INVATTENDENCE] GO /****** 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 D

[Brisingr Aerowing]

:eek:

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

[Brisingr Aerowing]

:eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek::eek:

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

[Peter_in_2780]

and that is why they invented TL;DR Cheers, Peter

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

[Gary Wheeler]

For some inexplicable reason, that makes it worse.

Software Zen: delete this;