I have a table of article's logs. I need to get all the articles which have only one log, or in case there are amount of logs more than 1: if an article has any log in status = 103, it's need to fetch only rows after this log, in other case all the logs. So from the following dataset I want to get only rows with Id 1383 and 284653.
Id | Article | Version | StatusId | AddedDate |
---|---|---|---|---|
1383 | 1481703 | 0 | 42 | 2011-11-25 09:23:42.000 |
284645 | 435545 | 1 | 41 | 2021-11-02 18:29:42.000 |
284650 | 435545 | 2 | 41 | 2021-11-02 18:34:58.000 |
284651 | 435545 | 2 | 103 | 2021-11-02 18:34:58.000 |
284653 | 435545 | 3 | 41 | 2021-11-02 18:38:33.000 |
Any ideas how to handle it properly ? Thanks in advance
CodePudding user response:
You can use window functions here. A combination of a running COUNT
and a windowed COUNT
will do the trick
The benefit of using window functions rather than self-joins is that you only scan the base table once.
SELECT
Id,
Article,
Version,
StatusId,
AddedDate
FROM (
SELECT *,
HasPrev103 = COUNT(CASE WHEN StatusId = 103 THEN 1 END) OVER
(PARTITION BY Article ORDER BY AddedDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
Has103 = COUNT(CASE WHEN StatusId = 103 THEN 1 END) OVER (PARTITION BY Article),
Count = COUNT(*) OVER (PARTITION BY Article)
FROM YourTable t
) t
WHERE (Has103 > 0 AND HasPrev103 > 0) OR Count = 1;
CodePudding user response:
CREATE TABLE #Article (
Id int NOT NULL PRIMARY KEY,
Article int NOT NULL,
Version int NOT NULL,
StatusId int NOT NULL,
DateAdded datetime NOT NULL
)
INSERT INTO #Article (Id, Article, Version, StatusId, DateAdded)
VALUES
(1383, 1481703, 0, 42, '2011-11-25 09:23:42.000'),
(284645, 435545, 1, 41 , '2021-11-02 18:29:42.000'),
(284650, 435545, 2, 41 , '2021-11-02 18:34:58.000'),
(284651, 435545, 2, 103, '2021-11-02 18:34:58.000'),
(284653, 435545, 3, 41 , '2021-11-02 18:38:33.000')
SELECT *
FROM #Article a
LEFT JOIN (
-- Get articles that appear only once.
SELECT Article
FROM #Article
GROUP BY Article
HAVING COUNT(*) = 1
) AS o
ON a.Article = o.Article
LEFT JOIN (
-- Get the 103s and their corresponding date.
SELECT Article, DateAdded
FROM #Article
WHERE StatusId = 103
) AS s
ON a.Article = s.Article AND s.DateAdded < a.DateAdded
WHERE o.Article IS NOT NULL OR (s.Article IS NOT NULL AND a.DateAdded > s.DateAdded)
DROP TABLE #Article