Fixing Missing Data Based on Prior Row Information

Fixing Missing Data Based on Prior Row Information

One of the commonly asked problems in the Transact-SQL forum is how to provide missing information based on the information in the first prior row that has data (or alternatively in the next row (by date)). One of the examples where this problem was discussed is this thread.

In this thread the original poster was kind enough to provide DDL and the DML (data sample), so it was easy to define a solution based on the OUTER APPLY

CREATE TABLE [dbo].[test_assign] (
    [name] [varchar](25) NULL
    ,[datestart] [date] NULL
    ,[dateEnd] [date] NULL
    ,[assign_id] [int] IDENTITY(1, 1) NOT NULL
    ,CONSTRAINT [PK_test_assign] PRIMARY KEY CLUSTERED ([assign_id] ASC) WITH (
        PAD_INDEX = OFF
        ,STATISTICS_NORECOMPUTE = OFF
        ,IGNORE_DUP_KEY = OFF
        ,ALLOW_ROW_LOCKS = ON
        ,ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
    ) ON [PRIMARY]
 
CREATE TABLE [dbo].[test_measure] (
    [name] [varchar](25) NULL
    ,[measurementDate] [date] NULL
    ,[measure_id] [int] IDENTITY(1, 1) NOT NULL
    ,CONSTRAINT [PK_test_measure] PRIMARY KEY CLUSTERED ([measure_id] ASC) WITH (
        PAD_INDEX = OFF
        ,STATISTICS_NORECOMPUTE = OFF
        ,IGNORE_DUP_KEY = OFF
        ,ALLOW_ROW_LOCKS = ON
        ,ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
    ) ON [PRIMARY]
 
INSERT INTO Test_Measure (
    NAME
    ,Measurementdate
    )
SELECT 'Adam'
    ,'1/1/2001'
 
INSERT INTO Test_Measure (
    NAME
    ,Measurementdate
    )
SELECT 'Adam'
    ,'2/2/2002'
 
INSERT INTO Test_assign (
    NAME
    ,DateStart
    ,DateEnd
    )
SELECT 'Adam'
    ,'1/15/2001'
    ,'12/31/2001'
 
INSERT INTO Test_assign (
    NAME
    ,DateStart
    ,DateEnd
    )
SELECT 'Adam'
    ,'2/15/2002'
    ,'12/31/2002'
 
INSERT INTO Test_assign (
    NAME
    ,DateStart
    ,DateEnd
    )
SELECT 'Adam'
    ,'3/15/2003'
    ,'12/31/2003'
 
 
-- Solution starts now
SELECT TA.*
    ,M.MeasurementDate
FROM Test_Assign TA
OUTER APPLY (
    SELECT TOP (1) *
    FROM Test_Measure TM
    WHERE TM.NAME = TA.NAME
        AND TM.MeasurementDate <= TA.Datestart
    ORDER BY TM.MeasurementDate DESC
    ) M

The idea of this solution is to use correlated OUTER APPLY subquery to get first measurement date that is prior the Start date of the main table.

A similar problem is also described in this thread and solution will also be a variation of CROSS APPLY solution. So, you can see that this problem is very frequent.


See Also




This entry participated in the TechNet Guru contributions for June contest.
Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 6. Comment: Added See Also

  • Naomi  N edited Revision 5. Comment: Changed to past tense

  • Naomi  N edited Revision 4. Comment: Minor edit

  • Naomi  N edited Revision 1. Comment: Added last line about contest

  • Naomi  N edited Original. Comment: More content

Page 1 of 1 (5 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Original. Comment: More content

  • Naomi  N edited Revision 1. Comment: Added last line about contest

  • Naomi  N edited Revision 4. Comment: Minor edit

  • Naomi  N edited Revision 5. Comment: Changed to past tense

  • Naomi  N edited Revision 6. Comment: Added See Also

Page 1 of 1 (5 items)