T-SQL, Omit Unwanted Results

CREATE TABLE #t(LocationCode varchar(10), ResourceId int, TransType char(3))

INSERT #t
SELECT 'STORE 001', 1, 'In' UNION ALL

SELECT 'STORE 002', 2, 'In' UNION ALL

SELECT 'STORE 003', 3, 'In' UNION ALL

SELECT 'STORE 001', 1, 'Out' UNION ALL
SELECT 'STORE 004', 1, 'In'  UNION ALL

SELECT 'STORE 004', 4, 'In' UNION ALL
SELECT 'STORE 004', 4, 'Out' UNION ALL

SELECT 'STORE 004', 1, 'Out' UNION ALL
SELECT 'STORE 001', 1, 'In'  

DROP TABLE #t

How to show only the items with the corresponding location having maximum number of "Ins" when compared with "Outs" (sorry for my bad english).

LocationCode              ResourceId
STORE 001[edited]     开发者_运维知识库    1
STORE 002                 2
STORE 003                 3

---

Assuming you only want Ins where there isn't a matching Out.

SELECT *
FROM #t AS a
WHERE a.TransType = 'In'
    AND NOT EXISTS (
        SELECT *
        FROM #t AS b
        WHERE b.TransType = 'Out'
            AND b.LocationCode = a.LocationCode
            AND b.ResourceId = a.ResourceId
    )

You'd need more data in your schema to be able to match an Out with an In by time.

Try something simpler like this:

SELECT LocationCode, ResourceID
FROM #t
GROUP BY LocationCode, ResourceID
HAVING COUNT(*) % 2 = 1

Here's an example where the transactions are sequenced and two ways to use that sequence:

CREATE TABLE #t(LocationCode varchar(10), ResourceId int, TransType char(3), Seq int UNIQUE NOT NULL)

INSERT #t
SELECT 'STORE 001', 1, 'In', 1 UNION ALL
SELECT 'STORE 002', 2, 'In', 2 UNION ALL
SELECT 'STORE 003', 3, 'In', 3 UNION ALL
SELECT 'STORE 001', 1, 'Out', 4 UNION ALL
SELECT 'STORE 004', 1, 'In', 5 UNION ALL
SELECT 'STORE 004', 4, 'In', 6 UNION ALL
SELECT 'STORE 004', 4, 'Out', 7 UNION ALL
SELECT 'STORE 004', 1, 'Out', 8 UNION ALL
SELECT 'STORE 001', 1, 'In', 9 

;WITH Ins AS (
SELECT * FROM #t
WHERE TransType = 'In'
)
,Outs AS (
SELECT * FROM #t
WHERE TransType = 'Out'
)
,Matched AS (
SELECT *,
(SELECT MIN(Seq)
FROM Outs
WHERE Outs.LocationCode = Ins.LocationCode
AND Outs.ResourceID = Ins.ResourceID
AND Outs.Seq > Ins.Seq) AS OutSeq
FROM Ins
)
SELECT *
FROM Matched
WHERE OutSeq IS NULL

;WITH LastIn AS (
SELECT ResourceID, MAX(Seq) AS Seq
FROM #t
WHERE TransType = 'In'
GROUP BY ResourceID
)
SELECT *
FROM LastIn
WHERE NOT EXISTS (
SELECT *
FROM #t outs
WHERE outs.TransType = 'Out'
AND Outs.ResourceID = LastIn.ResourceID
AND outs.Seq > LastIn.Seq)

DROP TABLE #t​​​​​​​​​​​