# Find Missing Sequence numbers for a variety of different keys

221 Reputation points
2022-05-24T00:03:47.347+00:00

I've found numerous options for finding missing sequence numbers where the sequence is straight-forward. In my case it's only part of a business key and I can't get my head around how I would use one of those methods in my case.

I have a table where generation in MW is recorded every half hour of the day for around 60 electricity generation stations. The key of each row is Station Node Code, Trading Date and Period ID. Each day has 46, 48 or 50 periods. 46 when we change to Daylight Savings and skip an hour and 50 when we revert to Standard Time and repeat an hour.

So, for each trading date and Station, I'd like to know if there are any trading periods missing.

Small sample:

As you can see for one of these stations, we're not missing any measurement periods on that trading day but we are for the others. I'd like to produce a list that would show just those stations, trading dates and trading periods where there is no row like this:

ARA2201 ARA0 2022-04-03 7
ARA2201 ARA0 2022-04-03 8
ARI1101 ARI0 2022-04-03 7
ARI1101 ARI0 2022-04-03 8
etc.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,613 questions

1. 5,891 Reputation points
2022-05-25T03:18:54.5+00:00

The recursive solution may not provide your optimal performance.
You can try to use a Number table without temp table in one run. I don't have the setup to test the query but you may update this thread to let us know.

``````  --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mycte1 as (
)
,mycte2 as (
)
from mycte2 C
ON C.STATION_NODE_CODE=T.STATION_NODE_CODE
``````

1. 10,046 Reputation points
2022-05-24T01:52:19.687+00:00

Hi @Nick Ryan
Try this:

``````;WITH CTE1 AS
(
FROM Table_Name
),CTE2 AS
(
FROM CTE1
UNION ALL
FROM CTE2 C2 JOIN CTE1 C1 ON C1.STATION_NODE_COED=C2.STATION_NODE_COED
)
SELECT C.*
``````

Best regards,
LiHong