0

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
Pawan Vishwakarma
  • 15
  • 4
  • What have you tried so far? Do you know how to do just this bit: _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')_ – Nick.Mc Jul 06 '21 at 04:23
  • Hi @Nick.McDermaid, I wrote below query and its works but i'm looking for more simple query instead of performing self join. SELECT * FROM TABLE WHERE TYPE = 'Onsite Repair' UNION SELECT * FROM TABLE A INNER JOIN TABLE B ON A.ACT_ID = B.ACT_ID WHERE A.PROJ_NAM IN ('Calibration', 'Interface Troubleshooting', 'Setup/Configuration','Customer Applications') AND B.TYPE='Travel' AND CAST(A.START_TS AS DATE FORMAT 'YYYY-MM-DD') = CAST(B.START_TS AS DATE FORMAT 'YYYY-MM-DD'); – Pawan Vishwakarma Jul 06 '21 at 14:59

1 Answers1

1

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
        )
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks @dnoeth. This answer set of the query exactly matches with the query answer set that i have written and i believe is correct. Can you help me understand the processing of the qualify part. I know qualify works like filter condition but i'm more curious on how this part is processed (Type = in ('Calibration','Interface Troubleshooting', 'Setup/Configuration','Customer Applications') AND count(case when Type='Travel' then 1 end) over (partition by Act_ID, cast(START_TS as date)) > 0 – Pawan Vishwakarma Jul 06 '21 at 14:57
  • 1
    QUALIFY is processed *after* WHERE/GROUP BY/HAVING and the **Group Count** applies aggregate logic without loosing detail rows. – dnoeth Jul 06 '21 at 16:54