asked on

How do i setup this SQL query?

The table I have looks similar to the following:

Sequence Number (Primary Key)
Cheque Amount
2ndChequeRequestedSEQ (Null able)

If 2ndChequeRequested is not null, that means there is another record (Where Sequence Number = 2ndChequeRequested). For Example:
SEQ CKAMT 2ndChequeRequested
1 $100.00 2
2 $400.00 Null
3 $50.00 Null
4 $500.00 Null
5 $150.00 6
6 $250.00 Null

What I need the select statement to do is give me the following Results
$500 (ckamt for 1 + 2)
$50 (ckamt for 3)
$500 (ckamt for 4)
$400 (ckamt for 5 + 6)

Sequence Type is Varchar while Ckamt is Decimal.
Any help would be greatly appreciated as I tried a few select case statements with zero luck. I am for some reason not able to figure out a way to code in SQL..

if 2ndChequeRequested is not null then
Ckamt = Select Sum(Ckamt + Select ckamt from table where SEQ = 2ndChequeRequested)
else
Select Ckamt from table
end if

DocSeltsam

Hi there,

is it possible to change the way the information is stored in the table?

If you could change the row `2ndChequeRequested`to smth like `belongsToCheque` and
store the value of the parent cheque things woll be quite easier..

--TheDoctor

Raja Jegan R

Try this one out:

Hope this solves your requirement

SELECT t1.SEQ, t1.CKAMT + COALESCE(T2.CKAMT, 0) AS AMT
FROM urtable t1 
LEFT JOIN
(SELECT SEQ, CKAMT, 2ndChequeRequested
FROM urtable 
WHERE 2ndChequeRequested IS NOT NULL) t2 on t1.SEQ = t2.SEQ

Open in new window

adathelad

Hi

Presumably, there's only ever 2 linked together like this and the 2nd check does not then link to 3rd and so on.

SELECT t1.ckamt + ISNULL(t2.ckamt, 0.00) AS TotalAmount
FROM YourTable t1
    LEFT JOIN YourTable t2 ON t1.2ndCheckRequested = t2.SEQ

Open in new window

Raja Jegan R

A small mistake:

Try this one out:

SELECT t1.SEQ, t1.CKAMT + COALESCE(T2.CKAMT, 0) AS AMT
FROM urtable t1 
LEFT JOIN
(SELECT SEQ, CKAMT, 2ndChequeRequested
FROM urtable 
WHERE 2ndChequeRequested IS NOT NULL) t2 on t1.SEQ = t2.2ndChequeRequested

Open in new window

reb73

Try -

SELECT	 t1.ckamt + 
	(SELECT ISNULL(ckamt, 0.00) FROM YourTable WHERE SEQ = t1.[2ndCheckRequested]) AS TotalAmount
	,'(ckamt for ' + CAST(t1.SEQ AS varchar(10)) + 
	(SELECT ISNULL(' + ' + CAST(SEQ AS varchar(10)), '') FROM YourTable WHERE SEQ = t1.[2ndCheckRequested]) + ')' AS SEQ
FROM YourTable t1
WHERE NOT EXISTS (SELECT NULL FROM YourTable WHERE [2ndCheckRequested] = t1.SEQ)

Open in new window

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnnyBCJ

ASKER

Thanks a lot!
Greatly appreciate the help!!!