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 *** 12  2748 
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.u swest.net...
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 ***
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.us west.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****@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
POSTING THE COMPLETE CODE WITH INSERT VALUES.
----LEDGERS TABLE
CREATE TABLE [Ledgers] (
[LedgerID] [int] NOT NULL ,
[LedgerName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----VOCUHERS TABLE
CREATE TABLE [Vouchers] (
[VoucherID] [uniqueidentifie r] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarratio n] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----TRANSACTIONS TABLE
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifie r] NOT NULL ,
[VoucherID] [uniqueidentifie r] NOT NULL ,
[ByTo] [char] (1) COLLATE SQL_Latin1_Gene ral_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('2E6ADB3 F-A0DA-4660-A8C2-3F0EFA2E06AB', 1, 2, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('0462962 3-9DFE-4A57-92EC-447F6D00115A', 1, 4, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('8B0AD48 E-790C-459A-AF20-4881EDAFEFAA', 1, 5, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('91BC754 F-1DB8-400E-9CED-49949112B482', 1, 6, 2001-09-03)
---TRANSACTIONS INSERT STATEMENTS
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('CBBD8EB E-55BA-4039-9C3B-0537FE348470',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B ',1,0,600)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('1736F5A 0-EBE3-4494-B075-52216E73E857',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T ',1,2,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('959BF81 3-DDF9-4193-A030-974F0118E126',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B ',12,0,2400)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('66D503D A-88DB-4EE0-9968-974F0118EF07',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T ',1,2900,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7D2A8B1 C-EA71-4A23-8B33-A2EDB60EC29E',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T ',1,50,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7ED334F 5-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.u swest.net...
POSTING THE COMPLETE CODE WITH INSERT VALUES.
----LEDGERS TABLE
CREATE TABLE [Ledgers] (
[LedgerID] [int] NOT NULL ,
[LedgerName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL
CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----VOCUHERS TABLE
CREATE TABLE [Vouchers] (
[VoucherID] [uniqueidentifie r] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
[VoucherNarratio n] [varchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----TRANSACTIONS TABLE
CREATE TABLE [Transactions] (
[TransactionID] [uniqueidentifie r] NOT NULL ,
[VoucherID] [uniqueidentifie r] NOT NULL ,
[ByTo] [char] (1) COLLATE SQL_Latin1_Gene ral_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('2E6ADB3 F-A0DA-4660-A8C2-3F0EFA2E06AB', 1, 2, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('0462962 3-9DFE-4A57-92EC-447F6D00115A', 1, 4, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('8B0AD48 E-790C-459A-AF20-4881EDAFEFAA', 1, 5, 2001-09-03)
INSERT INTO [Vouchers]
([VoucherID], [VoucherTypeID], [VoucherNo], [VoucherDate])
VALUES('91BC754 F-1DB8-400E-9CED-49949112B482', 1, 6, 2001-09-03)
---TRANSACTIONS INSERT STATEMENTS
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('CBBD8EB E-55BA-4039-9C3B-0537FE348470',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B ',1,0,600)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('1736F5A 0-EBE3-4494-B075-52216E73E857',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T ',1,2,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('959BF81 3-DDF9-4193-A030-974F0118E126',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B ',12,0,2400)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('66D503D A-88DB-4EE0-9968-974F0118EF07',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T ',1,2900,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7D2A8B1 C-EA71-4A23-8B33-A2EDB60EC29E',' 2E6ADB3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T ',1,50,0)
INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7ED334F 5-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****@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
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 *** 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 gogle1.com This is lee's website.
1 lee's gogle2.com This is lee's website.
2 Jon's yaho1.com This is Jon's website.
2 Jon's yaho2.com This is Jon's website.
|
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 and results that
have the latest date in the database for that particular game and for
the state . So the return data from the data below data would be:
Result:
--------------------------
|
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 update/insert/delete
statements and application B has 80-20 mix of select and
update/insert/delete statements. Being able to scale the databases as
needed so the performance is unaffected, is one of our critical
requirements. We've been investigating...
|
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 progress (dummy but at least something)
no frames
|
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 varname as string = 'one of my variable. It's part of
the table name.
Dim t1 as String = varname+ "0000"
| | |
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 includes the
following fields: YearId, PlayerId, and teamId.
I have been unable to write query that can caluclate the number of
years players would have played together on a team. (Technical note:
some players have records for more than one team...
|
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 the same day,
but by different employees, and for different visit codes (eg records 5-6,
or 9-11)
Here's the table
=====================================
|
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" query attempts to build a result set using a correlated
subquery. The part causing the error is that the correlated subquery
is part of a derived table (joining 3 tables). Trying to run the query
takes a long time and the more records in the...
|
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.
what i want the query to pull is all the records from the main table, and only the most recent record (IE latest date inputed into "date" field) from the sub table.
however what ends up happening is the query will pull all the records from...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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.
| |
