SQL Server Performance

Please Help with the Query.

Discussion in 'SQL Server 2008 General Developer Questions' started by nguyenl, Oct 6, 2011.

  1. nguyenl New Member

    Code:
    Hi,
    I need to update the second record take it from the first record.
    Below is the business rule and desire output.  SQL 2005
    
    Thank you so much in advance.
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsurInfo]') AND type in (N'U'))
    DROP TABLE [dbo].[InsurInfo]
    GO
    
    CREATE TABLE [dbo].[InsurInfo](
        [NewValidFrom] [datetime] NULL,
        [IDPatInsur] [int] NULL,
        [PID] [int] NULL,
        [ValidFrom] [datetime] NULL,
        [ValidTo] [datetime] NULL,
        [GenInsurType] [varchar](9) NOT NULL,
        [Inactive] [bit] NULL,
        [rn] [bigint] NULL
    );
    go
    
    INSERT INTO [dbo].[InsurInfo]([NewValidFrom], [IDPatInsur], [PID], [ValidFrom], [ValidTo], [GenInsurType], [Inactive], [rn])
    SELECT '20100331 00:00:00.000', 1000, 100, '20100401 00:00:00.000', NULL, N'Primary', 0, 1 UNION ALL
    SELECT '20100215 00:00:00.000', 1003, 100, '20100216 00:00:00.000', NULL, N'Primary', 1, 2 UNION ALL
    SELECT '20100930 00:00:00.000', 3106, 106, '20101001 00:00:00.000', NULL, N'Primary', 0, 1 UNION ALL
    SELECT '20080731 00:00:00.000', 1011, 106, '20080801 00:00:00.000', NULL, N'Primary', 1, 2 UNION ALL
    SELECT '20101231 00:00:00.000', 3329, 144, '20110101 00:00:00.000', NULL, N'Primary', 0, 1 UNION ALL
    SELECT '20090930 00:00:00.000', 2100, 144, '20091001 00:00:00.000', NULL, N'Primary', 1, 2
    GO
    
        SELECT *
          FROM InsurInfo
        GO
    
    Business rules:  Update the NewValidFrom field from the first record where Inactive = 0 update
                    to the second one and it must be within the same PID.
    
    -- Result want:
    NewValidFrom            IDPatInsur  PID        ValidFrom              ValidTo                GenInsurType Inactive rn
    ----------------------- ----------- ----------- ----------------------- ----------------------- ------------ -------- --
    2010-03-31 00:00:00.000 1000        100        2010-04-01 00:00:00.000 NULL                    Primary      0        1
    2010-03-31              1003        100        2010-02-16 00:00:00.000 NULL                    Primary      1        2
    
    2010-09-30 00:00:00.000 3106        106        2010-10-01 00:00:00.000 NULL                    Primary      0        1
    2010-09-30              1011        106        2008-08-01 00:00:00.000 NULL                    Primary      1        2
    
    2010-12-31 00:00:00.000 3329        144        2011-01-01 00:00:00.000 NULL                    Primary      0        1
    2010-12-31              2100        144        2009-10-01 00:00:00.000 NULL                    Primary      1        2 
  2. Shehap MVP, MCTS, MCITP SQL Server

    First welcome to Forums;

    Regarding that one , you could use the below query :

    update
    InsurInfo set NewValidFrom = NewValidFrom_First from (select NewValidFrom as NewValidFrom_First , PID from InsurInfo where Inactive = 0) S where
    InsurInfo
    .PID=S.
    PID

Share This Page