SQL Server Performance

Update Column

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Jun 8, 2013.

  1. immad Member

    This is my query

    Code:
     
    select
    CalDate,
    Timein,
    Timeout ,
    CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime_runtime,
    CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar ) as excesstime_runtime,
    CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout]   THEN 'Excess' else 'Short'
    END as Excess,
    
    from
    #attendance
    
    -- (2) This will calculate and store 'excessshort' &  'SpendTime' in the database later
    
    update
    #attendance
    set
    excessshort =
    CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
    ,-- Only here so visible
    Spend =
    CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
    from
    #attendance
    
    -- Display the result
    select * from #attendance
     
    
    i want to update excess column result in data base
    you can see excess column in select query
  2. davidfarr Member

    I wish I could help but I don't understand your question.

    Your query that you provided is actually two queries (one select and one update), they contain several syntax errors and they seem to contradict each other.
    Your SELECT query is using alias names [t] and [t4] but your query only includes the [#attendance] table which has no alias name in the syntax, which means that query is invalid and incomplete. What objects is [t] and [t4] referencing ?

    Your query includes unecessary join syntax which you can remove as follows;
    CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short'

    Your SELECT query is calculating a new column called [excesstime_runtime] from the [#attendance] table,
    and then your UPDATE query is updating the [excessshort] column, in the same [#attendance] table, with the same calculation syntax that was used to calculate the [excesstime_runtime] column. It makes no sense to do that.

    You have said you want to update "excess column result in data base ", which means you will need to update a permanent table in the database. Updating a #temp table will not change anything in the database.

    One of this forum's moderators has often said;
    "Instead of posting your query, it would have been more beneficial if you've posted a description of your problem, your table structures, some sample data and the requested output."
    I accept the problem that English is not your first language, but unfortunately it is the only language that I can understand with regard to technical problems.
  3. immad Member

    Sir i am sory now i change the query actullay i want to update excess result in select statement
    just like spendtime becouse may be later i need that data to make more reports

    Code:
    select
    t.CalDate,
    t.Timein,
    t.Timeout ,
    CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.[Timein],t.[Timeout]),0),108) AS SpendTime_runtime,
    CAST (ABS( 540 - DATEDIFF (MINUTE, t.[Timein], t.[timeout] ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST (ABS( 540 - DATEDIFF (MINUTE, t.[Timein], t.[timeout] ) ) % 60 as varchar ) as excesstime_runtime,
    CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= 540 and t.[Timein] = t.[Timein] and t.[Timeout] =
    t.[Timeout]  THEN 'Excess' else 'Short'
    END as Excess,
    
    from
    attend t
    
    update
    attend
    set
    excessshort =
    CAST (ABS( 540 - DATEDIFF (MINUTE, t.Timein, t.timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
    CAST (ABS( 540 - DATEDIFF (MINUTE, t.Timein, t.timeout ) ) % 60 as varchar )-- Minutes
    ,
    Spend =
    CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,t.[Timein],t.[Timeout]),0),108)
    from
    attend t
    
    -- Display the result
    select * from attend
    
  4. davidfarr Member

    Like this perhaps ?

    update attend set Excess = CASE WHEN DATEDIFF(MINUTE, [Timein], [Timeout]) >= 540 THEN 'Excess' ELSE 'Short' END
  5. immad Member

    sir there is a problem actullay its a procedure

    when i execute this procedure first time it give that result

    date----------------------------EID----------timein-----------------------------timeout-----------------spendtime------excessshort
    2013-04-11 00:00:00.000---26492-----2013-06-10 13:29:00.000--2013-06-10 15:44:00.000---02:15:00-----------NULL

    then again i execute procedure its give me this result

    date----------------------------EID-------timein-----------------------------timeout-----------------spendtime--------excessshort
    2013-04-11 00:00:00.000---26492-----2013-06-10 13:29:00.000--2013-06-10 15:44:00.000---02:15:00-----6 hrs : 45 min

    if i make a funuction and wriet this update query and link to procedure where i write select query then its give me all result one time

Share This Page