SQLTeam.com | Weblogs | Forums

Need a SQL Query

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

please explain the logic required

You will need LAG to do this:

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

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

well done thanks very much

Said

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