Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Script Help - Need to capture a single transaction from many possibilities Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 8:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:05 PM
Points: 387, Visits: 418
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.49 59BBC4473A013DCEAD3009B4AC1D1A8C SELF 88BBC4473A012F73B0300949AC1D1A8C 400.49
Post #1432977
Posted Tuesday, March 19, 2013 10:42 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 9:05 PM
Points: 387, Visits: 418
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.
Post #1432989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse