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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy