Search code examples
sqlteradatateradata-sql-assistantscenariosteradatasql

Complex Scenario Teradata Query From Single Table

QUESTION :

Fetch the records that are having type='Onsite Repair' as well as the records that have Type in ('Calibration', 'Interface Troubleshooting', 'Setup/Configuration','Customer Applications') only when there is also a row with Type='Travel' within the same Act_ID on the same date. Else ignore. (Record with onsite repair type should always appear)

Type = "Onsite Repair" OR Type = ("Calibration", "Interface Troubleshooting", "Setup/Configuration","Customer Applications") only when there is also a row with Type="Travel" within the same Act_ID on the same date. Else ignore the Types.

  • SOURCE DATA
ACT_ID TYPE START_TS END_TS
ACTID1 Travel 7/20/2016 13:00 7/20/2016 15:30
ACTID1 Interface Troubleshooting 7/20/2016 15:30 7/20/2016 19:00
ACTID1 Travel 7/20/2016 19:00 7/20/2016 21:00
ACTID1 Travel 9/20/2016 13:00 9/20/2016 15:30
ACTID1 Onsite Repair 9/20/2016 15:30 9/20/2016 23:30
ACTID1 Travel 9/21/2016 13:00 9/21/2016 15:30
ACTID1 Onsite Repair 9/21/2016 15:30 9/21/2016 23:30
ACTID1 Travel 9/22/2016 13:00 9/22/2016 15:30
ACTID1 Onsite Repair 9/22/2016 15:30 9/22/2016 23:30
  • EXPECTED DATA
ACT_ID TYPE START_TS END_TS
ACTID1 Interface Troubleshooting 7/20/2016 15:30 7/20/2016 19:00
ACTID1 Onsite Repair 9/20/2016 15:30 9/20/2016 23:30
ACTID1 Onsite Repair 9/21/2016 15:30 9/21/2016 23:30
ACTID1 Onsite Repair 9/22/2016 15:30 9/22/2016 23:30
Solution

  • Simple task for a Windowed Aggregate plus qualify:

    select *
from table
qualify Type = 'Onsite Repair'
     OR (Type = in ('Calibration','Interface Troubleshooting', 'Setup/Configuration','Customer Applications')
        -- row with Type='Travel'
          AND count(case when Type='Travel' then 1 end)
                         -- same Act_ID on the same date
              over (partition by Act_ID, cast(START_TS as date)  > 0
        )