Help with SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SP

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-03-14 : 11:07:31

Hi, i am trying to create an sp for a asp.net site in work, but i need to manipulate the "T_TP_RESULT.[Value]" field only in cases when 1,2 and 9 by * 0.8850746
CASE t_tp.type
WHEN 1 THEN 'Smin'
WHEN 2 THEN 'SMAX'
WHEN 5 THEN 'ts2'
WHEN 9 THEN 'tgate 158'
ELSE 'other'
END AS testpoint,

how do i write this into the sp

the sp is

CREATE PROCEDURE sp_get_test_point_results1
(
@COMPOUND NVARCHAR (20),
@BATCHNAME NVARCHAR(20),
@TYPE NVARCHAR(10)
)
AS

SELECT T_COMPOUND.Name AS Compound, T_TEST_RESULT.BatchName,
CASE t_tp.type
WHEN 1 THEN 'Smin'
WHEN 2 THEN 'SMAX'
WHEN 5 THEN 'ts2'
WHEN 9 THEN 'tgate 158'
ELSE 'other'
END AS testpoint,
T_TP.CHANNEL_ID, T_TP.Param1, T_TP_RESULT.Id, T_TP_RESULT.[Value], T_TP_RESULT.Orthogonal_Value, t_test_result.date

FROM
T_TP

INNER JOIN
(T_TP_RESULT INNER JOIN
(T_TEST_RESULT INNER JOIN
(T_TEST INNER JOIN T_COMPOUND ON T_TEST.[COMPOUND_ID] = T_COMPOUND.[ID])
ON T_TEST_RESULT.[TEST_ID] = T_TEST.[ID])
ON T_TP_RESULT.[TEST_RESULT_ID] = T_TEST_RESULT.[ID])
ON T_TP.[ID] = T_TP_RESULT.[TP_ID]

WHERE T_COMPOUND.NAME = @COMPOUND
AND T_TEST_RESULT.BATCHNAME =@BATCHNAME
AND T_TP.Type = @TYPE
AND
((T_TP.Type = 1) OR
(T_TP.Type = 2) OR
((T_TP.Type = 5) AND (T_TP.Param1 = 2)) OR
((T_TP.Type = 9) AND (T_TP.Param1 BETWEEN 117 and 119)))
AND T_TP.[CHANNEL_ID] =1

ORDER BY T_COMPOUND.[ID], T_TEST_RESULT.[BATCHNAME], T_TP.[TYPE]
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-14 : 11:15:53

[code]
T_TP.CHANNEL_ID, T_TP.Param1, T_TP_RESULT.Id,
~~T_TP_RESULT.[Value]~~
[Value] = T_TP_RESULT.[Value] * case when t_tp.type in (1, 2, 9) then 0.8850746 else 1.0 end,
T_TP_RESULT.Orthogonal_Value, t_test_result.date
[/code]

KH
[spoiler]Time is always against us[/spoiler]

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-03-14 : 11:24:13

Excellent some times I think it just needs a different set of eyes looking at it sometimes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 11:36:28

[code]CREATE PROCEDURE sp_get_test_point_results1
(
@COMPOUND NVARCHAR (20),
@BATCHNAME NVARCHAR(20),
@TYPE NVARCHAR(10)
)
AS

SET NOCOUNT ON

SELECT T_COMPOUND.Name AS Compound,
T_TEST_RESULT.BatchName,
CASE t_tp.type
WHEN 1 THEN 'Smin'
WHEN 2 THEN 'SMAX'
WHEN 5 THEN 'ts2'
WHEN 9 THEN 'tgate 158'
ELSE 'other'
END AS testpoint,
T_TP.CHANNEL_ID,
T_TP.Param1,
T_TP_RESULT.Id,
CASE T_TP.Type
WHEN IN (1, 2, 9) THEN 0.8850746 * T_TP_RESULT.[Value]
ELSE 1.0 * T_TP_RESULT.[Value]
END AS [Value],
T_TP_RESULT.Orthogonal_Value,
t_test_result.date
FROM T_TP
INNER JOIN T_TP_RESULT ON T_TP_RESULT.[TP_ID] = T_TP.[ID]
INNER JOIN T_TEST_RESULT ON T_TEST_RESULT.[ID] = T_TP_RESULT.[TEST_RESULT_ID]
INNER JOIN T_TEST ON T_TEST.[ID] = T_TEST_RESULT.[TEST_ID]
INNER JOIN T_COMPOUND ON T_COMPOUND.[ID] = T_TEST.[COMPOUND_ID]
WHERE T_COMPOUND.NAME = @COMPOUND
AND T_TEST_RESULT.BATCHNAME = @BATCHNAME
AND T_TP.Type = @TYPE
AND T_TP.[CHANNEL_ID] = 1
AND (
T_TP.Type IN (1, 2)
OR
T_TP.Type = 5 AND T_TP.Param1 = 2
OR
T_TP.Type = 9 AND T_TP.Param1 BETWEEN 117 and 119
)
ORDER BY T_COMPOUND.[ID],
T_TEST_RESULT.[BATCHNAME],
T_TP.[TYPE][/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources