Hi,

I need to write sql query for the following scenarios.

We have 3 financial systems (sys1, sys2, sys3) where a same transaction gets entered independently into each system OR entered into one and exported to the other system. Transaction need not be in all 3 systems. We need to create reconcile report to see the status of transaction and display if it is reconciled or not. For this, in our stored procedure we are pulling data from 3 systems into temp tables and using CTE and other logic marking each transaction status in each system temp table. One of the systems (eg sys1), we made it as source.

Sys1 temp table has both parent and child records and are distinguished using type. Sys2 and sys3 has only children records.  When report is created, we are showing parent  from sys1 temp table and children from new temp table where children status is updated based on availability of it in all 3 systems.

DECLARE @sys1 TABLE

            (

                        ID int,

                        childID varchar(20),

                        ParentID varchar(20),

                        RecType decimal(3,0),

                        SettleDate smalldatetime,

                        Principal money,

                        Sys3ID varchar(16) NULL,

                        Sys2ID int NULL,

                        Status varchar(25) NULL

            )

DECLARE @sys2 TABLE

            (

                        TxID int PRIMARY KEY NOT NULL,

                        CommonTransactionID varchar(16),

                        SettleDate smalldatetime,

                        Par money,

                        Sys3ID varchar(16) NULL,

                        Sys1ChildID,

                        Sys1ParentID bigint

            )

DECLARE @sys3 TABLE

            (

                        Sys3ID varchar(16),

                        REFERENCE varchar(16),

                        VALUE_DATE datetime,

                        DIRECTION char(1),

                        AMOUNT money,

                        RecSource varchar(2)

            )

Insert Into @sys1 (ID, childID, ParentID, RecType, SettleDate, Principal)

Select 172876, 217955, 217954, 100, 2015-03-01, 100.00

Union

Select 172877, 217956, 217955, 50, 2015-03-01, 15.00

Union

Select 172878, 217957, 217955, 50, 2015-03-01, 25.00

union

Select 172879, 217958, 217955, 50, 2015-03-01, 10.00

Union

Select 172880, 217959, 217955, 50, 2015-03-01, 10.00

union

Select 172881, 217960, 217955, 50, 2015-03-01, 40.00

Insert Into @sys2(TxID, Sys1ID, settleDate, Par)

Select 4336620, 217956, 2015-03-01, 15.00

Union

Select 4336621, 217957, 2015-03-01, 25.00

union

Select 4336613, 217958, 2015-03-01, 10.00

Union

Select 4336614, 217959, 2015-03-01, 10.00

union

Select 4336615, 217960, 2015-03-01, 40.00

Insert into @sys3(Sys3ID, Reference, Value_Date, Direction, Amount)

Select 1, 5654471 4336620, 2015-03-01, O, 15.00

Union

Select 2, 5654481 4336621, 2015-03-01, 'O',25.00

Union

Select 3, 5654491 4336613, 2015-03-01, 'O',10.00

Union

Select 4, 5654501 4336614, 2015-03-01, 'O',10.00

Union

Select 5, 5654511 4336615, 2015-03-01, 'O', 40.00

After going thru lot of other logic, final temp table will have only children with status assigned. The above temp table data is only for 1 scenario.

The following are status of children.

This is how status of children is determined:

• Not Settled All child records start as Not settled in temp tables.
• Settled when record exists in sys3 and other criteria is met.
• Partially settled record exists in sys3 and either in sys1 or sys2 or other criteria is not met.
• Reconciled child record should exist in all 3 systems and all criteria is match
• Mismatched record has wrong amount when compared in any of 2 systems.

**************** My Part below*******************

My part is to update the status of parent based on children status and parent amount must match sum of child amounts. If amounts dont match, then leave the status of parent as null.

Determining the status of parent:

• Not Settled None of children has yet settled.
• Settled All children are settled.
• Partially settled some of children are as settled OR 1+ children are partially settled.
• Reconciled All children are reconciled.
• Partially Reconciled some children are partially reconciled.
• Null 1 or more childen has a status of mismatched.
• AND sum of children amount should match parent amount

How can I update the status of parent based on all children and sum of amount of children equal to parent amount.

Thanks,

Spunny

How is Table 3 related to the other two tables?

Thank You for the response Jason. Table 2 is related to table 3 via reference column.

5654471 4336620 - In this 4336620 is the ID of table 2. We need to parse the column when joining.

Hi Spunny,

The logic in your description is too vague to understand. Could you please clarify.

Not Settled All child records start as Not settled in temp tables.
    what is NOT Settled ? does that mean a null SettleDate in sys2 or just record doesn't exist in
    sys3?

Settled when record exists in sys3 and other criteria is met.
    what is the other criteria is here. does Settled mean record exist in all 3 sys tables?

Partially settled record exists in sys3 and either in sys1 or sys2 or other criteria is not met.
    Is the status should be partially settled in the case record exists in sys3, sys1 but not in sys2
    what is "or othere criteria" here.

Reconciled child record should exist in all 3 systems and all criteria is match
    does this mean record exist in all 3 sys tables and amount matched under the condition of
    Settled

Mismatched record has wrong amount when compared in any of 2 systems.
    does this mean record exist in all 3 sys tables but the amount not matched under the condition of
    Settled

Ignoring the above vagues, I believe FULL JOINs among 3 tables could help to determine the status.

If you have any feedback on our support, you can click here.

Hi Eric,

Thanks for response. Sorry if it is confusing. Don't worry about the tables. After doing logic I get final result set of all children with status in to final temp table in my stored procedure. Then I need to tie it to parent table and update status of parent.

This is how I want.

1) Scenario 1:

Parent (Amount 25)

     child 1 Reconciled (amount 10)

     child 2 Partially settled (amount 10)

     child 3 Reconciled (amount 5)

In above case Parent status should be 'partially settled' even though sum of 3 children matches to parent amount.

2) Scenario 2:

Parent (30)

     child 1 Reconciled (amount 10)

     child 2 Reconciled (amount 10)

In the above case, even though children are reconciled, parent can't be set to 'Reconciled' because amount won't match. It should be set to 'amount mismatch'

3) Scenario 3:

Parent (30)

     child 1 Reconciled (amount 10)

     child 2 Reconciled (amount 20)

In the above case, parent status should be set to 'Reconciled' because all children are reconciled and amount matches.

4) Scenario 4:

Parent (30)

     child 1 Not Settled (amount 10)

     child 2 Reconciled (amount 20)

Parent should be set to 'Not Settled' even though amount matches

Thanks,

Spunny

>> We have 3 financial systems (sys1, sys2, sys3) where the same transaction gets entered independently into each system OR entered into one and exported to the other system. Transaction need not be in all 3 systems. <<

Your design is fundamentally wrong. In RDBMS, we want to have one fact, one way, in one place in the schema. The goal of all databases is to remove redundancy, not increase it. This not just SQL; this was true for hierarchical and network databases before them! 

>> We need to create reconcile report to see the status of transaction and display if it is reconciled or not. For this, in our stored procedure we are pulling data from 3 systems into temp tables and using CTE and other logic marking each transaction status in each system temp table. One of the systems (eg sys1), we made it as source. <<

You have re-invent the worst of 1970's file processing, but you want to use a temp table instead of scratch tape. This is not RDBMS or good SQL. 

>> Sys1 temp table has both parent [sic] and child [sic] records [sic] and are distinguished using type. Sys2 and sys3 has only children [sic] records [sic]. When report is created, we are showing parent from sys1 temp table and children from new temp table where children [sic] status is updated based on availability of it in all 3 systems. <, 

The terms child and parent are not part of RDBMS. They come from network databases. You are building fake pointer chains we do have referenced and referencing tables. Or do you mean to model weak and strong entities? Where is the DRI actions?

These things are not tables! They have no keys, so they are called (garbage) piles. But even the garbage is wrong. There is no generic id in RDBMS; it has to be <something in particular>_id to be valid. Since you do not do math on it, it should not be a numeric. 

A rec_type is a nominal scale, so it cannot be a numeric either! Likewise, we have no generic status, but a status is state of being so it has to have a temporal dimension. And did you know that reference is a reserved word in SQL as well as another ISO-11179 violation. 

The MONEY data type does not do correct math. Google it! It is another Sybase left-over that nobody should ever use. 

Finally, you used the old Sybase INSERT INTO ..SELECT..), .. disaster instead of the ANSI/ISO Standard VALUES table constructor. 

>> This is how status of children [sic] is determined: ..
My part is to update the status of parent [sic] based on children [sic] status and parent [sic] amount must match sum of child [sic] amounts.<<

Your narrative describes what Tom Johnston called a Non-Normal Form Redundancy. We do not put summary data in the strong entity; another basic data modeling principle. We build a VIEW that gives us the current summary. Your mindset is still in punch cards and magnetic tape files. 

"No matter how far you have gone down the wrong road, turn around!" -- Turkish proverb. 

Can you start over and do it right? 

Hi Spunny,

Thanks for the clarification, the scenarios listed do help to comprehend the logic.

For this case, since the status is enumerable, I would suggest to use a auxilliary table as below.

CREATE TABLE [Status]
(
ID INT,
Status VARCHAR(99)
)

INSERT INTO [Status] VALUES
(1,'Reconciled'),  --you should assign the id sequence based on the status priority
(2,'Partially Reconciled'),
(3,'Settled'),
(4,'Partially Settled'),
(5,'Mismatched')

You can join the [Status] table and aggregate grouping by parent. See the pseudo code as below.

;WITH cte AS(
SELECT ParentID,CASE WHEN SUM(childAmount)=MAX(parentAmount) THEN MAX(s.ID) ELSE 5 END AS Status_ID FROM ParentChildren p JOIN [Status] s ON p.childStatus = s.Status
GROUP BY ParentID 
)
SELECT * FROM cte c JOIN [Status] s ON C.Status_ID=s.ID

If you have any feedback on our support, you can click here.