Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Post Unanswered: SQL - Aggregate and Scalar Info on Same Record

    Currently, I am using the following SQL statement to extract data from an

    Oracle 8.1.7 database using the Hyperian (Brio Explorer) business

    intelligence tool.

    SELECT DISTINCT AL2.ACCT, AL3.ENCOUNTER_NO,
    trunc(AL3.ADMIT_DATE), trunc(AL3.DISCHARGE_DATE),

    AL3.DATE_BILLED,
    AL3.TOTAL_CHARGES, AL3.EXPECTED_REIMBURSEMENT,

    AL2.TOTAL_PAYMENTS,
    AL3.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS,
    AL3.EXPECTED_REIMBURSEMENT - AL3.TOTAL_PAYMENTS,

    AL2.TOTAL_PAYMENTS /
    AL3.EXPECTED_REIMBURSEMENT, AL3.TOTAL_PAYMENTS -

    AL2.TOTAL_PAYMENTS,
    AL3.TOTAL_CHARGES - Sum (AL2.NONCOVERED_PT_CHARGES +
    AL2.NONCOVERED_WO_CHARGES), AL3.TOTAL_CHARGES - Sum
    (AL6.ADJUSTMENT_AMOUNT), trunc(AL1.DATE_UPDATED),

    trunc(SYSDATE) FROM
    ENCOUNTER_PAYMENT_DETAIL AL1, ENCOUNTER_PAYOR AL2,
    PATIENT_ENCOUNTER AL3, ENCOUNTER_TRANSACTION_DETAILS
    AL6 WHERE ( AL1.ENCOUNTER_NO = AL3.ENCOUNTER_NO (+) AND
    AL2.ENCOUNTER_NO=AL3.ENCOUNTER_NO AND
    AL6.ENCOUNTER_NO=AL3.ENCOUNTER_NO) AND (AL2.RANK=1

    AND
    AL2.ACCT Not In ('AB4','AB5','BD6','BD7')
    AND AL1.PAYMENT_AMOUNT>0 AND AL1.TRANSACTION_CODE in
    ('2252','2253') AND
    AL3.EXPECTED_REIMBURSEMENT(+)>0 AND

    AL3.EXPECTED_REIMBURSEMENT -
    AL3.TOTAL_PAYMENTS>0 AND AL2.TOTAL_PAYMENTS /
    AL3.EXPECTED_REIMBURSEMENT<=0.75 AND

    AL6.TRANSACTION_CODE in ('4500','4502','4615') AND
    trunc(AL1.DATE_UPDATED) = trunc(sysdate) - 21) GROUP BY
    AL2.ACCT, AL3.ENCOUNTER_NO, trunc(AL3.ADMIT_DATE),
    trunc(AL3.DISCHARGE_DATE), AL3.DATE_BILLED,

    AL3.TOTAL_CHARGES,
    AL3.EXPECTED_REIMBURSEMENT, AL2.TOTAL_PAYMENTS,
    AL3.EXPECTED_REIMBURSEMENT - AL2.TOTAL_PAYMENTS,
    AL3.EXPECTED_REIMBURSEMENT - AL3.TOTAL_PAYMENTS,

    AL2.TOTAL_PAYMENTS /
    AL3.EXPECTED_REIMBURSEMENT, AL3.TOTAL_PAYMENTS -

    AL2.TOTAL_PAYMENTS,
    trunc(AL1.DATE_UPDATED), trunc(SYSDATE) HAVING

    ((AL3.TOTAL_CHARGES -
    Sum (AL6.ADJUSTMENT_AMOUNT)) -

    AL3.EXPECTED_REIMBURSEMENT) <> 0 ORDER
    BY 1, 2, 9


    Now, I would like to modify the SQL script to include the following

    information ("Amt of first payor payment", "Date of first payor payment",

    "Amt of last payor payment" and "Date of last payor payment"). Note, a

    payor payment is denoted by Transaction codes "2252" and "2253." Patient

    payments are payments with a transaction code of "2251." Displayed below

    is the information within the Oracle database:

    Acct---EncNo---PymtAmt-----PaymentDate---TransCode
    AB1----136589--162.61------01/12/05------2252
    AB1----136589--250.00------01/12/05------2251
    AB1----136589--500.00------01/12/05------2253
    AB1----136589--350.00------02/25/05------2251
    AB1----136589--100.00------02/25/05------2253
    AB1----136590--260.00------12/10/04------2251
    AB1----136590--400.00------02/05/05------2252
    AB2----150850--100.00------02/01/05------2251
    AB2----150850--450.00------02/15/05------2252
    AB2----150850--650.00------03/01/05------2252

    Upon obtaining the information, I will load into an Access database and

    update the "Date of the last payor payment" and "Amt of last payor

    payment" on a daily basis using an update query via an ADO connection.

    How should the aforementioned SQL statement be modified so that I can

    have the following information on one record per EncNo;"date of first

    payment", "amt of first payment", "date of last payment", and "amt of last

    payment"?

    I have tried, unsuccessfully to derive the answer using subqueries and inner

    joins.


    Thank you in advance!

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, I assumed that when you said "first" and "last" for a given encno, is a combination of paymentdate + transcode ( transcode being unique here for each day ). If so, they are a couple of ways to get this information.

    With joins, like you were trying..
    Code:
    SQL@8i> select t.encno,
      2         t1.paymentdate first_date,
      3         t1.pymamt first_date_amt,
      4         t2.paymentdate last_date,
      5         t2.pymamt last_date_amt
      6    from t, t t1, t t2
      7   where t1.paymentdate = ( select min( paymentdate ) from t where encno = t1.encno )
      8     and t1.transcode = ( select min( transcode ) from t where encno = t1.encno and paymentdate = t1.paymentdate )
      9     and t2.paymentdate = ( select max( paymentdate ) from t where encno = t2.encno )
     10     and t2.transcode = ( select max( transcode ) from t where encno = t2.encno and paymentdate = t2.paymentdate )
     11     and t.encno = t1.encno
     12     and t1.encno = t2.encno
     13   group by t.encno, t1.paymentdate, t1.pymamt, t2.paymentdate, t2.pymamt
     14  /
    
        ENCNO FIRST_DAT FIRST_DATE_AMT LAST_DATE LAST_DATE_AMT
    --------- --------- -------------- --------- -------------
       136589 12-JAN-05            250 25-FEB-05           100
       136590 10-DEC-04            260 05-FEB-05           400
       150850 01-FEB-05            100 01-MAR-05           650
    
    SQL@8i>
    With a MAX and SUBSTR trick, like..
    Code:
    SQL@8i> select encno,
      2         to_date( substr( min( to_char( paymentdate, 'yyyymmddhh24miss' ) || transcode ), 1, 14 ), 'yyyymmddhh24miss' ) first_date,
      3         to_number( substr( min( to_char( paymentdate, 'yyyymmddhh24miss' ) || to_char( transcode, 'fm0000000' ) || pymamt ), 22 ) ) first_date_amt,
      4         to_date( substr( max( to_char( paymentdate, 'yyyymmddhh24miss' ) || transcode ), 1, 14 ), 'yyyymmddhh24miss' ) last_date,
      5         to_number( substr( max( to_char( paymentdate, 'yyyymmddhh24miss' ) || to_char( transcode, 'fm0000000' ) || pymamt ), 22 ) ) last_date_amt
      6    from t
      7   group by encno
      8  /
    
        ENCNO FIRST_DAT FIRST_DATE_AMT LAST_DATE LAST_DATE_AMT
    --------- --------- -------------- --------- -------------
       136589 12-JAN-05            250 25-FEB-05           100
       136590 10-DEC-04            260 05-FEB-05           400
       150850 01-FEB-05            100 01-MAR-05           650
    
    SQL@8i>
    And latest ( and my prefered ) with analytics..
    Code:
    SQL@8i> select encno,
      2         first_date,
      3         max( case when paymentdate = first_date and transcode = first_transcode then pymamt end ) first_date_amt,
      4         last_date,
      5         max( case when paymentdate = last_date and transcode = last_transcode then pymamt end ) last_date_amt
      6    from (
      7  select t.*,
      8         min( paymentdate ) over( partition by encno ) first_date,
      9         min( transcode ) over( partition by encno, paymentdate ) first_transcode,
     10         max( paymentdate ) over( partition by encno ) last_date,
     11         max( transcode ) over( partition by encno, paymentdate ) last_transcode
     12    from t
     13         )
     14   group by encno, first_date, last_date
     15  /
    
        ENCNO FIRST_DAT FIRST_DATE_AMT LAST_DATE LAST_DATE_AMT
    --------- --------- -------------- --------- -------------
       136589 12-JAN-05            250 25-FEB-05           100
       136590 10-DEC-04            260 05-FEB-05           400
       150850 01-FEB-05            100 01-MAR-05           650
    
    SQL@8i>
    If you wait till I get home ( where I have 9i installed ), I will show you a way of doing this on one pass with Analytics also, but with the KEEP aggregates clause.

  3. #3
    Join Date
    Oct 2003
    Posts
    20

    SQL - Aggregate and Scalar Info on Same Record

    JMartinez,

    The transaction code ("transcode") is independent of the encounter number. The transcode is solely used to differentiate the source of the payment - whether from payor, patient, or other third party.

    Hence, to capture payor payments only, I incorporated the payor transaction codes as "limits" within my initial brio query.

    Over the past 4 hours I have explored the possibility of deriving the results in a "piecemeal" fashion using the Hyperion (Brio) query tool via several limit joins. However, it appears that this would not be updateable within Microsoft Access unless I initally pulled the data from Oracle, saved to an excel spreadsheet, and then manually updated the Access database every day including the weekends.

    I wonder if there a way to initially populate the Access Database using VBA via an ADO connection by simultaneously querying an Oracle Database on a particular server and another Oracle Database on another server?? Then, I would build the Append and Update queries within Access as well. Is this even possible??

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Among the options, Access has what is call a 'Link Tables' options, in which you could, once you get the information you want, store this query into a VIEW, write a instead of trigger to update it, and then link it on Access using ODBC.

  5. #5
    Join Date
    Oct 2003
    Posts
    20
    Actually,

    The transcode determines if the payment is from a payor or a patient. For example, the following transcodes indicate a payor payment; 5004,5006,5007,and 5009.

    I will try the suggestion and post back.

  6. #6
    Join Date
    Oct 2003
    Posts
    20
    How would I store the query in a VIEW and write a trigger to update a Microsoft Access table?

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wow! You must have been hibernated or something for the last year!

  8. #8
    Join Date
    Oct 2003
    Posts
    20
    The following SQL Passthru Query does not "pull" the correct value for "LatestPayorPymtDate." How would I tweak this to obtain the correct value for "LatestPayorPymtDate" and also the following fields:

    AmtLatestPayorPymt
    DateFirstPayorPymt
    AmtFirstPayorPymt

    Thanks in advance.

    SELECT distinct AL1.ACCOUNT_ID as AcctOrig, AL1.ACCOUNT_ID as AcctCurrent, AL7.ENCOUNTER_NO as EncNo, AL2.CUSTOMER_TYPE as CustType, AL6.LAST_NAME as LastName, AL6.FIRST_NAME as FirstName, AL6.RECORDS_NO as RN, AL2.ADMIT_DATE as AdmitDate, AL2.DISCHARGE_DATE as DischDate, AL2.DATE_BILLED as DateBilled, AL2.TOTAL_PAYMENTS as TotPymts, AL2.TOTAL_CHARGES as TotChgOrig, AL2.TOTAL_CHARGES as TotChgCurrent, AL2.EXPECTED_PAYMENT as ExpReimbOrig, AL2.EXPECTED_PAYMENT as ExpReimbCurrent, AL1.TOTAL_PAYMENTS as TotInsPymtsOrig, AL1.TOTAL_PAYMENTS as TotInsPymtsCurrent, AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS as BalAfterInsPymts, AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS as BalAfterAllPymts, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as OrigRatio , AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as RatioLatest, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS as OthPymts, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES) as CoveredCharges, AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT) as AllowOrig, AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT) as AllowCurrent, AL7.DATE_INTERFACED as DateIdentified, AL2.LENGTH_OF_STAY as LOS, AL2.DRG_NO as DrgNo, MAX ( AL7.PAYMENT_DATE ) as LatestPayorPymtDate, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceCurrent, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceOrig

    FROM CV_OCXVW.ACCOUNT_PAYOR AL1, CV_OCXVW.CUSTOMER_ENCOUNTER AL2, CV_OCXVW.ACCOUNT_TRANSACTION_DETAILS AL5, CV_OCXVW.CUSTOMER AL6, CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL7 WHERE ( AL2.ENCOUNTER_NO = AL1.ENCOUNTER_NO AND AL2.ENCOUNTER_NO=AL5.ENCOUNTER_NO AND AL6.CUSTOMER_NO=AL2.CUSTOMER_NO AND AL7.ENCOUNTER_NO=AL1.ENCOUNTER_NO) AND (AL1.ACCOUNT_ID Not In ('D15','D16','D17'))
    AND AL2.EXPECTED_PAYMENT>0 AND AL5.TRANSACTION_CODE in ('86004','86020','86035','86036','86037') AND AL1.RANK=1 AND AL7.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012',' 47013') AND AL7.PAYMENT_DATE IN (SELECT AL14.PAYMENT_DATE FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14 WHERE (AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012', '47013'))) AND trunc(AL7.DATE_INTERFACED) = trunc (sysdate) - 1

    GROUP BY AL1.ACCOUNT_ID, AL1.ACCOUNT_ID, AL7.ENCOUNTER_NO, AL2.CustType, AL6.LAST_NAME, AL6.FIRST_NAME, AL6.RECORDS_NO, AL2.ADMIT_DATE, AL2.DISCHARGE_DATE, AL2.DATE_BILLED, AL2.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES, AL2.TOTAL_CHARGES, AL2.EXPECTED_PAYMENT, AL2.EXPECTED_PAYMENT, AL1.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS, AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS, AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES), AL7.DATE_INTERFACED,AL7.DATE_UPDATED, AL2.LENGTH_OF_STAY, AL2.DRG_NO HAVING AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS >=5000
    ORDER BY 26

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Shame on you, SpeedThink! Although this bunch of letters in post #8 certainly is some kind of a language, and it - seeing SELECT, FROM, GROUP BY and ORDER BY - indicates that this is a query, I'd say that this can hardly be called a structured query language. SQL, abbreviated. Are you really capable of reading this text and debugging it? If so, congratulations!

    I can't, and I won't. Yes, I have a formatter here and could format it in no time, but I refuse to do it. Of course, nobody asked me to - there are many wonderful forum members here who might do that (and someone probably will), but - in my opinion - posting such a question shows disrespect towards all of us.

    Finally, what about incorrect value you mentioned in readable part of your post? As far as I'm concerned, it's perfectly fine. Why wouldn't it be? You didn't provide tables' description, sample data, current result and expected result. I'm not a mind reader and can't guess what you got as a result and what you'd like to have.

    This is a perfect example of how posts shouldn't look like ... truly sad.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This is a perfect example of how posts shouldn't look like ... truly sad.
    Please do not hold back. Tell us how you really feel.
    Yes, I agree. Some posts simply do not deserve any response.
    Other Forums have the ability to never see posts from specifc users (as you well know).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    LOL, I feel much better now, thank you This few lines must have been like an exhaust. Phew!

  12. #12
    Join Date
    Oct 2003
    Posts
    20
    The "confusion" was not intentional.

    Yes, I am able to debug the majority of the SQL Script. I am trying to incorporate a subquery within a passthru query in Microsoft Access 2003 which is quite a challenge indeed! Although the task before me is great - it will be solved!

    "MAX ( AL7.PAYMENT_DATE ) as LatestPayorPymtDate" within the SQL Script is not providing the date of the latest payor payment. In fact, it is providing the date of the first payor payment. Maybe, I will try DMAX instead.

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DMAX is Access, not Oracle function. You can't use it in Oracle SQL.

    Is it possible that "al7.payment_date" is not a DATE column but CHARACTER, and string (which represents date) is stored in format which makes the MAX function - now applied to CHARACTER instead of DATE - return first instead of last value? Something like this:
    Code:
    SQL> CREATE TABLE proba (znak VARCHAR2(20), datum DATE);
    
    Table created.
    
    SQL> INSERT INTO proba VALUES
      2  ('2001.05.01.', TO_DATE('01.05.2001.', 'dd.mm.yyyy.'));
    
    1 row created.
    
    SQL> INSERT INTO proba VALUES
      2  ('18.05.2006.', TO_DATE('18.05.2006.', 'dd.mm.yyyy.'));
    
    1 row created.
    
    SQL> SELECT * FROM proba;
    
    ZNAK                 DATUM
    -------------------- --------
    2001.05.01.          01.05.01
    18.05.2006.          18.05.06
    
    SQL> SELECT MAX(znak), MAX(datum) FROM proba;
    
    MAX(ZNAK)            MAX(DATU
    -------------------- --------
    2001.05.01.          18.05.06
    
    SQL>

  14. #14
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Well, I don't know Access and I couldn't read that query, but when you say "Aggregate and Scalar on Same Record" my first thought is analytic functions (as I've just noticed JMartinez mentioned a while back).

    Code:
    SELECT deptno, ename, hiredate FROM emp
    ORDER BY deptno, hiredate;
    
    DEPTNO  ENAME       HIREDATE
    ------- ----------- ---------
    10      CLARK       09-JUN-81
    10      KING        17-NOV-81
    10      MILLER      23-JAN-82
    20      SMITH       17-DEC-80
    20      JONES       02-APR-81
    20      FORD        03-DEC-81
    20      SCOTT       09-DEC-82
    20      ADAMS       12-JAN-83
    30      ALLEN       20-FEB-81
    30      WARD        22-FEB-81
    30      BLAKE       01-MAY-81
    30      TURNER      08-SEP-81
    30      MARTIN      28-SEP-81
    30      JAMES       03-DEC-81
    
    
    SELECT deptno, ename, hiredate
         , FIRST_VALUE(hiredate) OVER(PARTITION BY deptno ORDER BY hiredate)
           AS first_hire
    FROM   emp
    ORDER BY deptno, hiredate;
    
    DEPTNO  ENAME       HIREDATE   FIRST_HIRE
    ------- ----------- ---------  ----------
    10      CLARK       09-JUN-81  09-JUN-81
    10      KING        17-NOV-81  09-JUN-81
    10      MILLER      23-JAN-82  09-JUN-81
    20      SMITH       17-DEC-80  17-DEC-80
    20      JONES       02-APR-81  17-DEC-80
    20      FORD        03-DEC-81  17-DEC-80
    20      SCOTT       09-DEC-82  17-DEC-80
    20      ADAMS       12-JAN-83  17-DEC-80
    30      ALLEN       20-FEB-81  20-FEB-81
    30      WARD        22-FEB-81  20-FEB-81
    30      BLAKE       01-MAY-81  20-FEB-81
    30      TURNER      08-SEP-81  20-FEB-81
    30      MARTIN      28-SEP-81  20-FEB-81
    30      JAMES       03-DEC-81  20-FEB-81:
    Last edited by WilliamR; 05-18-06 at 05:35.

  15. #15
    Join Date
    Oct 2003
    Posts
    20
    Thanks for the insight.

    I do not have the ability to create tables in the Oracle environment.

    I also do not have the ability to create analytics.

    Upon reviewing JMartinez's post again, I definitely think that I should explore coorelated subqueries within a passthru query to obtain the following information;

    LatestPayorPymtDate
    AmtLatestPayorPymt
    DateFirstPayorPymt
    AmtFirstPayorPymt

    The Passthru query previously displayed results in all correct fields except the "LatestPayorPymtDate." Consequently, shouldn't the "MAX" portion be on the subquery piece of the SQL script?

    For example,

    AND AL7.PAYMENT_DATE IN (SELECT MAX(AL14.PAYMENT_DATE) FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14 WHERE (AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012', '47013')))

    If this actually works ( I will test in a few minutes), then - how do I tweak the SQL script further to obtain the additional information (AmtLatestPayorPymt,DateFirstPayorPymt,AmtFirstPay orPymt)?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •