Loading ...
Sorry, an error occurred while loading the content.
 

how to assign multiple local variables from select query.

Expand Messages
  • marcus_fen1x
    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
    Message 1 of 3 , Feb 3, 2009
      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.
    • Noman Aftab
      SELECT TOP 1 @X = INQ.DeltaTime, @Y = COUNT(INQ.Request), @Z = COUNT(INQ.DeltaTime ) / (NULLIF(COUNT(INQ.Request) ,0))*100   FROM ( SELECT TRN_Request. ID as
      Message 2 of 3 , Feb 4, 2009
        SELECT TOP 1
        @X = INQ.DeltaTime,
        @Y = COUNT(INQ.Request),
        @Z = COUNT(INQ.DeltaTime ) / (NULLIF(COUNT(INQ.Request) ,0))*100
         
        FROM
        (
        SELECT TRN_Request. ID as Request,a.ActionDat e 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.RequestActionTyp e = 1) INNER JOIN
        TRN_RequestAction as b ON
        TRN_Request. ID = b.Request AND (b.RequestActionTyp e = 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



         
        --- On Tue, 3/2/09, marcus_fen1x <faux.amis@...> wrote:

        From: marcus_fen1x <faux.amis@...>
        Subject: [SQLQueriesNoCode] how to assign multiple local variables from select query.
        To: SQLQueriesNoCode@yahoogroups.com
        Date: Tuesday, 3 February, 2009, 7:35 PM






        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.ActionDat e 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.RequestActionTyp e = 1) INNER JOIN
        TRN_RequestAction as b ON
        TRN_Request. ID = b.Request AND (b.RequestActionTyp e = 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.


















        [Non-text portions of this message have been removed]
      • Noman Aftab
        If your inner query will be retunring more that 1 row, then you probably need cursors to iterate on the resultset. Regards, Noman Aftab   ... From: Noman
        Message 3 of 3 , Feb 4, 2009
          If your inner query will be retunring more that 1 row, then you probably need cursors to iterate on the resultset.

          Regards,
          Noman Aftab
           

          --- On Wed, 4/2/09, Noman Aftab <noman17pk@...> wrote:

          From: Noman Aftab <noman17pk@...>
          Subject: Re: [SQLQueriesNoCode] how to assign multiple local variables from select query.
          To: SQLQueriesNoCode@yahoogroups.com
          Date: Wednesday, 4 February, 2009, 7:21 PM






          SELECT TOP 1
          @X = INQ.DeltaTime,
          @Y = COUNT(INQ.Request) ,
          @Z = COUNT(INQ.DeltaTime ) / (NULLIF(COUNT( INQ.Request) ,0))*100
           
          FROM
          (
          SELECT TRN_Request. ID as Request,a.ActionDat e 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.RequestActionTyp e = 1) INNER JOIN
          TRN_RequestAction as b ON
          TRN_Request. ID = b.Request AND (b.RequestActionTyp e = 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

           
          --- On Tue, 3/2/09, marcus_fen1x <faux.amis@yahoo. com> wrote:

          From: marcus_fen1x <faux.amis@yahoo. com>
          Subject: [SQLQueriesNoCode] how to assign multiple local variables from select query.
          To: SQLQueriesNoCode@ yahoogroups. com
          Date: Tuesday, 3 February, 2009, 7:35 PM

          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.ActionDat e 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.RequestActionTyp e = 1) INNER JOIN
          TRN_RequestAction as b ON
          TRN_Request. ID = b.Request AND (b.RequestActionTyp e = 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.

          [Non-text portions of this message have been removed]


















          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.