SQL Help

[Admiral Huddy]

I have a query that outputs the following

Client, Deal Sequence, Balance
1000,1,345.56
1001,1,300.00
1001,2,300.00
1001,2,300.00
1002,1,345.00

SELECT

D.CIF_NO,
D.SERIAL_NO,
(select sum(-1 * (CASE ACCOUNT_GROUP WHEN 'CALL' THEN CV_AVAIL_BAL ELSE 0 END))  from  ACCOUNTS A where D.CIF_NO = A.CIF_SUB_NO ) as Balance

from
    DEALS D

I'd like to change the query so that the balance only appear on the first occurrence where there is more than 1 record per client

so the example above should look like this:

Client, Deal Sequence, Balance
1000,1,345.56
1001,1,300.00
1001,2,0
1001,2,0
1002,1,345.00

I'm a bit stuck on how I can achieve this tbh.

Any help would be appreciated.

Thanks

 

[Admiral Huddy]

hi tom,

I should have said this is Sybase.

 

[Admiral Huddy]

Select D.Client, D.Balance From Deals D where Sequence = (Select Min(D1.Sequence) From Deals D1 Where D.Client = D1.Client);

This helped me crack the problem.. Thanks skyripper.

 

[Admiral Huddy]

Kind of related to the above, I have another one sorry, I'm fairly new to SQL

I'd like to read through the following table, updating the BALANCE to zero where a client (CIF_NO) previously has a balance in the table. A balance should only be shown for the most highest amount of days overdue for each client..



If you look at CIF - 10001461, the top entry (serial No. 10471) correctly has a balance of 17.85 because it it's the oldest overdue, but subsequent balances for the same client should be updated with a zero.

In the example above, the highlighted balanced should be zero.

any ideas on how I can achieve that, please?


Thanks

 

[Admiral Huddy]

thank biily .. looks good that but I get a syntax error near the keyword TOP.?

UPDATE T_QIB_MIDEALS_OVERDUE
SET BALANCE = 0
WHERE DAYS_OVERDUE <> (SELECT TOP 1 b.DAYS_OVERDUE FROM T_QIB_MIDEALS_OVERDUE b WHERE b.CIF_NO = CIF_NO ORDER BY b.PAYMENT_DATE ASC)

I know you can't have alias on the update table so changed to this:


UPDATE T_QIB_MIDEALS_OVERDUE
SET BALANCE = 0
FROM T_QIB_MIDEALS_OVERDUE a
WHERE a.DAYS_OVERDUE <> (SELECT TOP 1 b.DAYS_OVERDUE FROM T_QIB_MIDEALS_OVERDUE b WHERE b.CIF_NO = a.CIF_NO ORDER BY b.PAYMENT_DATE ASC)

But i still get the same syntax error.

thank you

edit possibly the order by isn't allowed in the subquery?

 

[Admiral Huddy]

Thanks

Not sure CROSS APPLY is available in SYBASE