SQL Help

Admiral Huddy · 5 May 2021 at 08:11

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

Code:

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

tom_e · 5 May 2021 at 08:18

What version of SQL are you using?

Off the top of my head if using MS SQL > 2012 IIRC then you could look at using the ROW_NUMBER window function and only display the balance where Row Number = 1

Admiral Huddy · 5 May 2021 at 08:28

hi tom,

I should have said this is Sybase.

Randomface74 · 5 May 2021 at 08:54

May as well throw away rows if the data isn't gonna be correct. So.. where deal sequence =1.

Otherwise..
If deal sequence = 1 select balance else select 0

skyripper · 5 May 2021 at 10:50

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

Admiral Huddy · 6 May 2021 at 16:14

skyripper said:
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.

skyripper · 6 May 2021 at 22:33

No worries. Glad I could help.

Admiral Huddy · 7 May 2021 at 18:09

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

Randomface74 · 7 May 2021 at 18:21

UPDATE Table a SET a.Balance = 0 WHERE a.ID <> (SELECT TOP 1 b.ID FROM Table b WHERE b.CIF_NO = a.CIF_NO ORDER BY PAYMENT_DATE ASC)

Admiral Huddy · 10 May 2021 at 09:52

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?

KompuKare · 10 May 2021 at 10:23

Admiral Huddy said:
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?

That would have to a Sybase thing as Microsoft SQL is perfectly happy to use aliases and it is often the best syntax:

Code:

UPDATE a
SET SomeColumn = b.SomeColumn
FROM TableB b
JOIN TableA a ON a.ID = b.ID

Anyway, how about the following:

Code:

UPDATE t
SET BALANCE = 0
FROM T_QIB_MIDEALS_OVERDUE t
    CROSS APPLY (    SELECT TOP 1 CIF_NO, PAYMENT_DATE
                                FROM T_QIB_MIDEALS_OVERDUE iT
                                WHERE iT.CIV_NO = t.CIF_NO
                                ORDER BY PAYMENT_DATE  DESC) i
WHERE t.CIF_NO = i.CIF_NO
    AND t.PAYMENT_DATE <> i.PAYMENT_DATE

Admiral Huddy · 10 May 2021 at 10:28

Thanks

Not sure CROSS APPLY is available in SYBASE

KompuKare · 10 May 2021 at 11:45

Admiral Huddy said:
Thanks

Not sure CROSS APPLY is available in SYBASE

Syntax variations can be tricky, but if this is the correct documentation it seems to be available:
http://dcx.sybase.com/1101/en/dbusage_en11/apply-joins-joinsasp.html
and it looks the same as for Microsoft TSQL:

Code:

SELECT D.DepartmentName, HS.Name
  FROM Departments D
    CROSS APPLY (
        SELECT E.GivenName || ' ' || E.Surname
            FROM Employees E
            WHERE E.DepartmentID = D.DepartmentID AND E.Salary > 80000
    ) HS( Name );

EDIT: change the example to one which is uses the outer columns in the WHERE clause.