SQL Server Transpose to-date and from-date to consecutive calendar month increments

zdwk9cvp 于 2023-05-16 发布在其他

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

I have a table like this
| Person | Status | FromDate | ToDate |
| ------------ | ------------ | ------------ | ------------ |
| 1 | A | 2023-01-01 | 2023-03-31 |
| 1 | B | 2023-04-01 | 3999-12-31 |

I also have another balance table with aggregates on a monthly basis.

Person	Value	Period
1	value	2023-01-01
1	value	2023-02-01
1	value	2023-03-01
1	value	2023-04-01
1	value	2023-05-01

What would be a smart way to join in the status values on a monthly increment so it appears as follows

Person	Status	Period
1	A	2023-01-01
1	A	2023-02-01
1	A	2023-03-01
1	B	2023-04-01
1	B	2023-05-01

Keen on thoughts!

I have tried some different joins but cant get it right. I am sure there is a smart way of doing this.

sql-server

来源：https://stackoverflow.com/questions/76220011/transpose-to-date-and-from-date-to-consecutive-calendar-month-increments

1条答案

按热度按时间

r1wp621o1#

You can do it using inner join :

select t.Person, t.Status, b.Period
from mytable t
inner join balance b on b.Period between t.FromDate and t.ToDate

Result :

Person  Status  Period
1       A       2023-01-01
1       A       2023-02-01
1       A       2023-03-01
1       B       2023-04-01
1       B       2023-05-01

Demo here

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server Transpose to-date and from-date to consecutive calendar month increments

1条答案

相关问题

热门标签

最新问答