0

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
9
Views
1 Year
Discussion Span
Last Post by Glyn_2
0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.