Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Exclamation Unanswered: Impossible Function

    Hi! all

    I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure

    CREATE PROCEDURE [update_Alarm_1]

    declare @datetime1 ?????

    declare @datetime2 ?????

    [SELECT Coming
    FROM Student_Log
    where datepart(hour, AL_Coming) = 9
    and
    AL_State = 0 ] = @datetime1



    [SELECT Coming
    FROM Student_Log
    where datepart(hour, AL_Coming) = 9
    and
    AL_State = 1 ] = @datetime2



    [SELECT date_part( 'minute', datetime1 - datetime2 )] = @result



    AS UPDATE [Time_Result]

    SET [Comment] = @Comment_2

    WHERE
    ( [result] = @result)

    Thanks in Advance
    Programming could be Addictive!!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ZurinetA
    I need to store the output of two select statement in a Temporal Table inside a storeProcedure.
    no, you do not

    what you actually need is merely the difference in minutes between two datetime values

    you are struggling with set concepts, aren't you?

    relational databases are best handled with set concepts, not record-at-a-time processing concepts

    you do not need a stored proc

    you need a query which will allow you to use the DATEFIFF function

    you've started three or four threads here on the same topic

    my advice is to go to your student advisor or lecturer and get some help to understand how queries manipulate result sets, not single rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I completely agree withj Rudy, see this thread
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jan 2005
    Location
    Zurich Switzerland
    Posts
    17

    Question Question formation Error ;-)

    Quote Originally Posted by r937
    no, you do not

    what you actually need is merely the difference in minutes between two datetime values

    you are struggling with set concepts, aren't you?

    relational databases are best handled with set concepts, not record-at-a-time processing concepts

    you do not need a stored proc

    you need a query which will allow you to use the DATEFIFF function

    you've started three or four threads here on the same topic

    my advice is to go to your student advisor or lecturer and get some help to understand how queries manipulate result sets, not single rows

    Hi! Still thank you for your help.. Below is actually what I wanted.. Two Select statement in an SP and later update..
    However the diffrence now is I am the last diffrence b/w the two last columns.. I need to get the sum of Individual querys before I compute them with the datediff function..


    CREATE PROCEDURE Sp_GetLogTime(@hour char(2), @period char(20))

    AS

    declare @going datetime
    declare @coming datetime
    declare @result int

    SELECT @going = AL_Coming
    FROM Student_Log
    where datepart(hour, AL_Coming) = @hour
    and AL_State = 1

    SELECT @coming = AL_Coming
    FROM Student_Log
    where datepart(hour, AL_Coming) = @hour
    and AL_State = 0


    SELECT @result = dateDiff( minute, @coming, @going )


    UPDATE Total_Student_Time

    SET Duration = @result

    where ID = @period
    go



    EXECUTE Sp_GetLogTime '5', 'DownTimeNight05'
    Programming could be Addictive!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •