SQL Script Help - Need to capture a single transaction from many possibilities

  • Here is the DLL

    IF EXISTS ( SELECT

    *

    FROM

    sys.objects

    WHERE

    object_id = OBJECT_ID(N'[dbo].[XX_TEMP_AR_INSERT]') AND

    type IN ( N'U' ) )

    DROP TABLE [dbo].[XX_TEMP_AR_INSERT]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID] [varchar](32) NULL

    , [PAYER_ID] [varchar](15) NULL

    , [AMOUNT] [decimal](12 , 2) NULL

    , [BILLING_SITE_ID] [varchar](15) NULL

    , [TRANSFER_ID] [varchar](32) NULL

    )

    ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    'DA6773113801A85B803C0F3FAC1D1A8D'

    , '0466'

    , '-100.78'

    , '400'

    , '348C701138017AAD2203767EAC1B1238'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    'FA6773113801D9A6833C0F24AC1D1A8D'

    , 'SELF'

    , '100.78'

    , '400'

    , '348C701138017AAD2203767EAC1B1238'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    '8411E299A994420681210E3AB07E8ABF'

    , 'SELF'

    , '-100.78'

    , '400'

    , '2F525D70E0B74634A82E26983E3EBCA9'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    '42FAE87AC9774132ADDA13570402AC1C'

    , 'SELF'

    , '100.78'

    , '900'

    , '2F525D70E0B74634A82E26983E3EBCA9'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    '59BBC4473A013DCEAD3009B4AC1D1A8C'

    , '0466'

    , '-400.49'

    , '400'

    , '8620BF473A01AFE3BE03AF49AC1B1339'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    '88BBC4473A012F73B0300949AC1D1A8C'

    , 'SELF'

    , '400.49'

    , '400'

    , '8620BF473A01AFE3BE03AF49AC1B1339'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    'B84A37A6E93B4882B6289C016EC47868'

    , 'SELF'

    , '-400.49'

    , '400'

    , '9EEFA7DEE2CA4318B7B140148E111293'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    'E49651E5715046269AF023332CB50C40'

    , 'SELF'

    , '400.49'

    , '900'

    , '9EEFA7DEE2CA4318B7B140148E111293'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    '758237E78D5A4458A4DD6B2BAD3F602D'

    , 'SELF'

    , '501.27'

    , '901'

    , '28F880EDC0D148E9B2C3393161CA509A'

    )

    INSERT INTO [dbo].[XX_TEMP_AR_INSERT]

    (

    [SYS_ID]

    , [PAYER_ID]

    , [AMOUNT]

    , [BILLING_SITE_ID]

    , [TRANSFER_ID]

    )

    VALUES

    (

    '916E1F1F238E4EA48D1C31525C846372'

    , 'SELF'

    , '-501.27'

    , '900'

    , '28F880EDC0D148E9B2C3393161CA509A'

    )

    Here is query that I'm working with

    SELECT

    [JArd1].[PAYER_ID] AS TRANSFER_FROM

    , [JArd1].[AMOUNT]

    , [JArd1].[SYS_ID]

    , [JArd2].[PAYER_ID] AS TRANSFER_TO

    , [JArd2].[SYS_ID]

    , [JArd2].[AMOUNT]

    FROM

    [dbo].[XX_TEMP_AR_INSERT] AS Ard

    JOIN

    (

    SELECT

    [Ard2].[SYS_ID]

    , [Ard2].[PAYER_ID]

    , [Ard2].[TRANSFER_ID]

    , [Ard2].[AMOUNT]

    FROM

    [dbo].[XX_TEMP_AR_INSERT] AS Ard2

    WHERE

    [Ard2].[AMOUNT] < 0.00

    ) JArd1

    ON [Ard].[SYS_ID] = [JArd1].[SYS_ID]

    JOIN

    (

    SELECT

    [Ard3].[SYS_ID]

    , [Ard3].[PAYER_ID]

    , [Ard3].[TRANSFER_ID]

    , [Ard3].[AMOUNT]

    FROM

    [dbo].[XX_TEMP_AR_INSERT] AS Ard3

    WHERE

    [Ard3].[AMOUNT] > 0.00

    ) JArd2

    ON [JArd1].[TRANSFER_ID] = [JArd2].[TRANSFER_ID]

    WHERE

    [JArd1].[PAYER_ID] <> [JArd2].[PAYER_ID]

    What I'm trying to capture is the last transaction to occur in XX_TEMP_AR_INSERT where the balance goes from payer "X" to payer "Y" and payer "X" <> payer "Y".

    When you run SELECT * FROM XX_TEMP_AR_INSERT, I want the query above to capture data from rows 5 & 6 only. Ignore rows 1 & 2 because they are the first occurence of the a transfer.

    These are the results that I want the query to return:

    0466-400.4959BBC4473A013DCEAD3009B4AC1D1A8CSELF88BBC4473A012F73B0300949AC1D1A8C400.49

  • I'm going to go ahead and answer my own question. I create a temp table and inserted my SQL Statement into it. The I ran this code against it.

    SELECT DISTINCT

    [xth].[CLAIM_NUMBER]

    , [xth].[TRANSFER_FROM]

    , [xth].[FROM_AMOUNT]

    , [xth].[TRANSFER_TO]

    , [xth].[TO_AMOUNT]

    , CONVERT(VARCHAR(32) , [xth].[MAX_TIME] , 110) AS [MAX_TIME]

    , [xth].[STATUS]

    FROM

    [#XX_TEMP_HOLD] AS xth

    JOIN

    (

    SELECT DISTINCT

    [xth].[CLAIM_NUMBER]

    , MAX([xth].[MAX_TIME]) AS MAX_TIME

    FROM

    [#XX_TEMP_HOLD] AS xth

    GROUP BY

    [xth].[CLAIM_NUMBER]

    --ORDER BY [xth].[CLAIM_NUMBER]

    ) JTemp

    ON [xth].[CLAIM_NUMBER] = [JTemp].[CLAIM_NUMBER] AND

    [xth].[MAX_TIME] = [JTemp].[MAX_TIME]

    That gave me the distinct LAST record I needed.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply