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'
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;