SQLTeam.com | Weblogs | Forums

Fill row null value from other row based on item_id and Item_reference_id and startdate

sql2008r2

#1

Hi experts !
I need support to have sqlquery to fill the null value of reference_id row based on Item_id and start date of other row.
both rows contains reference_id and Item_id

The first row Item_type ( TANK) is the main row and second row Company is the reference row.
I need that the Column_1 value of Row1 data 201 also replicate to reference row Column_1 Null value to 201 .

Below the sample table

  CREATE TABLE [dbo].[TEST1](
	[ITEM_TYPE] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[REFERNCE_ID] [char](32) NULL,
	[PROD_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[COLUMN_1] [numeric](28, 12) NULL,	
)
GO

INSERT [dbo].[TEST1] ([ITEM_TYPE], [ITEM_ID], [REFERNCE_ID],[PROD_NAME],[START_DATETIME], [END_DATETIME], [COLUMN_1]) VALUES('TANK','5a00ff8641ac41cfa6d9b8a0c0d91ee3','ae7e9801735b4b70aef56147957cb9aa','Storage1','2015-07-01 00:00','2015-07-01 00:00',201.00000	)
INSERT [dbo].[TEST1] ([ITEM_TYPE], [ITEM_ID], [REFERNCE_ID],[PROD_NAME],[START_DATETIME], [END_DATETIME], [COLUMN_1]) VALUES('COMPANY','ae7e9801735b4b70aef56147957cb9aa','5a00ff8641ac41cfa6d9b8a0c0d91ee3','POL1','2015-07-01 00:00','2015-07-01 00:00',NULL	)

SELECT * FROM TEST1