Check it: dbfiddle here
Basically, get all rows where P1 is null, and join its with all rows that have a P1 value and where name like c1.
Using LIKE
UPDATE t1
SET P1 = t2.P1
FROM tbl1 t1
INNER JOIN tbl1 t2
ON t2.P1 IS NOT NULL
AND t2.c1 LIKE '%' + t1.Name + '%'
WHERE t1.P1 IS NULL;
| id|Name |C1 | P1|
|----:|:----------|:-------------------------------------------|----:|
|29214|g6-1sr |g6-1, g6-1sr, g6-1se,g6-1se12,g6-1se1 |28000|
|29215|g6-1se |g6-1, g6-2, g6-1000, g6-1980, g6-1230,|28000|
|29226|g6-1sf |g6-1, g6-2, g6-1000, g6-1980, g6-1230,| |
|29237|g6-1se1 |g6-1, g6-2, g6-1000, g6-1980, g6-1230,|28000|
|29248|g6-1se12 |g6-1, g6-2, g6-1000, g6-1980, g6-1230,|28000|
|29259|Nkg6-1se |g6-1, g6-2, g6-1000, g6-1980, g6-1230,| |
|29269|N56-1341se |N56-11, N56-1341se, N56-1100, N56-1348se, |32000|
|29270|N56-1348se |N56-11, N56-21, N56-1100, N56-2980, |32000|
|29271|F566 1341se|g6-1, g6-2, g6-1000, g6-1980, g6-1230,|38000|
Using dbo.SplitCompare() function
UPDATE t1
SET P1 = t2.P1
FROM tbl1 t1
INNER JOIN tbl1 t2
ON t2.P1 IS NOT NULL
AND dbo.SplitCompare(t2.C1, t1.Name) = 1
WHERE t1.P1 IS NULL;
Update
Check it: dbfiddle here
First, I've added a new function: [dbo].[SplitCompare]
-------------------------------------------------------------------------------------
-- Compares each item of a coma-delimited string (without trainling spaces),
-- against @ToSearch.
--
-- Returns: (int)
--
-- 1 - @ToSearch matches some item
-- 0 - No matches found.
-------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[SplitCompare] (@List nvarchar(MAX), @ToSearch nvarchar(1024))
RETURNS int
AS
BEGIN
DECLARE @Item nvarchar(1024) = null;
DECLARE @Result int = 0;
WHILE LEN(@List) > 0
BEGIN
IF PATINDEX('%,%', @List) > 0
BEGIN
SET @Item = SUBSTRING(@List, 0, PATINDEX('%,%', @List));
SET @List = SUBSTRING(@List, LEN(@Item + ';') + 1, LEN(@List));
END
ELSE
BEGIN
SET @Item = @List;
SET @List = NULL;
END
IF RTRIM(LTRIM(@Item)) = @ToSearch -- does it match?
BEGIN
SET @Result = 1;
SET @List = '';
END
END
RETURN @Result;
END
Second, according to OP requirement, I've implemented this solution using a CURSOR.
-------------------------------------------------------------------------------------
-- Use @LimitRows just to limit the number of rows to be updated each time
-- the script is executed.
-------------------------------------------------------------------------------------
DECLARE @LimitRows int = 100;
DECLARE @Id int, @Name nvarchar(2048), @P1 int;
-- only selects rows where P1 IS NULL
DECLARE CurPrices CURSOR
FOR SELECT TOP (@LimitRows) Id, Name FROM #tbl1 WHERE P1 IS NULL;
OPEN CurPrices
FETCH NEXT FROM CurPrices INTO @Id, @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @P1 = NULL;
-- note: top (1)
--
SELECT TOP (1) @P1 = t.P1
FROM tbl1 t
WHERE t.P1 IS NOT NULL
AND dbo.SplitCompare(t.C1, @Name) = 1
ORDER BY Id;
IF COALESCE(@P1,0) > 0
BEGIN
UPDATE #tbl1
SET P1 = @P1
WHERE Id = @Id;
IF @@ERROR <> 0
BEGIN
CLOSE CurPrices;
DEALLOCATE CurPrices;
RAISERROR('ERROR', 20, -1);
END
END
FETCH NEXT FROM CurPrices INTO @Id, @Name
END
CLOSE CurPrices;
DEALLOCATE CurPrices;
The result is the same as the first command, but this one allow to limit the number of affected rows every time it is executed.
This is a 'Gaps and Islands' problem. I took this example and tweaked it for your situation.
DECLARE @TestData TABLE (
ID NUMERIC(18) NOT NULL
,[DATE] DATE NOT NULL
,[TYPE] VARCHAR(20) NOT NULL
,[VALUE] VARCHAR(20)
)
INSERT INTO @TestData VALUES
(17482, '2016-08-24', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-25', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-26', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-04', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-05', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-16', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-17', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-23', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-24', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-25', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-27', 'VOLUME_EXCEPTION', 'Low'),
(17482, '2016-09-28', 'VOLUME_EXCEPTION', 'Low')
;
WITH mycte
AS (
SELECT *
,DATEADD(DAY, - ROW_NUMBER() OVER (
PARTITION BY [Value] ORDER BY [Date]
), [Date]) AS grp
FROM @TestData
)
--select * from mycte --Uncomment to see the data from mycte
SELECT min([Date]) AS [From]
,max([Date]) AS [To]
,[Type]
,[value]
FROM mycte
GROUP BY [Type]
,[value]
,grp
ORDER BY [From];
| From | To | Type | value |
|------------|------------|--------------------|-------|
| 2016-08-24 | 2016-08-26 | PRESSURE_EXCEPTION | Over |
| 2016-09-04 | 2016-09-05 | PRESSURE_EXCEPTION | Over |
| 2016-09-16 | 2016-09-17 | PRESSURE_EXCEPTION | Under |
| 2016-09-23 | 2016-09-25 | PRESSURE_EXCEPTION | Under |
| 2016-09-27 | 2016-09-28 | VOLUME_EXCEPTION | Low |
The OVER/PARTITION logic is basically assigning each row to a 'group' so you can pick out the MIN
and MAX
from each group. If you un-comment the
select * from mycte
(I added an order by date) and run the script from that part to the top, you'll see that each row gets assigned to a group.
Look at the first group of rows assigned to '2016-08-23'. The ROW_NUMBER
for date '2016-08-24' is 1, so the DATEADD
subtracts 1 from '2016-08-24' to put that row in group '2016-08-23'. The ROW_NUMBER
for date '2016-08-25' is 2, so the DATEADD
subtracts 2 from '2016-08-25' to put it in the same group as row number 1 and so on.
| ID | DATE | TYPE | VALUE | grp |
|-------|------------|--------------------|-------|------------|
| 17482 | 2016-08-24 | PRESSURE_EXCEPTION | Over | 2016-08-23 |
| 17482 | 2016-08-25 | PRESSURE_EXCEPTION | Over | 2016-08-23 |
| 17482 | 2016-08-26 | PRESSURE_EXCEPTION | Over | 2016-08-23 |
| 17482 | 2016-09-04 | PRESSURE_EXCEPTION | Over | 2016-08-31 |
| 17482 | 2016-09-05 | PRESSURE_EXCEPTION | Over | 2016-08-31 |
| 17482 | 2016-09-16 | PRESSURE_EXCEPTION | Under | 2016-09-15 |
| 17482 | 2016-09-17 | PRESSURE_EXCEPTION | Under | 2016-09-15 |
| 17482 | 2016-09-23 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-24 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-25 | PRESSURE_EXCEPTION | Under | 2016-09-20 |
| 17482 | 2016-09-27 | VOLUME_EXCEPTION | Low | 2016-09-26 |
| 17482 | 2016-09-28 | VOLUME_EXCEPTION | Low | 2016-09-26 |
Now, it's just a matter of pulling the MIN
and MAX
from each group.
Best Answer
The magic of NULLIF seems to do the trick for the test case in your question. Since you used a different example than in your SQL Fiddle, I don't know if that's what you want there too.
Returns:
If you need a more general solution that handles negative numbers and other edge cases, see for example The Product Aggregate in T-SQL Versus the CLR by Scott Burkow. One T-SQL construction from that article is:
As to why your original
CASE
expression did not work as expected, from the documentation for CASE (Transact-SQL) (emphasis added):