SQL Server Performance

Problem in Joining Tables

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

  1. immad Member

    how to convert hours into minute in sql server 2005
  2. davidfarr Member

    Minutes = Hours * 60
    Will that do ?

    What query are you trying to run ? Does the conversion require a specific format or does it read values from a specific format ?
  3. immad Member

    actullay sir this is my shift tabel structure

    CREATE TABLE [dbo].[Shift]
    (
    [TimeIn] [datetime] NULL,
    [TimeOut] [datetime] NULL,
    [Totaltime] [datetime] NULL,
    )

    this is a query

    select
    *,CONVERT(varchar(20),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS TotalTime
    from shift
    result show like this

    ------timein--------------------------------------------timeout-----------------------------------TotalTime-
    -2013-01-23 07:00:00.000--------2013-01-23 15:00:00.000------------------------------08:00:00

    now i want to show this type of data

    ------timein---------------------------------timeout-----------------------------------TotalTime-------minute
    -2013-01-23 07:00:00.000--------2013-01-23 15:00:00.000------------------08:00:00----------480

    i want to totaltime hour minute mean 8 hour * 60 = 480 and 480 show in minute column
  4. davidfarr Member

    Are you quite sure that your [Totaltime] field is a [datetime] data type ? Your example value of 08:00:00 is certainly not a [datetime] data type because it has no date.
    Your query from the other thread was converting the [Totaltime] value to a [varchar(8)] data type when calculating the TotalTime column. It therefore seems to me that your [Totaltime] column should then also be a [varchar(8)] to carry that value.

    For now, I would suggest that Minutes can be calculated as DATEDIFF(minute,timein,timeout)
  5. immad Member

    Sir i give u detail

    please tell me how to select query and update that result in a table

    this is my procedure how i can update that procedure result in table(attedni)

    ALTER procedure [dbo].[query]
    (
    @empid nvarchar(50),
    @FromDateDatetime,
    @ToDateDatetime,
    @CIDint,
    @BIDint,
    @DeptVARCHAR(50)
    )

    as
    begin
    select

    [date],
    min([Timein]) as First_Record,
    sum(DATEDIFF(ss, [Timein], [Timeout])) as Time_In_Seconds

    into #temp1 from attendi
    where eid=17090
    group by [date]
    --GO
    select
    --t3.cid,
    t.[date],
    e.ename,
    t.eid,
    t5.dname,
    d.name designation,
    T4.shift,
    --d.Name,
    --d.name,
    t.[Timein],
    t.[Timeout],

    --s.shift,
    CONVERT(VARCHAR(8), DATEADD(ss, Time_In_Seconds, 0), 108) AS SpendTime,
    --case when t.[Timein] is null and t.[Timeout] is null and Excess = 'Short' then 'Absent' else '' end Remarks,

    CAST (ABS( 540 - DATEDIFF (MINUTE, t.[Timein], t.[Timeout] ) ) / 60 as varchar ) + ' hrs : ' +-- Hours ( + ':' separator )
    CAST (ABS( 540 - DATEDIFF (MINUTE, t.[Timein], t.[Timeout] ) ) % 60 as varchar ) + ' min' as excesshort,-- Minutes
    CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= 540 and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short' END Excess

    FROM attendi t
    left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
    --left join froaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) r ON t.eid = r.eid
    left join employee e on e.eid = t.eid
    left join designation d on e.designationid = d.designationid
    LEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
    LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
    LEFT OUTER JOIN Department T5 ON T5.did = e.did
    where t.eid=17090
    order by t.[date]
    end

    there is four column in table (attendi) eid,date, time in and time out
    i select date,eid, time in and time out and calulate its spend time , excessshort from timein and time out

    now i want a update query that when i execute procedure to calulate its spendtime , excess short automatically spendtime and excesshsort update in that table (attendi)

    i make two more column in attendi spendtime and excess short so when procedure calculate spendtime and excesshsort its automaticlay update that row in that table.

    i hope u under stand
  6. davidfarr Member

    I am not sure that I fully understand you...
    Are you asking for syntax that will add a [Minutes] column to your table and then update the column with minutes values ?
    If so, then try this;

    ALTER TABLE [Shift] add [Minute] int
    UPDATE [Shift] set [Minute] = DATEDIFF(minute,timein,timeout)

    I would, however, advise against using reserved words as column names. It would be better to create and name the column as [int_Minute] or a name that has no ambiguity within SQL Server.
  7. immad Member

    sir i solve this problem thanks for the suggestions

Share This Page