Only lists records when StockValue changes

Question

Post reply

Only lists records when StockValue changes

waldemar

SSC Veteran

Points: 270
More actions
March 19, 2021 at 11:32 am

#3859874
Good day,
After running below create and insert scripts, my query should only return the rows in blue color
(when StockValue changes from IN to SOLD , SOLD to IN, IN to SOLD)
I've listed 2 NR's to help with understanding what I'm looking for.
Can anyone help me with this?
Thank you
- This topic was modified 3 years, 8 months ago by waldemar.
- This topic was modified 3 years, 8 months ago by waldemar.
- This topic was modified 3 years, 8 months ago by waldemar.
- This topic was modified 3 years, 8 months ago by waldemar.
- This topic was modified 3 years, 8 months ago by waldemar.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

waldemar SSC Veteran Points: 270 More actions · Answer 1

Below is the create and insert scripts

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST_SAMPLE]') AND type in (N'U'))

DROP TABLE [dbo].[TEST_SAMPLE]

GO

CREATE TABLE [dbo].[TEST_SAMPLE](

[NR] [varchar](9) NOT NULL,

[ControlNo] [float] NULL,

[LocationNo] [varchar](50) NULL,

[StockValue] [nvarchar](255) NULL,

[DeliveryDate] [nvarchar](255) NULL,

[Date] [datetime] NULL,

[RowNo] [bigint] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 41, N'AZT7000 H', N'IN', N'NULL', CAST(N'2019-07-31T00:00:00.000' AS DateTime), 1)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'IN', N'NULL', CAST(N'2019-10-04T00:00:00.000' AS DateTime), 2)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'SOLD', N'10/15/2019 00:00:00', CAST(N'2019-10-15T00:00:00.000' AS DateTime), 3)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'SOLD', N'10/15/2019 00:00:00', CAST(N'2019-10-16T00:00:00.000' AS DateTime), 4)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'SOLD', N'10/15/2019 00:00:00', CAST(N'2020-01-03T00:00:00.000' AS DateTime), 5)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'SOLD', N'10/15/2019 00:00:00', CAST(N'2020-04-29T00:00:00.000' AS DateTime), 6)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'IN', N'NULL', CAST(N'2020-05-08T00:00:00.000' AS DateTime), 7)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'SOLD', N'10/15/2019 00:00:00', CAST(N'2020-05-12T00:00:00.000' AS DateTime), 8)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5971ACT', 42, N'AZT7000 K', N'SOLD', N'10/15/2019 00:00:00', CAST(N'2020-09-10T00:00:00.000' AS DateTime), 9)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5968GHT', 41, N'AZT7000', N'IN', N'NULL', CAST(N'2020-10-09T00:00:00.000' AS DateTime), 1)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5968GHT', 41, N'AZT7000', N'IN', N'NULL', CAST(N'2020-10-17T00:00:00.000' AS DateTime), 2)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5968GHT', 41, N'AZT7000', N'SOLD', N'10/28/2020 00:00:00', CAST(N'2020-10-28T00:00:00.000' AS DateTime), 3)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5968GHT', 42, N'LPO6185', N'SOLD', N'10/28/2020 00:00:00', CAST(N'2020-10-29T00:00:00.000' AS DateTime), 4)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5968GHT', 42, N'LPO6185', N'IN', N'NULL', CAST(N'2020-11-11T00:00:00.000' AS DateTime), 5)

GO

INSERT [dbo].[TEST_SAMPLE] ([NR], [ControlNo], [LocationNo], [StockValue], [DeliveryDate], [Date], [RowNo]) VALUES (N'ZN5968GHT', 42, N'LPO6185', N'SOLD', N'10/28/2020 00:00:00', CAST(N'2021-01-04T00:00:00.000' AS DateTime), 6)

GO

This reply was modified 3 years, 8 months ago by waldemar.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

It doesn't quite appear you've asked a question, and this isn't well formatted to understand what you want. Please format code as code, and explain what results or logic you are looking for and what you have tried.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

My approach, looking at it, you are wanting all rows where there is an IN followed by a SOLD.

For a start with your WHERE clause, filter out all rows where the stock is SOLD AND the delivery date <> Date. This gets you all of the SOLD rows in blue. Now you just need the IN rows. A CTE can help with that an the LAG function (or LEAD). So take your filtered data set (from the WHERE above), but that into a CTE, add a calculated column to the end of the select called something like 'NextSold' and use LEAD to look at the next row to see if it is SOLD or not.

Then you can SELECT from the CTE where "NextSold" = 1 OR StockValue = 'Sold'.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

waldemar SSC Veteran Points: 270 More actions · Answer 4

filter out all rows where the stock is SOLD AND the delivery date <> Date

If I do above, then rownumber 3 & 12 will be excluded. Don't want to exclude them.

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

Like what Mr Brian Gale described you could try a combination of LEAD and LAG functions

with lead_lag_cte as (
    select *,
           lead(StockValue) over (partition by NR order by [Date]) lead_sv,
           lag(StockValue) over (partition by NR order by [Date]) lag_sv
    from [dbo].[TEST_SAMPLE])
select *
from lead_lag_cte
where (StockValue='SOLD' and lag_sv='IN') or
      (StockValue='IN' and lead_sv='SOLD')
order by RowNo;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

waldemar SSC Veteran Points: 270 More actions · Answer 6

Thanks Ten Centuries, exactly what I was looking for.