Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Tough Sql QueryI 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 ***
Post Follow-up to this message
Re: Tough Sql QueryThe 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 ***
Post Follow-up to this message Re: Tough Sql QueryVoucher 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 ***
Post Follow-up to this message Re: Tough Sql QueryWould 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 ***
Post Follow-up to this message Re: Tough Sql QueryBill 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
Post Follow-up to this message Re: Tough Sql QueryPOSTING 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 CONSTRAI
NT
& #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], [By
To],
[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], [By
To],
[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], [By
To],
[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], [By
To],
[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], [By
To],
[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], [By
To],
[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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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], [VoucherI
D], [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 ***
Post Follow-up to this message Re: Tough Sql QueryWhat 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 CONSTRAI NT & #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], [By To], [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], [By To], [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], [By To], [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], [By To], [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], [By To], [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], [By To], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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], [VoucherI D], [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 ***
Post Follow-up to this message Re: Tough Sql QueryBill 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
Post Follow-up to this message Re: Tough Sql QueryTom 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
Post Follow-up to this message Re: Tough Sql QuerySorry, 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 ***
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
