CODEWITHSUNDEEP
sqlhana
Jamey Copeland
Jamey Copeland

Reputation: 135

How to Join Specific Max Row in SAP HANA

Hello I am performing a query in SAP HANA and trying to only take a unique record with the most recent item (in this case it is a contract start date):

The query I am running is this:

SELECT 
 A1."ObjectShipToPartyName",
 A1."ObjectShipToParty",
 A1."ObjShippedDate",
 A1."ObjInstalledDate",
 A1."ProductModelNumber",
 A1."RefProd",
 A1."RefProdDescription",
 A1."AssetType",
 A1."IbaseComponentType",
 A1."WarrantyStartDate",
 A1."WarrantyEndDate",
 A1."WarrantyStartDateStatus",
 A1."WarrantyEndDateStatus",
 A1."ValidWarrStartDate",
 A1."ValidWarrEndDate",
 A1."IbaseObjectID",
 A1."AssociatedIBaseID",
 A2."ContractID",
 Max(A2."HDRStartDate") AS "HDRStartDate",
 Max(A2."HDREndDate") AS "HDREndDate",
 A2."ContractType"

FROM "Table1" A1 

LEFT OUTER JOIN "Table2" A2
    ON LTRIM(A2."IBASEID", 0) = LTRIM(A1."AssociatedIBaseID", 0) 

WHERE A1."AssetType" = '01'
    AND Cast(A1."ObjShippedDate" AS Date) > TO_DATE('20070801', 'YYYYMMDD') 
    AND A2."LineStatusText" IN ('Active','Completed')
    AND A2."HDRStatusTxt" IN  ('Active','Completed')
    AND A2."ContractType" <> 'ZQAS'

 GROUP BY
 A1."ObjectShipToPartyName",
 A1."ObjectShipToParty",
 A1."ObjShippedDate",
 A1."ObjInstalledDate",
 A1."ProductModelNumber",
 A1."RefProd",
 A1."RefProdDescription",
 A1."AssetType",
 A1."IbaseComponentType",
 A1."WarrantyStartDate",
 A1."WarrantyEndDate",
 A1."WarrantyStartDateStatus",
 A1."WarrantyEndDateStatus",
 A1."ValidWarrStartDate",
 A1."ValidWarrEndDate",
 A1."IbaseObjectID",
 A1."AssociatedIBaseID",
 A2."ContractID",
 A2."ContractType"

Which gives Data that looks like this: enter image description here

Here I only am interested in keeping the contract that is the highlighted row. Any help is greatly appreciated, I am fairly new to SQL.

Upvotes: 0

Views: 3417

Answers (1)

Lars Br.
Lars Br.

Reputation: 10388

This is a common requirement. Based on your clarification a solution could look like this:

SELECT 
 A1."ObjectShipToPartyName",
 A1."ObjectShipToParty",
 A1."ObjShippedDate",
 A1."ObjInstalledDate",
 A1."ProductModelNumber",
 A1."RefProd",
 A1."RefProdDescription",
 A1."AssetType",
 A1."IbaseComponentType",
 A1."WarrantyStartDate",
 A1."WarrantyEndDate",
 A1."WarrantyStartDateStatus",
 A1."WarrantyEndDateStatus",
 A1."ValidWarrStartDate",
 A1."ValidWarrEndDate",
 A1."IbaseObjectID",
 A1."AssociatedIBaseID",
 A2."ContractID",
 A2."HDRStartDate"
 A2."HDREndDate"
 A2."ContractType"

FROM 
    "Table1" A1 

    LEFT OUTER JOIN "Table2" A2
    ON LTRIM(A2."IBASEID", 0) = LTRIM(A1."AssociatedIBaseID", 0) 

    inner join
         (SELECT "IBASEID", MAX("HDREndDate") as "MAX_HDREndDate"
          FROM "Table2" 
          WHERE 
                "LineStatusText" IN ('Active','Completed')
            AND "HDRStatusTxt" IN  ('Active','Completed')
            AND "ContractType" !='ZQAS'
          GROUP BY "IBASEID") S2

    on (A2."IBASEID", A2."HDREndDate") = (S2."IBASEID", S2."MAX_HDREndDate")

WHERE 
    A1."AssetType" = '01'
    AND TO_DATE(A1."ObjShippedDate") > TO_DATE('20070801', 'YYYYMMDD');

The aggregation needs to be on a separate level than the actual data collection. Therefore you first find all the most current/max dates per IBASEID then retrieve the details you need for those records.
The resulting set is what you join to your Table1.

Side remark: I would stick to one kind of type conversion (either TO_DATE or CAST) for readability. Also joining on the transformed IBASEID is not enhancing performance. A better option would be to store the data in the right format.

Upvotes: 1

Related Questions