Posted by Tom Moreau on 02/19/06 15:31

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" <nospam@devdex.com> wrote in message
news:YHUJf.91$Vr6.16683@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 ***

[Back to original message]