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
  • 358 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by Glyn_2
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.