0

I have a query that joins different tables does some calculations and collects the data in the following format: (same data is provided in excel format in the link below)

enter image description here

sample data can be found here: https://docs.google.com/spreadsheets/d/1zZ3e8L0rYQYIiK9XKlEVVZVhM4atdzKt/edit?usp=drive_link&ouid=114233428136510498080&rtpof=true&sd=true

What I want is a query to strip out all the records for each ID after the 'DELIVERY' type record. i.e. for the first ID, it should keep first 6 records, for the second ID, it should keep first 5 records, and so on. If there are mutiple records with type= ‘Delivery’ per ID, then it should drop records after the first ‘Delivery’ type record. How can I achieve this?

How I want my data is:

enter image description here

My current query is:

SEL t1.ID, 
t3.TIME, 
t2.TYPE,
ROW_NUMBER() OVER (PARTITION BY t1.ID
                   ORDER BY t3.TIME) SEQ

FROM master t1

INNER JOIN scan t3
ON t1.ID = t3.ID

INNER JOIN lookup t2
ON t3.CODE = t2.CODE
User771
  • 53
  • 5
  • so basically, you want to remove records `WHERE TYPE NOT IN ('XXXX', 'YYYY')` ? – KeepCalmAndCarryOn Aug 08 '23 at 22:03
  • No that’ not it. Only records after the first ‘Delivery’ type. – User771 Aug 08 '23 at 22:13
  • But "remove after the first" isn't consistent with saying keep 9 rows for the second ID. And it would also be less confusing to format your timestamp data to show both the date and time. – Fred Aug 09 '23 at 00:33
  • @Fred Edited my post, sorry I made a mistake earlier. – User771 Aug 09 '23 at 03:05
  • 1
    This should work as expected, keeping rows up to the first DELIVERY: `qualify max(case when type = 'DELIVERY' then 1 end) OVER (PARTITION BY t1.ID ORDER BY t3.TIME rows between unbounded preceding and 1 preceding) is null` – dnoeth Aug 09 '23 at 05:55
  • @dnoeth Worked. Thank you so much. – User771 Aug 09 '23 at 15:07

0 Answers0