Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
184 views
in Technique[技术] by (71.8m points)

sql server - MSSQL Join Calculation

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If the two tables exist then this should be a simple aggregation.

SELECT
    B.ID,
    B.Name,
    B.Key,
    CountAction5 = SUM(CASE WHEN S.Action = 5 THEN Duration ELSE 0 END)
FROM
    BasicTable B
    INNER JOIN SecondTable S ON S.Key = B.Key
GROUP BY
    B.ID,
    B.Name,
    B.Key

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...