Member Avatar for Glyn_2

I have a SQL Server bank account table which contains ...

ID (Identity)
Trdate (Date),
Description (Varchar),
Debit (Money),
Credit (Money),
Balance (Money)
Details (Varchar)

I want to list a subset of the transactions between two dates (the period of an Australian Financial year) and the query below works correctly

Select ID, TrDate, Description, Debit, Credit, Balance, Details From Bank_Acct Where TrDate Between '2009-07-01' and '2010-06-30' Order By TrDate, ID

but I want to also add into the result set another record at the beginning which is the opening or brought forward balance for that period and the query below works and returns the correct information when run on it's own

Select Top 1 ID, TrDate, 'Opening Balance' as Description, 0 as Debit, 0 as Credit, Balance, '' as Details From Bank_Acct Where TrDate<'2009-07-01' Order By TrDate Desc, ID Desc

However when I attempt to do a union of these two queries I get an error on the order by clause for the part of the query that returns the opening balance.

Select ID, TrDate, Description, Debit, Credit, Balance, Details From Bank_Acct Where TrDate Between '2009-07-01' and '2010-06-30'
Union
(Select Top 1 ID, TrDate, 'Opening Balance' as Description, 0 as Debit, 0 as Credit, Balance, '' as Details From Bank_Acct Where TrDate<'2009-07-01' Order By TrDate Desc, ID Desc)
Order By TrDate, ID

How can I get around that problem?
While I haven't shown this fact in my examples above the dates are normally passed as parameters to a stored SQL query so that I can easily choose a different period.

Thanks

  • 2 Contributors
  • 2 Replies
  • 380 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by Glyn_2

Dani AI

Generated

's suggestion is the practical quick fix — wrapping the TOP query in a subselect lets SQL Server apply the ORDER BY that selects the single pre-period row, while a single ORDER BY on the combined result controls final presentation. 's follow-up confirms that approach works.

If you want an alternative that avoids relying on picking "the last row" (useful if the running balance column is not perfectly maintained), compute the opening balance as an aggregate and tag rows with a sort key so the opening row always appears first. Example pattern:

WITH Opening AS (
  SELECT
    'Opening Balance' AS Description,
    SUM(COALESCE(Credit,0) - COALESCE(Debit,0)) AS Balance,
    '' AS Details
  FROM Bank_Acct
  WHERE TrDate < @StartDate
)
SELECT 0 AS SortKey, NULL AS ID, @StartDate AS TrDate, Description, 0.00 AS Debit, 0.00 AS Credit, Balance, Details
FROM Opening

UNION ALL

SELECT 1 AS SortKey, ID, TrDate, Description, Debit, Credit, Balance, Details
FROM Bank_Acct
WHERE TrDate BETWEEN @StartDate AND @EndDate

ORDER BY SortKey, TrDate, ID;

A few practical tips:

  • Use UNION ALL unless you explicitly need duplicate-elimination; UNION adds unnecessary work.
  • Ensure the column order and data types match across both parts of the UNION.
  • If you rely on the stored running-balance field, the TOP-1 (wrapped) approach is simplest and usually fastest if TrDate/ID are indexed. If you compute the opening balance with SUM, be explicit about sign conventions and handle NULLs with COALESCE.
  • Parameterize dates and ensure your date literals use an unambiguous format or parameters to avoid regional-conversion issues.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

This might work:

Select ID, TrDate, Description, Debit, Credit, Balance, Details From Bank_Acct Where TrDate Between '2009-07-01' and '2010-06-30'
Union
select * from (Select Top 1 ID, TrDate, 'Opening Balance' as Description, 0 as Debit, 0 as Credit, Balance, '' as Details From Bank_Acct Where TrDate<'2009-07-01' Order By TrDate Desc, ID Desc) Tmp
Order By TrDate, ID
Member Avatar for Glyn_2

And, in fact, it DOES work - thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.