MS SQL - Get Before / After Specific Rows-CodePudding

I have this table in SQL. Columns F and G are blank in SQL but I have shown how I would like to fill them out in this table.

When the column "Valid" is True, I want to capture the first row before it starts for all the rows until the last e.g. it goes false. Then populate the Units value in the Pre Units column. Then when it has the last row I want it to populate the "Post Units" column. Any idea on how to do this with MS SQL please?

Table Sample

 CREATE TABLE [dbo].[SODATA](
    [PKID] [bigint] NOT NULL,
    [Date] [date] NULL,
    [ProductCode] [nvarchar](50) NULL,
    [Units] [float] NULL,
    [Valid] [bit] NULL,
    [PreUnits] [decimal](18, 5) NULL,
    [PostUnits] [decimal](18, 5) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (400, N'2019-04-01', N'Product1', 306, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (480, N'2019-04-08', N'Product1', 471, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (561, N'2019-04-15', N'Product1', 248, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (642, N'2019-04-22', N'Product1', 87, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (725, N'2019-04-29', N'Product1', 304, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (804, N'2019-05-06', N'Product1', 234, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (892, N'2019-05-13', N'Product1', 131, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (980, N'2019-05-20', N'Product1', 137, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1070, N'2019-05-27', N'Product1', 491, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1158, N'2019-06-03', N'Product1', 474, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1245, N'2019-06-10', N'Product1', 424, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1333, N'2019-06-17', N'Product1', 312, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1420, N'2019-06-24', N'Product1', 483, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1510, N'2019-07-01', N'Product1', 378, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1598, N'2019-07-08', N'Product1', 301, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1684, N'2019-07-15', N'Product1', 67, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1784, N'2019-07-22', N'Product1', 153, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1878, N'2019-07-29', N'Product1', 232, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (1974, N'2019-08-05', N'Product1', 145, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2069, N'2019-08-12', N'Product1', 84, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2163, N'2019-08-19', N'Product1', 231, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2257, N'2019-08-26', N'Product1', 454, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2351, N'2019-09-02', N'Product1', 297, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2445, N'2019-09-09', N'Product1', 274, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2539, N'2019-09-16', N'Product1', 331, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2633, N'2019-09-23', N'Product1', 348, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2727, N'2019-09-30', N'Product1', 220, NULL, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2821, N'2019-10-07', N'Product1', 300, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (2915, N'2019-10-14', N'Product1', 132, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (3009, N'2019-10-21', N'Product1', 68, 1, NULL, NULL)
GO
INSERT [dbo].[SODATA] ([PKID], [Date], [ProductCode], [Units], [Valid], [PreUnits], [PostUnits]) VALUES (3102, N'2019-10-28', N'Product1', 450, NULL, NULL, NULL)
GO

CodePudding user response：

You can do this with CTE (Common Table Expressions), LAG and LEAD.

The code, after the inserts on the question, is:

;WITH mark_changes as (
    select
    PKID,Date,ProductCode,Units,Valid,
    case when ISNULL(Valid,0)<>ISNULL(LAG(Valid,1,2) OVER(order by PKID),0) then 1 else 0 end as Changed
    from SODATA
)
SELECT
PKID,Date,ProductCode,Units,Valid,
case when Valid=1 and Changed=1 then LAG(Units,1,0) OVER(order by PKID) else NULL end as PreUnits,
case when Valid=1 and ISNULL(LEAD(Valid,1) OVER(order by PKID),0)=0 then Units else NULL end as PostUnits
from mark_changes
order by PKID

And you can see this example on DB Fiddle here.

CodePudding user response：

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3e35b5962e43c1466ee85b6cc08316f9 lptr posted as a comment and works wonderfully thanks.

update x
set preunits = newpreunits,
postunits = newpostunits
from
(
select *, 
  max(prvunits) over(partition by productcode, grpid) as newpreunits,
  max(nxtunits) over(partition by productcode, grpid) as newpostunits
from
(
select *, sum(addme) over(partition by productcode order by date) as grpid
from
(
select *, 
  case when valid=1 and lead(valid) over(partition by productcode order by date) is null
  then lead(units) over(partition by productcode order by date) 
  end as nxtunits,
  
  case when valid=1 and lag(valid) over(partition by productcode order by date) is null
  then lag(units) over(partition by productcode order by date) 
  end as prvunits,
  
  case when 
    (valid=1 and lag(valid) over(partition by productcode order by date) is null)
    or
    (valid is null and lag(valid) over(partition by productcode order by date) = 1)
  then 1 else 0 end as addme
from sodata
) as agg
) as g
) as x
where newpreunits is not null
or newpostunits is not null