Update long SQL query with TOP clause

Question

I'm not familiar with sql servers. I've very long query which took from other tech guy. I need update this query with top clause for filter first 10 results. I added TOP 10 after first SELECT, but not working as expected.

SELECT SalesTranHeader.id, CustID, OrgName, BillNo, TranDate, TranDesc, db = CASE
            WHEN trancode = 'T5'
            OR trancode LIKE 'F2'
            OR trancode LIKE 'F3'
            OR trancode LIKE 'F4'
            OR TranCode LIKE 'F11' THEN
                0
            ELSE
                [amount] - [OtherTranAmount] - [HignTemp] - [Penalty]
            END,
             cr = CASE
            WHEN [trancode] LIKE 't5'
            OR [trancode] LIKE 'F2'
            OR [trancode] LIKE 'F3'
            OR [trancode] LIKE 'F4'
            OR [trancode] LIKE 'F11' THEN
                [amount] - [OtherTranAmount] - [higntemp] - [Penalty]
            ELSE
                0
            END,
             ' ' AS bankID,
             [trancode]
            FROM
                salesTranHeader
            WHERE
                id <> 0
            AND CustID = '0002'
            AND TranDate BETWEEN CONVERT (
                DATETIME,
                '2014-01-01 00:00:00',
                102
            )
            AND CONVERT (
                DATETIME,
                '2015-01-01 00:00:00',
                102
            )
            UNION
                SELECT
                    IncomeTransactions.id,
                    IncomeTransactions.CustID AS CustID,
                    IncomeTransactions.CustName,
                    Str(IncomeTransactions.BillDoc),
                    IncomeTransactions.TranDate,
                    IncomeTranType.TranDesc,
                    0 AS db,
                    IncomeTransactions.Amount AS Cr,
                    BankID,
                    TranCode = CASE
                WHEN TranType = 32 THEN
                    'F3'
                ELSE
                    CASE
                WHEN TranType = 27 THEN
                    'F2'
                ELSE
                    CASE
                WHEN TranType = 42 THEN
                    'F11'
                ELSE
                    'C'
                END
                END
                END
                FROM
                    IncomeTransactions,
                    IncomeTranType
                WHERE
                    IncomeTransactions.TranType = IncomeTranType.id
                AND (
                    IncomeTransactions.TranType = 17
                    OR IncomeTransactions.TranType = 32
                    OR IncomeTransactions.TranType = 42
                    OR IncomeTransactions.TranType = 27
                )
                AND CustID = '0002'
                AND TranDate BETWEEN CONVERT (
                    DATETIME,
                    '2014-01-01 00:00:00',
                    102
                )
                AND CONVERT (
                    DATETIME,
                    '2015-01-01 00:00:00',
                    102
                )
                ORDER BY
                    5

Edit: Below is my current one (only added TOP 10). But result is same.

SELECT TOP 10 SalesTranHeader.id, CustID, OrgName, BillNo, TranDate, TranDesc, db = CASE
            WHEN trancode = 'T5'
            OR trancode LIKE 'F2'
            OR trancode LIKE 'F3'
            OR trancode LIKE 'F4'
            OR TranCode LIKE 'F11' THEN
                0
            ELSE
                [amount] - [OtherTranAmount] - [HignTemp] - [Penalty]
            END,
             cr = CASE
            WHEN [trancode] LIKE 't5'
            OR [trancode] LIKE 'F2'
            OR [trancode] LIKE 'F3'
            OR [trancode] LIKE 'F4'
            OR [trancode] LIKE 'F11' THEN
                [amount] - [OtherTranAmount] - [higntemp] - [Penalty]
            ELSE
                0
            END,
             ' ' AS bankID,
             [trancode]
            FROM
                salesTranHeader
            WHERE
                id <> 0
            AND CustID = '0002'
            AND TranDate BETWEEN CONVERT (
                DATETIME,
                '2014-01-01 00:00:00',
                102
            )
            AND CONVERT (
                DATETIME,
                '2015-01-01 00:00:00',
                102
            )
            UNION
                SELECT
                    IncomeTransactions.id,
                    IncomeTransactions.CustID AS CustID,
                    IncomeTransactions.CustName,
                    Str(IncomeTransactions.BillDoc),
                    IncomeTransactions.TranDate,
                    IncomeTranType.TranDesc,
                    0 AS db,
                    IncomeTransactions.Amount AS Cr,
                    BankID,
                    TranCode = CASE
                WHEN TranType = 32 THEN
                    'F3'
                ELSE
                    CASE
                WHEN TranType = 27 THEN
                    'F2'
                ELSE
                    CASE
                WHEN TranType = 42 THEN
                    'F11'
                ELSE
                    'C'
                END
                END
                END
                FROM
                    IncomeTransactions,
                    IncomeTranType
                WHERE
                    IncomeTransactions.TranType = IncomeTranType.id
                AND (
                    IncomeTransactions.TranType = 17
                    OR IncomeTransactions.TranType = 32
                    OR IncomeTransactions.TranType = 42
                    OR IncomeTransactions.TranType = 27
                )
                AND CustID = '0002'
                AND TranDate BETWEEN CONVERT (
                    DATETIME,
                    '2014-01-01 00:00:00',
                    102
                )
                AND CONVERT (
                    DATETIME,
                    '2015-01-01 00:00:00',
                    102
                )
                ORDER BY
                    5

Answer 1

You should add the TOP on the result of the UNION.

SELECT TOP 10 *
FROM (
    SELECT
        SalesTranHeader.id, 
        CustID, 
        OrgName, 
        BillNo, 
        TranDate, 
        TranDesc, 
        db = 
            CASE
                WHEN trancode IN('T5', 'F2,' ,'F3', 'F4', 'F11') THEN 0
                ELSE [amount] - [OtherTranAmount] - [HignTemp] - [Penalty]
            END,
        cr = 
            CASE
                WHEN trancode IN('T5', 'F2,' ,'F3', 'F4', 'F11') THEN [amount] - [OtherTranAmount] - [higntemp] - [Penalty]
                ELSE 0
            END,
        bankID = ' ',
        [trancode]
    FROM salesTranHeader
    WHERE
        id <> 0
        AND CustID = '0002'
        AND TranDate BETWEEN CONVERT(DATETIME, '2014-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-01 00:00:00', 102)

    UNION

    SELECT
        IncomeTransactions.id,
        CustID = IncomeTransactions.CustID,
        IncomeTransactions.CustName,
        STR(IncomeTransactions.BillDoc),
        IncomeTransactions.TranDate,
        IncomeTranType.TranDesc,
        db = 0,
        Cr = IncomeTransactions.Amount,
        BankID,
        TranCode = 
            CASE
                WHEN TranType = 32 THEN 'F3'
                WHEN TranType = 27 THEN 'F2'
                WHEN TranType = 42 THEN 'F11'
                ELSE 'C'
            END
    FROM IncomeTransactions
    INNER JOIN IncomeTranType
        ON IncomeTransactions.TranType = IncomeTranType.id
    WHERE
        IncomeTransactions.TranType IN(17, 32, 42, 27)
        AND CustID = '0002'
        AND TranDate BETWEEN CONVERT(DATETIME, '2014-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-01 00:00:00',102)
)t
ORDER BY 5

I've also made some simplification on the query.