Microsoft SQL Server forum - Tough Sql Query

Author

Tough Sql Query

Bill Bob

2006-02-18, 3:23 am

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

Vouchers
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
& #91;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] & #91;uniqueidentifier
] NOT NULL ,
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT & #91;PK_Transactions]
PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT & #91;FK_Transactions_
Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT & #91;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.droptable.com ***

Tom Moreau

2006-02-18, 7:23 am

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" <nospam@devdex.com> wrote in message
news:MTzJf.91$6M5.14383@news.uswest.net...

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

Vouchers
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
& #91;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] & #91;uniqueidentifier
] NOT NULL ,
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[ByTo] [char] (1)
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT & #91;PK_Transactions]
PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT & #91;FK_Transactions_
Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT & #91;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.droptable.com ***

Bill Bob

2006-02-18, 1:23 pm

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.droptable.com ***

Tom Moreau

2006-02-18, 1:23 pm

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" <nospam@devdex.com> wrote in message
news:8pJJf.16$Vr6.5889@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.droptable.com ***

Erland Sommarskog

2006-02-18, 8:23 pm

Bill Bob (nospam@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bill Bob

2006-02-19, 3:23 am

POSTING THE COMPLETE CODE WITH INSERT VALUES.

----LEDGERS TABLE

CREATE TABLE [Ledgers] (
[LedgerID] [int] NOT NULL ,
[LedgerName] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NOT
NULL

CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

----VOCUHERS TABLE

CREATE TABLE [Vouchers] (
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
& #91;VoucherNarration
] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

----TRANSACTIONS TABLE

CREATE TABLE [Transactions] (
[TransactionID] & #91;uniqueidentifier
] NOT NULL ,
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[ByTo] [char] (1) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL CONSTRAINT
& #91;DF_Transactions_
ByTo]

DEFAULT ('By'),
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT & #91;PK_Transactions]
PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT & #91;FK_Transactions_
Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT & #91;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','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',1,0
,600)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('1736F5A0-EBE3-4494-B075- 52216E73E857','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2
,0)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('959BF813-DDF9-4193-A030- 974F0118E126','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',12,
0,2400)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('66D503DA-88DB-4EE0-9968- 974F0118EF07','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2
900,0)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7D2A8B1C-EA71-4A23-8B33- A2EDB60EC29E','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,5
0,0)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7ED334F5-AAE6-4185-B5C6- AA7789209107','2E6AD
B3F-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.droptable.com ***

Tom Moreau

2006-02-19, 9:23 am

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_C
P1_CI_AS NOT
NULL

CONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED
(
[LedgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

----VOCUHERS TABLE

CREATE TABLE [Vouchers] (
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[VoucherTypeID] [int] NOT NULL ,
[VoucherNo] [int] NULL ,
[VoucherDate] [datetime] NOT NULL ,
& #91;VoucherNarration
] [varchar] (255) COLLATE SQL_Latin1_General_C
P1_CI_AS
NULL ,
CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED
(
[VoucherID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

----TRANSACTIONS TABLE

CREATE TABLE [Transactions] (
[TransactionID] & #91;uniqueidentifier
] NOT NULL ,
[VoucherID] & #91;uniqueidentifier
] NOT NULL ,
[ByTo] [char] (1) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL CONSTRAINT
& #91;DF_Transactions_
ByTo]

DEFAULT ('By'),
[LedgerID] [int] NOT NULL ,
[Credit] [money] NOT NULL ,
[Debit] [money] NOT NULL ,
CONSTRAINT & #91;PK_Transactions]
PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY] ,
CONSTRAINT & #91;FK_Transactions_
Ledgers] FOREIGN KEY
(
[LedgerID]
) REFERENCES [Ledgers] (
[LedgerID]
),
CONSTRAINT & #91;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','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',1,0
,600)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('1736F5A0-EBE3-4494-B075- 52216E73E857','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2
,0)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('959BF813-DDF9-4193-A030- 974F0118E126','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','B',12,
0,2400)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('66D503DA-88DB-4EE0-9968- 974F0118EF07','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,2
900,0)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7D2A8B1C-EA71-4A23-8B33- A2EDB60EC29E','2E6AD
B3F-A0DA-4660-A8C2-3
F0EFA2E06AB','T',1,5
0,0)

INSERT INTO [Transactions]([TransactionID], [VoucherID], [ByTo],
[LedgerID], [Credit], [Debit])
VALUES('7ED334F5-AAE6-4185-B5C6- AA7789209107','2E6AD
B3F-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.droptable.com ***

Erland Sommarskog

2006-02-19, 9:23 am

Bill Bob (nospam@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Erland Sommarskog

2006-02-19, 9:23 am

Tom Moreau (tom@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bill Bob

2006-02-19, 9:23 am

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.droptable.com ***

Tom Moreau

2006-02-19, 9:23 am

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" <esquel@sommarskog.se> wrote in message
news:Xns976FA19DB42F
6Yazorman@127.0.0.1...
Tom Moreau (tom@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

--CELKO--

2006-02-19, 11:23 am

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

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

Erland Sommarskog

2006-02-19, 11:23 am

Bill Bob (nospam@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(uniqueidenti
fier, 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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links