Tough Sql Query

[Bill Bob]

I am going mad with this Query. I need to join 3 Tables. Their Formats
are

Vouchers
[VoucherID] [uniqueidentifie r] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarratio n] [varchar] (255)
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
Ledgers
[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,
[LedgerName] [varchar] (50) COLLATE
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifie r] NOT NULL ,
[VoucherID] [uniqueidentifie r] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transactions _Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT [FK_Transactions _Vouchers] FOREIGN KEY
(
[VoucherID]
) REFERENCES [Vouchers] (
[VoucherID]
)
) ON [PRIMARY]
GO
The Required Output is

ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 5 2001-09-03 Bank-1
0.00

But, I am getting More than One row from the transactions table. I just
need the first matching row

ID VoucherNo VoucherDate LedgerName Amount
1 1 2001-09-03 Bank-1 2400.00
2 2 2001-09-03 Cash 600.00
3 3 2001-09-03 TAX A/C 0.00
4 4 2001-09-03 Bank-1 4000.00
5 4 2001-09-03 Cash 400.00
6 5 2001-09-03 Bank-1 0.00
7 5 2001-09-03 Cash 5035.00

The Query I am using is

SELECT dbo.Vouchers200 1.VoucherID,
dbo.Vouchers200 1.VoucherNo,
dbo.Vouchers200 1.VoucherDate,
dbo.Ledgers.Led gerName,
SUM(dbo.Transac tions2001.Debit ) AS Amount

FROM dbo.Vouchers200 1 INNER JOIN
dbo.Transaction s2001
ON dbo.Vouchers200 1.VoucherID =
dbo.Transaction s2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transaction s2001.LedgerID =
dbo.Ledgers.Led gerID
WHERE (dbo.Vouchers20 01.VoucherTypeI D = 1)

GROUP BY dbo.Vouchers200 1.VoucherID,
dbo.Ledgers.Led gerName,
dbo.Vouchers200 1.VoucherDate,
dbo.Vouchers200 1.VoucherNo,
dbo.Vouchers200 1.VoucherTypeID
ORDER BY dbo.Vouchers200 1.VoucherID,
dbo.Ledgers.Led gerName,
dbo.Vouchers200 1.VoucherDate,
dbo.Vouchers200 1.VoucherNo

Plz help Out

*** Sent via Developersdex http://www.developersdex.com ***

[Tom Moreau]

I agree. It's a real challenge trying to divine the business spec from the
data! As it turns out, I'm trying to enter my accounting info into
QuickBooks and we have been the victims of double-entry bookkeeping. :-(

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Tom Moreau (to*@dont.spam. me.cips.ca) writes:

What business rules do you have? Based on your data, it looked like you
wanted SUM(Credit - Debit) but the desired output you posted earlier
doesn't match this.

That sum is hopefully always 0, or else something is really broken!

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>> As it turns out, I'm trying to enter my accounting info into QuickBooks and we have been the victims of double-entry bookkeeping. :-( <<

Good choice of words! I picked a book on matrix methods for accounting
about 20 years ago and never mimicked double-entry bookkeeping in my
programming again.

[Erland Sommarskog]

Bill Bob (no****@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(var char(36), TransactionID)) ,
VoucherID
FROM Transactions
GROUP BY VoucherID) AS t1 ON v.VoucherID = t1.VoucherID
JOIN Transactions t
ON convert(uniquei dentifier, t1.TransactionI D) =
t.TransactionID
JOIN Ledgers l ON t.LedgerID = l.LedgerID
WHERE (v.VoucherTypeI D = 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx