Results 1 to 8 of 8

Thread: deleting

  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: deleting

    create table emp
    (
    empno int,
    intime datetime null,
    outtime datetime null
    )

    insert into emp values('100','01-01-2010 9:34:12 AM',NULL)
    insert into emp values('101','01-01-2010 8:40:12 AM',NULL)
    insert into emp values('102','01-01-2010 9:10:12 AM',NULL)
    insert into emp values('100',NULL,'01-01-2010 10:45:12 AM')
    insert into emp values('101',NULL,'01-01-2010 09:55:48 AM')
    insert into emp values('102',NULL,'01-01-2010 10:15:16 AM')
    insert into emp values('100','01-01-2010 11:00:02 AM',NULL)
    insert into emp values('101','01-01-2010 10:05:42 AM',NULL)
    insert into emp values('102','01-01-2010 10:35:02 AM',NULL)
    insert into emp values('100',NULL,'01-01-2010 01:35:51 PM')
    insert into emp values('101',NULL,'01-01-2010 12:35:48 PM')
    insert into emp values('102',NULL,'01-01-2010 12:05:36 PM')

    select * from emp
    drop table emp

    i want only first intime and last outtime it is nothing just min(intime) and max(outtime) the remaining data must be deleted i was trying this code to execute but fails

    delete T where ('empno'and'intime'and'outtime') not in
    (
    select empno,min(intime),max(outtime) from emp group by empno
    )T

    can hope some one will help thanks for considering
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The contents of the table, when complete, will be this, correct?
    Code:
    SELECT  empno
          , MIN(intime)
          , MAX(outtime) 
    FROM    emp 
    GROUP BY 
            empno
    This is very important because I think you have not clearly understood what you are trying to do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i can get it individually when i try for deleting first min values then after max values
    but i was trying to do it at once in single command i tried like this for single attempt

    Code:
    delete t where empno,intime,outime not in 
    (select min(intime)fromemp group by empno)
    like this
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That wasn't my question. Please can you answer my question?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    exactly i want the output after deleting all the remaining code is
    Code:
    select empno,min(intime),max(outtime) from emp group by empno
    result i expected after deleting the data is
    Code:
    empno                firstintime                                lastouttime
    100    	2010-01-01 09:34:12.000      	2010-01-01 13:35:51.000
    101    	2010-01-01 08:40:12.000	        2010-01-01 12:35:48.000
    102    	2010-01-01 09:10:12.000      	2010-01-01 12:05:36.000
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That means that you are not selectively deleting the data - you are replacing it with entirely new data.

    Be very careful with this - you are deleting your data forever - make sure you have archived it.
    Code:
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    BEGIN TRY
            
        BEGIN TRANSACTION 
    
            SELECT  empno
                  , intime      = MIN(intime)
                  , outtime     = MAX(outtime) 
            INTO    dbo.emp_new
            FROM    dbo.emp 
            GROUP BY 
                    empno
    
            TRUNCATE TABLE dbo.emp
    
            INSERT INTO dbo.emp
            SELECT  *
            FROM    dbo.emp_new
            WITH    (TABLOCK)
            
            DROP TABLE dbo.emp_new
    
        COMMIT TRANSACTION 
    
    END TRY
    BEGIN CATCH
        
        DECLARE    @l_error_message    AS NVARCHAR(2048)
              , @l_error_number     AS INT
              , @l_error_state      AS INT
              , @l_error_severity   AS INT
              , @l_error_line       AS INT
        
        SELECT  @l_error_message    = ERROR_MESSAGE() 
              , @l_error_number     = ERROR_NUMBER()
              , @l_error_state      = ERROR_STATE()
              , @l_error_severity   = ERROR_SEVERITY()
              , @l_error_line       = ERROR_LINE()
        
        IF @@TRANCOUNT >= 1
        BEGIN
            ROLLBACK TRANSACTION
        END
        
        RAISERROR
            (
                'Error #%d occured at line %d: %s'
              , @l_error_severity
              , @l_error_state
              , @l_error_number
              , @l_error_message
              , @l_error_line
            ) WITH NOWAIT
        
    END CATCH
    
    SELECT  * 
    FROM    dbo.emp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    thanks pootle the caption of yours is correct i remember i was the reason for the caption any way your codes were always very complicated very perfect. I had to understand the code. i tried even putting case condition to get the o/p but failed. any way
    thanks again and again you were for me
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I might add that to my sig

    I am pleased you are pleased.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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