, Guest>> || || |
/ Microsoft SQL Server
 SQL   [new]
nc1318
Member

:
: 13
:

CounterPartyID INT
PRIMARY KEY IDENTITY [Name] VARCHAR(255) IsActive BIT
1 1
2 0
3 1
CounterPartyID
[Name]
IsActive


TransID INT TransDate DATE RcvID INT SndID INT AssetID INT Quantity NUMERIC(19, 8)
1 01.01.2012 1 2 1 100
2 02.01.2012 1 3 2 150
3 03.01.2012 3 1 1 300
4 04.01.2012 2 1 3 50
TransID
TransDate
RcvID
SndID
AssetID (, , , ..)
Quantity
: .

:
SELECT A.CounterPartyID, A.Name, w.AssetID
FROM dbo.accounts A
JOIN dbo.wirings w ON A.CounterPartyID = w.RcvID
WHERE IsActive = 1
UNION ALL
SELECT A.CounterPartyID, A.Name, w.AssetID
FROM dbo.accounts A
JOIN dbo.wirings w ON A.CounterPartyID = w.SndID
WHERE IsActive = 1

, .
9 19, 09:26[21905310]      |
 Re: SQL   [new]
alexeyvg
Member

: Moscow
: 30773
nc1318
, .
select A.CounterPartyID, A.Name, count(*)
FROM (
    SELECT distinct A.CounterPartyID, A.Name, w.AssetID
    FROM dbo.accounts A
        JOIN dbo.wirings w ON A.CounterPartyID in (w.SndID, w.RcvID)
    WHERE A.IsActive = 1
) as A
group by A.CounterPartyID, A.Name
HAVING count(*) > 1
9 19, 10:43[21905337]      |
/ Microsoft SQL Server