英文:
Transpose to-date and from-date to consecutive calendar month increments
问题
以下是您要翻译的内容：
"我有这样的表格
| 人员 | 状态 | 起始日期 | 结束日期 |
|---|---|---|---|
| 1 | A | 2023-01-01 | 2023-03-31 |
| 1 | B | 2023-04-01 | 3999-12-31 |
我还有另一个按月汇总的余额表格。
| 人员 | 值 | 期间 |
|---|---|---|
| 1 | 值 | 2023-01-01 |
| 1 | 值 | 2023-02-01 |
| 1 | 值 | 2023-03-01 |
| 1 | 值 | 2023-04-01 |
| 1 | 值 | 2023-05-01 |
有什么聪明的方法可以在月增量上连接状态值，使其显示如下
| 人员 | 状态 | 期间 |
|---|---|---|
| 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 |
期待您的想法！
我尝试了一些不同的连接方式，但无法做对。我相信有一种聪明的方法可以做到这一点。"
英文:
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.
答案1
得分: 1
你可以使用 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
结果：
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论