Need a SQL Query

Transact-SQL
1

I have a table like below

ID Current

1 2.73
2 2.71
3 2.22
4 2.66
5 3.02
6 1.99
7 7.29
8 2.12
9 5.11
10 2.06
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 3.02
20 1.99
21 7.29
22 2.12
23 5.11
24 0
25 0
26 0
27 0

I want a Query which returns:

ID Current

1 2.73
10 2.06
11 0
18 0
19 3.02
23 5.11
24 0
27 0

Thank you in advance
Said

2

please explain the logic required

3

You will need LAG to do this:

LAG (Transact-SQL) - SQL Server | Microsoft Docs

4

In future please post consumable test data:

CREATE TABLE #t
(
	ID int NOT NULL
		PRIMARY KEY
	,[Current] decimal(5,2) NOT NULL
);
INSERT INTO #t
VALUES (1, 2.73),(2, 2.71),(3, 2.22),(4, 2.66),(5, 3.02)
	,(6, 1.99),(7, 7.29),(8, 2.12),(9, 5.11),(10, 2.06)
	,(11, 0),(12, 0),(13, 0),(14, 0),(15, 0)
	,(16, 0),(17, 0),(18, 0),(19, 3.02),(20, 1.99)
	,(21, 7.29),(22, 2.12),(23, 5.11),(24, 0),(25, 0)
	,(26, 0),(27, 0);

One Approach:

WITH PrevVals
AS
(
	SELECT ID, [Current]
		,CASE WHEN [Current] = 0 THEN 1 ELSE 0 END AS IsZero
		,LAG(CASE WHEN [Current] = 0 THEN 1 ELSE 0 END) OVER (ORDER BY ID) AS PrevIsZero
	FROM #t
)
,SeqBreaks
AS
(
	SELECT ID, [Current]
		,CASE
			WHEN PrevIsZero IS NULL
				OR IsZero <> PrevIsZero
			THEN 1
			ELSE 0
		END AS SeqBreak
	FROM PrevVals
)
,Grps
AS
(
	SELECT RIGHT('00000' + CAST(ID AS varchar(6)), 6)
			+ CAST([Current] AS varchar(10)) AS IDCurrent
		,SUM(SeqBreak) OVER (ORDER BY ID) AS Grp
	FROM SeqBreaks
)
,GrpRange
AS
(
	SELECT MIN(IDCurrent) AS MinIDCurrent
		,MAX(IDCurrent) AS MaxIDCurrent
	FROM Grps
	GROUP BY Grp
)
SELECT X.ID, X.[Current]
FROM GrpRange R
	CROSS APPLY
	(
		VALUES (CAST(LEFT(R.MinIDCurrent, 6) AS int), CAST(SUBSTRING(R.MinIDCurrent, 7, 20) AS decimal(5,2)))
			,(CAST(LEFT(R.MaxIDCurrent, 6) AS int), CAST(SUBSTRING(R.MaxIDCurrent, 7, 20) AS decimal(5,2)))
	) X (ID, [Current])
ORDER BY ID;
2 Likes
5

well done thanks very much

Said

6

Here is a much more concise query to return what you want:

CREATE TABLE #t
(
	ID int NOT NULL
		PRIMARY KEY
	,[Current] decimal(5,2) NOT NULL
);
INSERT INTO #t
VALUES (1, 2.73),(2, 2.71),(3, 2.22),(4, 2.66),(5, 3.02)
	,(6, 1.99),(7, 7.29),(8, 2.12),(9, 5.11),(10, 2.06)
	,(11, 0),(12, 0),(13, 0),(14, 0),(15, 0)
	,(16, 0),(17, 0),(18, 0),(19, 3.02),(20, 1.99)
	,(21, 7.29),(22, 2.12),(23, 5.11),(24, 0),(25, 0)
	,(26, 0),(27, 0);

SELECT * 
  FROM #t
 WHERE ID IN(1,10,11,18,19,23,24,27);
1 Like