I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [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] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] 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.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.developersdex.com *** 12  2661 
The first column in the resultset is ID, but you don't say from which the ID
originates, since there is no such column in any table. Also, you did not
provide INSERT statements of sample data.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada www.pinpub.com
..
"Bill Bob" <no****@devdex.com> wrote in message
news:MT****************@news.uswest.net...
I am going mad with this Query. I need to join 3 Tables. Their Formats
are
Vouchers
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [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] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] 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.Vouchers2001.VoucherID,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherDate,
dbo.Ledgers.LedgerName,
SUM(dbo.Transactions2001.Debit) AS Amount
FROM dbo.Vouchers2001 INNER JOIN
dbo.Transactions2001
ON dbo.Vouchers2001.VoucherID =
dbo.Transactions2001.VoucherID INNER JOIN
dbo.Ledgers ON dbo.Transactions2001.LedgerID =
dbo.Ledgers.LedgerID
WHERE (dbo.Vouchers2001.VoucherTypeID = 1)
GROUP BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo,
dbo.Vouchers2001.VoucherTypeID
ORDER BY dbo.Vouchers2001.VoucherID,
dbo.Ledgers.LedgerName,
dbo.Vouchers2001.VoucherDate,
dbo.Vouchers2001.VoucherNo
Plz help Out
*** Sent via Developersdex http://www.developersdex.com ***
Voucher Table
7332429B-22BF-49B8-A5B6-00094FF9DD59 4 1
2001-07-04 19:54:00.000
26D65B5D-E389-4E74-8605-000EC9F10575 14 1
2001-07-30 15:49:00.000
A40996E6-0581-48D9-91A9-000F1E0D0B46 14 1
2001-09-17 15:23:00.000
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4 1
2001-09-15 21:48:00.000
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4 1
2001-06-04 18:28:00.000
Transactions Table
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B 1 .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T 25 48.0000
..0000
3221E16C-CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACECA978 B 1 .0000
..5000
1736F5A0-EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T 1 2.0000
..0000
B475F07A-7012-4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B 232 .0000
..5000
Ledger Table
1 Cash
2 Profit & Loss A/c
3 Branch-1
4 ReserveSurplus-1
5 DutiesTaxes-1
*** Sent via Developersdex http://www.developersdex.com ***
Would it be possible to get these as INSERT VALUES statements?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada www.pinpub.com
..
"Bill Bob" <no****@devdex.com> wrote in message
news:8p***************@news.uswest.net...
Voucher Table
7332429B-22BF-49B8-A5B6-00094FF9DD59 4 1
2001-07-04 19:54:00.000
26D65B5D-E389-4E74-8605-000EC9F10575 14 1
2001-07-30 15:49:00.000
A40996E6-0581-48D9-91A9-000F1E0D0B46 14 1
2001-09-17 15:23:00.000
6EE61DEC-C157-4371-AAD3-0018B70B1A96 4 1
2001-09-15 21:48:00.000
267E5D45-F38D-4FB8-A2DF-00210BF9D037 4 1
2001-06-04 18:28:00.000
Transactions Table
CBBD8EBE-55BA-4039-9C3B-0537FE348470
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB B 1 .0000
600.0000
836E2414-6E6B-4608-BF63-0A15DBD540DA
96C2D6F7-C3EF-4A92-9861-EA8E99E2D297 T 25 48.0000
0000
3221E16C-CB2E-487E-A875-4613BAFB40D2
4A181205-67B7-4B38-A9C8-D416ACECA978 B 1 .0000
5000
1736F5A0-EBE3-4494-B075-52216E73E857
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB T 1 2.0000
0000
B475F07A-7012-4DC8-B0C9-7BE8A66493C8
4A181205-67B7-4B38-A9C8-D416ACECA978 B 232 .0000
5000
Ledger Table
1 Cash
2 Profit & Loss A/c
3 Branch-1
4 ReserveSurplus-1
5 DutiesTaxes-1
*** Sent via Developersdex http://www.developersdex.com ***
Bill Bob (no****@devdex.com) writes: I am going mad with this Query. I need to join 3 Tables. Their Formats
are
...
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
I'm afraid that if you want help, you will need to put more effort
into your postings.
It appears that your output produce rows that are not desired, but
you don't give any business rules to select the "first matching row".
What I see is that you have a voucher table, but the same voucher number
can appear more than once. This looks very strange to me, but I don't
know the business domain. Vochers are apparently linked to transactions
by voucher id, and since you use SUM, I assumed that there can be more
than one transction per voucher id.
Anyway, for this type of postings it is recommendable, to post:
1) CREATE TABLE statements for the tables.
2) INSERT statements with sample data.
3) The desired output given the sample data.
4) A short narrative, describing the business problem.
1 and 2 makes it easy to copy and paste into a query tool to develop
a query, that can be verified against 3.
In a separate post, you posted some sample data, but they are not
related to the desired output above, and furthermore, I cannot see
that there are any matching voucher ids.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
POSTING THE COMPLETE CODE WITH INSERT VALUES.
----LEDGERS TABLE
CREATE TABLE [Ledgers] (
[LedgerID] [int] NOT NULL ,
[LedgerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----VOCUHERS TABLE
CREATE TABLE [Vouchers] (
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----TRANSACTIONS TABLE
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT
[DF_Transactions_ByTo]
DEFAULT ('By'),
[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
--LEDGERS INSERT STATEMENTS
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(1, 'Cash')
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(12, 'Bank-1')
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(232, 'Corporation Tax A/C')
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(25, 'Sale Account')
--VOUCHERS INSERT STATEMENTS
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB', 1, 2, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('04629623-9DFE-4A57-92EC-447F6D00115A', 1, 4, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('8B0AD48E-790C-459A-AF20-4881EDAFEFAA', 1, 5, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('91BC754F-1DB8-400E-9CED-49949112B482', 1, 6, 2001-09-03)
---TRANSACTIONS INSERT STATEMENTS
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('CBBD8EBE-55BA-4039-9C3B-0537FE348470','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',1,0,600)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('1736F5A0-EBE3-4494-B075-52216E73E857','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('959BF813-DDF9-4193-A030-974F0118E126','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',12,0,2400)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('66D503DA-88DB-4EE0-9968-974F0118EF07','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2900,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7D2A8B1C-EA71-4A23-8B33-A2EDB60EC29E','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,50,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7ED334F5-AAE6-4185-B5C6-AA7789209107','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',232,48,0)
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('f16c50c0-8b43-45bb-a3f6-974f0118cce6',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'B', 12, 0.0, 4000.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('04a85760-e113-4fcc-8aec-974f0118cdf6',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'T', 1, 4000.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('04f847c3-df4e-4983-ab88-974f0118d8ba',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'B', 1, 0.0, 200.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('01c1387d-8840-4543-9643-974f0118e93e',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'T', 12, 200.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('7ec79e34-01d1-4b61-8347-974f0118eb46',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'B', 1, 0.0, 200.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('233bfc0d-b100-4535-839b-974f0118f799',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'T', 12, 200.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('b8d47ca5-0759-4526-afcd-974f0118dc26',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'B', 1, 0.0, 2345.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('8dc97aa8-980a-4a90-828c-974f0118dfab',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'T', 12, 2345.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('61ced37b-4028-428e-8d15-974f0118e08c',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'B', 1, 0.0, 2345.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('52c11be4-e3b8-499b-ab84-974f0118e464',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'T', 12, 2345.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('078e881c-2c85-4650-8769-974f0118e6d8',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'T', 12, 345.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('fe7def3d-ac40-4c4e-8487-974f0118e89a',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'B', 1, 0.0, 345.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('fe5a9084-cbf2-4820-86b5-974f0118d34a',
'91bc754f-1db8-400e-9ced-49949112b482', 'B', 1, 0.0, 400.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('6394af15-5291-41a7-a554-974f0118de2f',
'91bc754f-1db8-400e-9ced-49949112b482', 'T', 12, 400.0, 0.0)
GO
*** Sent via Developersdex http://www.developersdex.com ***
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.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada www.pinpub.com
"Bill Bob" <no****@devdex.com> wrote in message
news:YH****************@news.uswest.net...
POSTING THE COMPLETE CODE WITH INSERT VALUES.
----LEDGERS TABLE
CREATE TABLE [Ledgers] (
[LedgerID] [int] NOT NULL ,
[LedgerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----VOCUHERS TABLE
CREATE TABLE [Vouchers] (
[VoucherID] [uniqueidentifier] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarration] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----TRANSACTIONS TABLE
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifier] NOT NULL ,
[VoucherID] [uniqueidentifier] NOT NULL ,
[ByTo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT
[DF_Transactions_ByTo]
DEFAULT ('By'),
[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
--LEDGERS INSERT STATEMENTS
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(1, 'Cash')
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(12, 'Bank-1')
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(232, 'Corporation Tax A/C')
INSERT INTO [Ledgers]([LedgerID], [LedgerName])
VALUES(25, 'Sale Account')
--VOUCHERS INSERT STATEMENTS
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB', 1, 2, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('04629623-9DFE-4A57-92EC-447F6D00115A', 1, 4, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('8B0AD48E-790C-459A-AF20-4881EDAFEFAA', 1, 5, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('91BC754F-1DB8-400E-9CED-49949112B482', 1, 6, 2001-09-03)
---TRANSACTIONS INSERT STATEMENTS
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('CBBD8EBE-55BA-4039-9C3B-0537FE348470','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',1,0,600)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('1736F5A0-EBE3-4494-B075-52216E73E857','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('959BF813-DDF9-4193-A030-974F0118E126','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',12,0,2400)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('66D503DA-88DB-4EE0-9968-974F0118EF07','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2900,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7D2A8B1C-EA71-4A23-8B33-A2EDB60EC29E','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,50,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7ED334F5-AAE6-4185-B5C6-AA7789209107','2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',232,48,0)
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('f16c50c0-8b43-45bb-a3f6-974f0118cce6',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'B', 12, 0.0, 4000.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('04a85760-e113-4fcc-8aec-974f0118cdf6',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'T', 1, 4000.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('04f847c3-df4e-4983-ab88-974f0118d8ba',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'B', 1, 0.0, 200.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('01c1387d-8840-4543-9643-974f0118e93e',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'T', 12, 200.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('7ec79e34-01d1-4b61-8347-974f0118eb46',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'B', 1, 0.0, 200.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('233bfc0d-b100-4535-839b-974f0118f799',
'04629623-9dfe-4a57-92ec-447f6d00115a', 'T', 12, 200.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('b8d47ca5-0759-4526-afcd-974f0118dc26',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'B', 1, 0.0, 2345.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('8dc97aa8-980a-4a90-828c-974f0118dfab',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'T', 12, 2345.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('61ced37b-4028-428e-8d15-974f0118e08c',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'B', 1, 0.0, 2345.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('52c11be4-e3b8-499b-ab84-974f0118e464',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'T', 12, 2345.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('078e881c-2c85-4650-8769-974f0118e6d8',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'T', 12, 345.0, 0.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('fe7def3d-ac40-4c4e-8487-974f0118e89a',
'8b0ad48e-790c-459a-af20-4881edafefaa', 'B', 1, 0.0, 345.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('fe5a9084-cbf2-4820-86b5-974f0118d34a',
'91bc754f-1db8-400e-9ced-49949112b482', 'B', 1, 0.0, 400.0)
GO
INSERT INTO [dbo].[Transactions] ([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit]) VALUES
('6394af15-5291-41a7-a554-974f0118de2f',
'91bc754f-1db8-400e-9ced-49949112b482', 'T', 12, 400.0, 0.0)
GO
*** Sent via Developersdex http://www.developersdex.com ***
Bill Bob (no****@devdex.com) writes: POSTING THE COMPLETE CODE WITH INSERT VALUES.
Thanks for the code and tables! I was able to see that the transactions
are fairly standard accounting transactions where for each voucher
the sum of the credit and the debit amounts are the same. However, I
still do not understand the requirements for the required output in
you original posting.
Also, the sample data you posted now, does not appear to be equal with
the data in your original post, so I don't know what the desired output
is for the data you posted now.
So you can explain the business requirements and posted the desired output,
that would make it easier to produce a query.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
Sorry, I had changed the GUID Field so that the output would be easier
to understand. I am Rectifying and posting again
VoucherID VoucherNo VoucherDate
LedgerName Amount
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB 2 2001-09-03 22:00:00.000
Bank-1 2400.0000
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB 2 2001-09-03 22:00:00.000
Cash 600.0000
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB 2 2001-09-03 22:00:00.000
CORPORATION TAX A/C .0000
04629623-9DFE-4A57-92EC-447F6D00115A 4 2001-09-03 22:00:00.000
Bank-1 4000.0000
04629623-9DFE-4A57-92EC-447F6D00115A 4 2001-09-03 22:00:00.000
Cash 400.0000
8B0AD48E-790C-459A-AF20-4881EDAFEFAA 5 2001-09-03 22:00:00.000
Bank-1 .0000
8B0AD48E-790C-459A-AF20-4881EDAFEFAA 5 2001-09-03 22:00:00.000
Cash 5035.0000
91BC754F-1DB8-400E-9CED-49949112B482 6 2001-09-03 22:00:00.000
Bank-1 .0000
91BC754F-1DB8-400E-9CED-49949112B482 6 2001-09-03 22:00:00.000
Cash 400.0000
The required output is the first row created by the data-entry program.
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.
2E6ADB3F-A0DA-4660-A8C2-3F0EFA2E06AB 2 2001-09-03 22:00:00.000
Bank-1 3000.0000
04629623-9DFE-4A57-92EC-447F6D00115A 4 2001-09-03 22:00:00.000
Bank-1 4400.0000
8B0AD48E-790C-459A-AF20-4881EDAFEFAA 5 2001-09-03 22:00:00.000
Bank-1 5035.0000
91BC754F-1DB8-400E-9CED-49949112B482 6 2001-09-03 22:00:00.000
Bank-1 400.0000
*** Sent via Developersdex http://www.developersdex.com ***
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****@sommarskog.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****@sommarskog.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
>> 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.
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(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, es****@sommarskog.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: leegold2 |
last post by:
Let's I do a mysql query and then I do a,
for( $i = 1; $row = mysql_fetch_array($result); $i++ ) {...}
and it gives me this:
PageID Title URL Description
1 lee's ...
|
by: Roman |
last post by:
I've been trying this one for 2-3 hours and can't figure it out. I'de
appreciate any help or pointers in the right direction. Thanks.
Query
I need the query to return me all the lottery names...
|
by: Sy Borg |
last post by:
Hello:
We are designing two multi-user client server applications that
performs large number of transactions on database servers. On an
average Application A has a 50% mix of select and...
|
by: greg |
last post by:
Hi
Basically I call a page that does a very long op
(like very long database query)
and display results (possible on another page)
But I need to display a progress bar
for user to see some...
|
by: Arial |
last post by:
My SQL string is kind of wierd one.
In my application, I need to select things from an
unknown name table. But I know the table name before the
SQL command is executed.
For instance,
Dim...
|
by: Dan |
last post by:
I am trying to create a query (in either sql or the design view) to
determine which two (or more I suppose if it's not too complicated)
baseball players were teammates the longest. The database...
|
by: DFS |
last post by:
The following data set is building inspection visits. It consists of
multiple visits (2+) made to the same building on the same day.
I want to get a list of visits made to the same building on...
|
by: steven.fafel |
last post by:
I am running 2 versions of a correlated subquery. The two version
differ slightly in design but differ tremendously in performance....if
anyone can answer this, you would be awesome.
The "bad"...
|
by: jconstan |
last post by:
hey all,
i am having a problem with a certain query which pulls records from two tables. one table contains info entered into a main form, and the other has information entered into the subform. ...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
|
by: Arjunsri |
last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
|
by: Rahul1995seven |
last post by:
Introduction:
In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
|
by: Johno34 |
last post by:
I have this click event on my form. It speaks to a Datasheet Subform
Private Sub Command260_Click()
Dim r As DAO.Recordset
Set r = Form_frmABCD.Form.RecordsetClone
r.MoveFirst
Do
If...
|
by: ezappsrUS |
last post by:
Hi,
I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
| |
