|
|
Posted by Erland Sommarskog on 10/01/51 11:40
Bill Bob (nospam@devdex.com) writes:
> The required output is the first row created by the data-entry program.
I have bad news for you. That query is not writeable with the tables
you have provided. There is no information in the Transaction table in which
order the rows were entered. Had you been using an numeric artificial key
for the transactions, we could have made a guess. But since GUID are
not ordered, there is not even a trace of information.
Best would of course have been a datetime value. Then again, I would
expect all rows for a voucher to be entered at once. And in any case,
I completely to fail see the point to showing only the first.
> I need to show a list of all the transactions of a particular type.
> Also, I need to show the name of the primary ledger that was involved in
> the transaction along with the voucherid, voucherdate, ledgername,
> transaction amount. I just need the Debit/Credit (Whichever is not Zero)
> from the first row from the Transactions table which matches the
> VoucherID.
Here is a query which does that, except that it does not take the "first
row", but just makes any arbitray choice. It is also likely to have
poor performance, because of the convertion forth and back to varchar
of the GUI,
SELECT v.VoucherID, v.VoucherNo, v.VoucherDate, l.LedgerName,
SUM(t.Credit - t.Debit) AS Amount
FROM Vouchers v
JOIN (SELECT TransactionID =
MIN(convert(varchar(36), TransactionID)),
VoucherID
FROM Transactions
GROUP BY VoucherID) AS t1 ON v.VoucherID = t1.VoucherID
JOIN Transactions t
ON convert(uniqueidentifier, t1.TransactionID) =
t.TransactionID
JOIN Ledgers l ON t.LedgerID = l.LedgerID
WHERE (v.VoucherTypeID = 1)
GROUP BY v.VoucherID, l.LedgerName, v.VoucherDate, v.VoucherNo,
v.VoucherTypeID
ORDER BY v.VoucherID, l.LedgerName, v.VoucherDate, v.VoucherNo
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|