i am searching for a solution in regards to joining and MSSQL.
I have two tables.
The first one the Basic Table:
ID, Name, Key
1, Test1, 1x11
2, Test2, 2x22
3, Test3, 3x33
The second is the table which I want to join to the Basic table:
Key, Action, create, close, duration
1x11, 1, 01/01/2021 06:00,01/01/2021 07:00, 1
1x11, 5, 01/01/2021 07:00,01/01/2021 10:00, 1
1x11, 10, 01/01/2021 10:00,0, 0
2x22, 1, 01/01/2021 10:00,01/01/2021 11:00, 1
2x22, 5, 01/01/2021 11:00,01/01/2021 12:00, 1
2x22, 7, 01/01/2021 12:00,01/01/2021 13:00, 1
2x22, 5, 01/01/2021 13:00,01/01/2021 14:00, 1
2x22, 10, 01/01/2021 14:00,0, 0
3x33, 1, 01/01/2021 10:00,01/01/2021 12:00, 2
3x33, 10, 01/01/2021 12:00,0, 0
In this table the closedate was not given, so i had to use the following command to get the closedate (closedate is the next createdate):
lead (create,1) OVER (PARTITION BY Key ORDER BY create) AS close
Now, my goal is to join the sum(of ActionNumber 5 per Key) to the basic table
Can someone tell me how to do that? I am really frustrated.
Final Table:
ID, Name, Key, join(sum of 5)
1, Test1, 1x11,1
2, Test2, 2x22,2 (because there are two times one hour that means 2h)
3, Test3, 3x33,0
Thanks for helping. Christian