Sub-Query Sorting

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Teradata Employee

Re: Sub-Query Sorting

The nPath function doesn't need any extension (probably a custom install in 15.10), and it's included in every 16.xx+ database (not 100% sure about the xx begin 10 or 20).

Ambassador

Re: Sub-Query Sorting

If the result returned by nPath is what you expected then it's just the data from the row following 'Reopen' and can be written using a simple LEAD, no RESET WHEN or nPath needed:

SELECT t.*
  ,Lead(Status) Over (PARTITION BY Benefit_Number ORDER BY UpdateDate ASC) AS next_status
  ,Lead(UpdateDate) Over (PARTITION BY Benefit_Number ORDER BY UpdateDate ASC) AS next_open
FROM mvt_datas AS t
WHERE Status IN ('Reopen', 'Closed')
QUALIFY Status = 'Reopen'
  AND next_status = 'Closed'
ORDER BY Benefit_Number ASC, UpdateDate ASC;

 

Enthusiast

Re: Sub-Query Sorting

Taking it as step further, I'd like to be able to count the number of payments between the reopen date and close date. Right now I have it so it will count every payment after the reopen date but since the closed_date is a LEAD, it will not let me put it in the sub-query's where statement. Is there a way I can use both dates in line with each other to get that data? I messed around with joining the two tables but that made things pretty messy.


SELECT B.CLM_CASENUMBER, B.BNFT_CASENUMBER, B.BNFT_CASE_ACTION_STATUS, B.BNFT_CASE_ACTION_LASTUPDTE_TSP, Lead(B.BNFT_CASE_ACTION_STATUS) Over (PARTITION BY B.BNFT_CASENUMBER ORDER BY B.BNFT_CASE_ACTION_LASTUPDTE_TSP ASC) AS closed_status, Lead(B.BNFT_CASE_ACTION_LASTUPDTE_TSP) Over (PARTITION BY B.BNFT_CASENUMBER ORDER BY B.BNFT_CASE_ACTION_LASTUPDTE_TSP ASC) AS closed_date, (Select count(*) from P_FIR_BI_VW.V_PAY_HISTORY_SCHEDULED P where B.BNFT_CASENUMBER = P.BNFT_CASENUMBER AND P.PAYMENT_DRAFT_ISSUE_DTE > B.BNFT_CASE_ACTION_LASTUPDTE_TSP) AS issued_payments FROM P_FIR_BI_VW.V_BNFT_CASE_ACTIONS B WHERE B.BNFT_CASE_ACTION_STATUS IN ('Reopen', 'Closed') QUALIFY B.BNFT_CASE_ACTION_STATUS = 'Reopen' AND closed_status = 'Closed' ORDER BY B.BNFT_CASENUMBER ASC, B.BNFT_CASE_ACTION_LASTUPDTE_TSP ASC
Teradata Employee

Re: Sub-Query Sorting

Make the query you have (except the COUNT and final ORDER BY) a subquery, then you can JOIN or use the correlated subquery and sort the final result in an outer query.

Enthusiast

Re: Sub-Query Sorting

Can you provide a little more detail? I'm having a hard time following.

Enthusiast

Re: Sub-Query Sorting

I also looked into using a Derived Table but I get an object does not exist when I try to use values from the first table in the creation of the derived table.

Ambassador

Re: Sub-Query Sorting

Can you provide more example data (similar to Waldar's) and expected result?

Enthusiast

Re: Sub-Query Sorting

Using the last example data set that I sent to Waldar and the new data set I posted below. The tables share the key of Benefit Number. What I would like to see is the number of payments (from the new data set) issued during the reopen and close noted on each row. Below is also how I would like to see it. Let me know if that doesn't make sense.

 

Benfit NumberPayment Date
9097000030104/25/2009
9097000030104/26/2009
9097000030104/27/2009
9097000030104/28/2009
9097000050106/03/2009
9097000050106/03/2009

 

Claim NumberBenefit NumberReopen StatusReopen DateClose StatusClose Date# of Payments Issued
090970000300090970000301Reopen04/29/2009 4:05:59 PMClosed04/30/2009 3:19:26 PM2
090970000300090970000301Reopen05/01/2009 9:43:16 AMClosed05/02/2009 8:28:09 AM0
090970000500090970000501Reopen06/03/2009 7:45:35 AMClosed06/04/2009 11:01:58 AM0
090990000100090990000101Reopen04/21/2009 2:24:56 PMClosed04/21/2009 2:25:25 PM0
Highlighted
Teradata Employee

Re: Sub-Query Sorting

I've modified the dates of payments to timestamp for it to match the expected output with the initial table provided earlier in the thread.

Payment data

create multiset volatile table mvt_payment, no log
( Benefit_Number   varchar(12)
, PaymentDate      timestamp(0)
)
primary index (Benefit_Number)
on commit preserve rows;

insert into mvt_payment values ('090970000301', timestamp '2009-04-25 18:00:00');
insert into mvt_payment values ('090970000301', timestamp '2009-04-26 13:00:00');
insert into mvt_payment values ('090970000301', timestamp '2009-04-27 08:00:00');
insert into mvt_payment values ('090970000301', timestamp '2009-04-28 15:00:00');
insert into mvt_payment values ('090970000501', timestamp '2009-06-03 00:00:00');
insert into mvt_payment values ('090970000501', timestamp '2009-06-03 00:00:00');

collect statistics column (Benefit_Number) on mvt_payment;

With nPath it's still kinda easy to find your answer :

  select np.*
    from nPath(          on (select Claim_Number, Benefit_Number, Status, UpdateDate
                               from mvt_datas
                              union all
                             select '', Benefit_Number, 'Pay', PaymentDate
                               from mvt_payment)
               partition by Benefit_Number
                   order by UpdateDate asc
                      using
                       mode ( nonoverlapping )
                    symbols ( Status = 'Reopen' as R
                            , Status = 'Pay'    as P
                            , Status = 'Closed' as C
                            )
                    pattern ( 'R.P*.C' )
                     result ( first(Claim_Number   of R) as Claim_Number
                            , first(Benefit_Number of R) as Benefit_Number
                            , first(Status         of R) as Status_Reopen
                            , first(UpdateDate     of R) as UpdateDate_Reopen
                            , first(Status         of C) as Status_Closed
                            , first(UpdateDate     of C) as UpdateDate_Closed
                            , count(*              of P) as NbPaymentIssued
                            )
              ) as np
order by Benefit_Number, UpdateDate_Reopen;

claim_number  benefit_number  status_reopen  updatedate_reopen    status_closed  updatedate_closed    nbpaymentissued
------------  --------------  -------------  -------------------  -------------  -------------------  ---------------
090970000300  090970000301    Reopen         2009-04-25 16:05:59  Closed         2009-04-26 15:19:26                2
090970000300  090970000301    Reopen         2009-04-27 09:43:16  Closed         2009-04-28 08:28:09                0
090970000500  090970000501    Reopen         2009-06-03 07:45:35  Closed         2009-06-04 11:01:58                0
Teradata Employee

Re: Sub-Query Sorting

This is what I was describing:

SELECT 
    X.CLM_CASENUMBER, 
    X.BNFT_CASENUMBER,
    X.BNFT_CASE_ACTION_STATUS,
    X.BNFT_CASE_ACTION_LASTUPDTE_TSP,
    X.closed_status,
    X.closed_date,
    (Select count(*) from P_FIR_BI_VW.V_PAY_HISTORY_SCHEDULED P where P.BNFT_CASENUMBER = X.BNFT_CASENUMBER 
	AND P.PAYMENT_DRAFT_ISSUE_DTE > X.BNFT_CASE_ACTION_LASTUPDTE_TSP and P.PAYMENT_DRAFT_ISSUE_DTE <= X.closed_date) AS issued_payments
	FROM (
SELECT 
    B.CLM_CASENUMBER, 
    B.BNFT_CASENUMBER,
    B.BNFT_CASE_ACTION_STATUS,
    B.BNFT_CASE_ACTION_LASTUPDTE_TSP,
    Lead(B.BNFT_CASE_ACTION_STATUS) Over (PARTITION BY B.BNFT_CASENUMBER ORDER BY B.BNFT_CASE_ACTION_LASTUPDTE_TSP ASC) AS closed_status,
    Lead(B.BNFT_CASE_ACTION_LASTUPDTE_TSP) Over (PARTITION BY B.BNFT_CASENUMBER ORDER BY B.BNFT_CASE_ACTION_LASTUPDTE_TSP ASC) AS closed_date
FROM 
    P_FIR_BI_VW.V_BNFT_CASE_ACTIONS B 
WHERE 
    B.BNFT_CASE_ACTION_STATUS IN ('Reopen', 'Closed') QUALIFY B.BNFT_CASE_ACTION_STATUS = 'Reopen' AND 
    closed_status = 'Closed'
	) as X
ORDER BY 
    X.BNFT_CASENUMBER ASC, X.BNFT_CASE_ACTION_LASTUPDTE_TSP ASC