SQL Server Get records that have been added or closed between certain dates in a table

ylamdve6 于 2023-02-18 发布在其他

关注(0)|答案(1)|浏览(156)

My table:
| Column1 | Date |
| ------------ | ------------ |
| Value1 | 1-Feb |
| Value2 | 1-Feb |
| Value2 | 2-Feb |
| Value3 | 2-Feb |
| Value1 | 3-Feb |
| Value2 | 3-Feb |
| Value4 | 3-Feb |

Expected Output:

Column1	Date	Status
Value1	1-Feb	Added
Value2	1-Feb	Added
Value1	2-Feb	Closed
Value3	2-Feb	Added
Value1	3-Feb	Added
Value3	3-Feb	Closed
Value4	3-Feb	Added

How to get the records that have been added or closed between certain dates in a table in SQL

I'm executing an except command between each date and vice versa then inserting data into a table. Is there any way I can achieve the desired output with a single query?

I'm currently using this solution.

SELECT column1, 'Added' AS Status FROM mytable WHERE date = '2023-02-03' EXCEPT SELECT column1 FROM mytable WHERE date = '2023-02-02'
UNION
SELECT column1, 'Closed' AS Status FROM mytable WHERE date = '2023-02-02' EXCEPT SELECT column1 FROM mytable WHERE date = '2023-02-03'

sql-server

来源：https://stackoverflow.com/questions/75370768/get-records-that-have-been-added-or-closed-between-certain-dates-in-a-table

1条答案

按热度按时间

6ie5vjzr1#

You can do it by generating the upcoming days for every column with no upcoming day, then with left join you can get the Status.

with cte as (
  select column1, DATEADD(day, 1, min(Date)) AS Date
  from mytable
  where  Date < (select max(Date) from mytable)
  group by column1
  having count(1) = 1
  union 
  select column1, Date
  from mytable
),
cte2 as (
  select c.*, iif(t.Date is not null, 'Added', 'Closed') as status
  from cte c 
  left join mytable t on t.column1 = c.column1 and t.Date = c.Date
)
select column1, min(Date) as Date, status 
from cte2
group by column1, status
order by min(Date), column1;

Demo here

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server Get records that have been added or closed between certain dates in a table

1条答案

相关问题

热门标签

最新问答