Populating Row from a Previous Row for Group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Populating Row from a Previous Row for Group

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2014-01-22 : 10:09:36

I'm trying to copy the value of a column from an immediately previous row if the value of the column on the existing row is NULL. This is only done within each group of rows, which is defined as the first 8 characters of ta-number (the last 3 digits are the sequence). Further, it should only copy from a lesser sequence to a higher sequence (down), not from a higher sequence to a less one (up). I have supplied two examples.

Example 1, the data in the table TAUDIT_TEST:


-ta-number------ta-location1
----------------------------
00002001001-----2000
00002001002-----2380
00002002001-----2000
00002002002-----2450
00002005001-----NULL
00002005002-----NULL
00002005003-----NULL
00002006001-----2319
00002006002-----NULL
00002006003-----NULL
00002007001-----2000
00002007002-----NULL
00002007003-----2111
00002007004-----NULL

The query should change the above data to:

-ta-number------ta-location1
----------------------------
00002001001-----2000
00002001002-----2380
00002002001-----2000
00002002002-----2450
00002005001-----NULL
00002005002-----NULL
00002005003-----NULL
00002006001-----2319
00002006002-----2319
00002006003-----2319
00002007001-----2000
00002007002-----2000
00002007003-----2111
00002007004-----2111

Example 2:

-ta-number------ta-location1
----------------------------
00002001001 2000
00002001002 2380
00002002001 NULL
00002002002 2450
00002005001 NULL
00002005002 NULL
00002005003 NULL
00002006001 2319
00002006002 NULL
00002006003 NULL
00002007001 NULL
00002007002 NULL
00002007003 2111
00002007004 NULL

Should look like:

-ta-number------ta-location1
----------------------------
00002001001 2000
00002001002 2380
00002002001 NULL
00002002002 2450
00002005001 NULL
00002005002 NULL
00002005003 NULL
00002006001 2319
00002006002 2319
00002006003 2319
00002007001 NULL
00002007002 NULL
00002007003 2111
00002007004 2111

My query partly works but it does not always use values from the immediately previous rows, and does not always respect the "border" of the group, which in this case is defined as the first 8 positions of ta-number.

Here is the query I have come up with so far. It would perhaps benefit by an Order By clause, but the great inscrutable SQL god will not tolerate it:


UPDATE TAUDIT_TEST
SET
    [ta-location1] = newdata.[ta-location1]
FROM
    (
    SELECT
        [ta-number],
        [ta-location1]
    FROM TAUDIT_TEST
    WHERE
        [ta-location1] IS NOT NULL
    ) newdata
WHERE
    TAUDIT_TEST.[ta-location1] IS NULL
    AND SUBSTRING(TAUDIT_TEST.[ta-number],1,8) = SUBSTRING(newdata.[ta-number],1,8)

Here is a script to create the data for example 1:


USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TAUDIT_TEST](
	[ta-number] [varchar](12) NULL,
	[ta-location1] [varchar](8) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002001001', N'2000')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002001002', N'2380')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002002001', N'2000')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002002002', N'2450')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002005001', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002005002', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002005003', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002006001', N'2319')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002006002', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002006003', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007001', N'2000')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007002', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007003', N'2111')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007004', NULL)

Here is a script to create data for example 2:

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TAUDIT_TEST](
[ta-number] [varchar](12) NULL,
[ta-location1] [varchar](8) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002001001', N'2000')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002001002', N'2380')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002002001', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002002002', N'2450')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002005001', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002005002', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002005003', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002006001', N'2319')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002006002', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002006003', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007001', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007002', NULL)
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007003', N'2111')
INSERT [dbo].[TAUDIT_TEST] ([ta-number], [ta-location1]) VALUES (N'00002007004', NULL)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-22 : 17:34:28

Here's one way:


;with split (Grp, Seq, loc1)
as   (
       select left([ta-number], 8)
              ,stuff([ta-number], 1,8,'')
              ,[ta-location1]
       from   taudit_test
       ) 
select s.Grp + s.Seq as [ta-Number]
       ,coalesce(s.Loc1, ca.Loc1) as [ta-Location1]
from split s
outer apply (
       select top 1 Grp, Loc1
       from   split 
       where  Grp =  s.Grp
       and    Loc1 is not null
       and    Seq < s.Seq
       order by Seq desc
       ) ca

Be One with the Optimizer
TG

BobRoberts
Posting Yak Master

109 Posts

Posted - 2014-01-23 : 11:03:54

TG, your solution work perfectly! Thank you for helping me with this. However, I am not using a SELECT statement, but an UPDATE statement, and it isn't clear how to incorporate your ;with split/SELECT statement into my UPDATE statement so that the data will actually be changed to reflect your SELECT statment. I've tried a few things, but they all yield syntax errors.

quote:

Originally posted by TG

Here's one way:


;with split (Grp, Seq, loc1)
as   (
       select left([ta-number], 8)
              ,stuff([ta-number], 1,8,'')
              ,[ta-location1]
       from   taudit_test
       ) 
select s.Grp + s.Seq as [ta-Number]
       ,coalesce(s.Loc1, ca.Loc1) as [ta-Location1]
from split s
outer apply (
       select top 1 Grp, Loc1
       from   split 
       where  Grp =  s.Grp
       and    Loc1 is not null
       and    Seq < s.Seq
       order by Seq desc
       ) ca

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-23 : 11:45:31

You're welcome. Here is a way to turn the original statement into an update:


;with split (Grp, Seq, loc1)
as   (
       select left([ta-number], 8)
              ,stuff([ta-number], 1,8,'')
              ,[ta-location1]
       from   taudit_test
       ) 
--select s.Grp + s.Seq as [ta-Number] ,coalesce(s.Loc1, ca.Loc1) as [ta-Location1]
update tt set tt.[ta-Location1] = ca.Loc1
from split s
outer apply (
       select top 1 Grp, Loc1
       from   split 
       where  Grp =  s.Grp
       and    Loc1 is not null
       and    Seq < s.Seq
       order by Seq desc
       ) ca
inner join taudit_test tt
       on tt.[ta-number] = s.Grp + s.Seq
where s.Loc1 is null

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources