March 19, 2013 at 8:44 pm
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
March 19, 2013 at 10:42 pm
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