'How to i assign a new column by matching all the steps in hana sql?
I have event, cases and road tables.
I want to join these 3 tables but i have a problem because there is two different road_id which matched same travelpoint, nextpoint, market and factory columns.
I want the exactly matched steps to define road_id but the data is always duplicate with two different Road_ID.
Because of there is some same casestep in different road_ID. So when i look at where ROAD_ID = ROADID001 and CaseStep = 1 i can see values from another casestep = 1 from ROADID002
i tried the following example but it couldnt work. How can i add this condition without duplicate?
I've been working with this for a week but it was very difficult to understand and solve for me. I hope someone can help about this hard situation.
Thanks a lot.
select Road_ID,
CaseStep,
Factory,
Market,
TravelPoint,
EventStep,
NextPoint,
RoadtoEventStart,
RoadtoEventEnd,
AVG(Duration),
Count(DISTINCT CaseId)
from(
SELECT
"R".ROAD_ID,
"E"."RoadtoEventStart",
"E"."RoadtoEventEnd",
"E".CaseStep,
"E."FACTORY,
"E".MARKET,
"E".TravelPoint,
"E".NextPoint,
"E".EventStep
FROM "EVENTS" as "E"
inner join "ROADS" "R" ON
"E"."RoadtoEventStart"="R."RoadtoEventStart"
and "E"."RoadtoEventEnd"="R."RoadtoEventEnd"
and "E"."CaseStep" = "R"."CaseStep"
and "E"."TravelPoint"="R."TRavelPoint"
and "E"."NextPoint"="R."NexPoint"
and "E"."Factory" = "R"."Factory"
and"E"."Market" = "R."Market" )
GROUP by
Road_ID, RoadStep, Factory,
Market, TravelPoint,
CaseStep, RoadtoEventStart, RoadtoEventEnd
Road Table
| Road_ID | RoadStep | Factory | Market | TravelPoint | NextPoint | RoadtoEventStart | RoadtoEventEnd |
|---|---|---|---|---|---|---|---|
| ROADID001 | 1 | Finland | Sweden | A Point | B Point | 07801001 | X220101 |
| ROADID001 | 2 | Finland | Sweden | B Point | C Point | 07801001 | X220101 |
| ROADID001 | 3 | Finland | Sweden | C Point | END | 07801001 | X220101 |
| ROADID004 | 1 | Finland | Sweden | A Point | B Point | 07801001 | X110101 |
| ROADID004 | 2 | Finland | Sweden | B Point | C Point | 07801001 | X110101 |
| ROADID004 | 3 | Finland | Sweden | C Point | END | 07801001 | X110101 |
| ROADID002 | 1 | Finland | Sweden | A Point | B Point | 07801001 | X110101 |
| ROADID002 | 2 | Finland | Sweden | B Point | D Point | 07801001 | X110101 |
| ROADID002 | 3 | Finland | Sweden | D Point | END | 07801001 | X110101 |
| ROADID003 | 1 | Finland | Sweden | A Point | D Point | 07801001 | X110101 |
| ROADID003 | 2 | Finland | Sweden | D Point | END | 07801001 | X110101 |
Events TABLE Example 1
| CaseID | Factory | CaseStep | Market | TravelPoint | NextPoint | Duration | RoadtoEventStart | RoadtoEventEnd |
|---|---|---|---|---|---|---|---|---|
| 10001 | Finland | 1 | Sweden | A Point | B Point | 18 | 07801001 | X110101 |
| 10001 | Finland | 2 | Sweden | B Point | C Point | 3 | 07801001 | X110101 |
| 10001 | Finland | 3 | Sweden | C Point | END | 5 | 07801001 | X110101 |
Events TABLE Example 2
| CaseId | Factory | CaseStep | Market | TravelPoint | NextPoint | Duration | RoadtoEventStart | RoadtoEventEnd |
|---|---|---|---|---|---|---|---|---|
| 10002 | Finland | 1 | Sweden | A Point | B Point | 4 | 07801001 | X220101 |
| 10002 | Finland | 2 | Sweden | B Point | D Point | 1 | 07801001 | X220101 |
| 10002 | Finland | 3 | Sweden | D Point | END | 2 | 07801001 | X220101 |
Events TABLE Example 3
| CaseId | Factory | CaseStep | Market | TravelPoint | NextPoint | Duration | RoadtoEventStart | RoadtoEventEnd |
|---|---|---|---|---|---|---|---|---|
| 10003 | Finland | 1 | Sweden | A Point | D Point | 10 | 07801001 | X220101 |
| 10003 | Finland | 2 | Sweden | D Point | END | 29 | 07801001 | X220101 |
Events TABLE Example 4
| CaseId | Factory | CaseStep | Market | TravelPoint | NextPoint | Duration | RoadtoEventStart | RoadtoEventEnd |
|---|---|---|---|---|---|---|---|---|
| 10004 | Finland | 1 | Sweden | A Point | B Point | 2 | 07801001 | X220101 |
| 10004 | Finland | 2 | Sweden | B Point | D Point | 3 | 07801001 | X220101 |
| 10004 | Finland | 3 | Sweden | D Point | END | 1 | 07801001 | X220101 |
Target Output Table
| Road_ID | CaseStep | Factory | Market | TravelPoint | NextPoint | AVG(Duration) | Count (Distinct CaseID) | RoadtoEventStart | RoadtoEventEnd |
|---|---|---|---|---|---|---|---|---|---|
| ROADID001 | 1 | Finland | Sweden | A Point | B Point | 18 | 1 | 07801001 | X220101 |
| ROADID001 | 2 | Finland | Sweden | B Point | C Point | 3 | 1 | 07801001 | X220101 |
| ROADID001 | 3 | Finland | Sweden | C Point | END | 5 | 1 | 07801001 | X220101 |
| ROADID002 | 1 | Finland | Sweden | A Point | B Point | 3 | 2 | 07801001 | X220101 |
| ROADID002 | 2 | Finland | Sweden | B Point | D Point | 2 | 2 | 07801001 | X220101 |
| ROADID002 | 3 | Finland | Sweden | D Point | END | 1.5 | 2 | 07801001 | X220101 |
| ROADID003 | 1 | Finland | Sweden | A Point | D Point | 10 | 1 | 07801001 | X220101 |
| ROADID003 | 2 | Finland | Sweden | D Point | END | 29 | 1 | 07801001 | X220101 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|