Forums Últimos mensajes - Powered by IBM
 
Tags Palabras claves

how to assign multiple local variables from select query.

03/02/2009 - 15:32 por Marcus Fenix | Informe spam
Ayuda Hacer una pregunta
Hi,
I've the following Sql commands, All I want to do is assign X,
TotalRequest and CreationPercentage to some locat variables X,Y and Z
respectively so that i may write some code with those variables.
actually i want to use these values in a loop in T-SQL.

declare @X INT
declare @Y INT
declare @Z INT
SELECT
INQ.DeltaTime as X, COUNT(INQ.Request) as TotalRequest,
COUNT(INQ.DeltaTime) / (NULLIF(COUNT(INQ.Request),0))*100 as
CreationPercentage

FROM
(
SELECT TRN_Request.ID as Request,a.ActionDate as a, b.ActionDate as
b,
DATEDIFF(hour, a.ActionDate, b.ActionDate) as DeltaTime
FROM TRN_Request INNER JOIN
TRN_RequestAction as a ON TRN_Request.ID = a.Request AND
(a.RequestActionType = 1) INNER JOIN
TRN_RequestAction as b ON TRN_Request.ID = b.Request AND
(b.RequestActionType = 5)
WHERE (TRN_Request.RequestType = 1) AND (TRN_Request.RequestStatus
= 6)
AND (a.ActionDate>='2009-01-30 10:58:54.000' AND
a.ActionDate<='2010-01-30 10:58:54.000')
) AS INQ
WHERE INQ.DeltaTime<=2
Group by INQ.DeltaTime


_________

I've tried couple of things but couldn't get it right. If anyone can
guide me i'll be really thanful.
email Siga el debateRespuesta 1 respuestaRespuesta Tengo una respuesta
DRAGON AGE™: INQUISITION – Matadragones
 

Leer las respuestas

#1 Alejandro Mesa
03/02/2009 - 15:53 | Informe spam
Marcus Fenix,

You should post the question in the English newsgroup.

If just one row is returned from the grouping statement (see that I added
TOP 1 ... Order BY INQ.DeltaTime ASC), then you can use:

declare @X INT
declare @Y INT
declare @Z INT

select
@X = X,
@Y = TotalRequest,
@Z = CreationPercentage
from
(
SELECT TOP 1
INQ.DeltaTime as X, COUNT(INQ.Request) as TotalRequest,
COUNT(INQ.DeltaTime) / (NULLIF(COUNT(INQ.Request),0))*100 as
CreationPercentage

FROM
(
SELECT TRN_Request.ID as Request,a.ActionDate as a, b.ActionDate as
b,
DATEDIFF(hour, a.ActionDate, b.ActionDate) as DeltaTime
FROM TRN_Request INNER JOIN
TRN_RequestAction as a ON TRN_Request.ID = a.Request AND
(a.RequestActionType = 1) INNER JOIN
TRN_RequestAction as b ON TRN_Request.ID = b.Request AND
(b.RequestActionType = 5)
WHERE (TRN_Request.RequestType = 1) AND (TRN_Request.RequestStatus
= 6)
AND (a.ActionDate>='2009-01-30 10:58:54.000' AND
a.ActionDate<='2010-01-30 10:58:54.000')
) AS INQ
WHERE INQ.DeltaTime<=2
Group by INQ.DeltaTime
Order BY INQ.DeltaTime ASC
) as t


AMB


"Marcus Fenix" wrote:

Hi,
I've the following Sql commands, All I want to do is assign X,
TotalRequest and CreationPercentage to some locat variables X,Y and Z
respectively so that i may write some code with those variables.
actually i want to use these values in a loop in T-SQL.

declare @X INT
declare @Y INT
declare @Z INT
SELECT
INQ.DeltaTime as X, COUNT(INQ.Request) as TotalRequest,
COUNT(INQ.DeltaTime) / (NULLIF(COUNT(INQ.Request),0))*100 as
CreationPercentage

FROM
(
SELECT TRN_Request.ID as Request,a.ActionDate as a, b.ActionDate as
b,
DATEDIFF(hour, a.ActionDate, b.ActionDate) as DeltaTime
FROM TRN_Request INNER JOIN
TRN_RequestAction as a ON TRN_Request.ID = a.Request AND
(a.RequestActionType = 1) INNER JOIN
TRN_RequestAction as b ON TRN_Request.ID = b.Request AND
(b.RequestActionType = 5)
WHERE (TRN_Request.RequestType = 1) AND (TRN_Request.RequestStatus
= 6)
AND (a.ActionDate>='2009-01-30 10:58:54.000' AND
a.ActionDate<='2010-01-30 10:58:54.000')
) AS INQ
WHERE INQ.DeltaTime<=2
Group by INQ.DeltaTime


_________

I've tried couple of things but couldn't get it right. If anyone can
guide me i'll be really thanful.

Preguntas similares

 

Busqueda sugerida :