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  2716 
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
