Join needed on three tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join needed on three tables

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-10-02 : 11:12:56

I have three tables relating to files and transactions clients upload to our server. In the Account table, Clients will have either a 'B' account or a 'I' account or in some cases either. I need to get counts of all Transactions made using the 'B' account, and all transactions made using the 'I' account. If the Account is 'I' i also need the count in the transset table. The Transet table contains the submittedDate, and the query needs to return the counts for the previous month .. ie today would return all counts for September 2010

Code and hoped for result below

CREATE TABLE [dbo].[Account](
[Licence] [varchar](6) NOT NULL,
[OAccountID] [uniqueidentifier] NOT NULL,
[Country] [varchar](32) NULL,

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TranSet](
[LedgerKey] [uniqueidentifier] NOT NULL,
[Licence] [varchar](6) NOT NULL,
[OAccountID] [uniqueidentifier] NOT NULL,
[SubmittedDate] [smalldatetime] NULL,
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Transactions](
[LedgerKey] [uniqueidentifier] NOT NULL,
[TransactionID] [uniqueidentifier] NOT NULL,
[DestSortCode] [varchar](32) NOT NULL,
[DestAccountNumber] [varchar](32) NOT NULL,
[DestAccountName] [varchar](32) NULL,
[DestBankRef] [varchar](32) NULL,
[TransValue] [money] NOT NULL,
) ON [PRIMARY]

GO

INSERT INTO Account (Licence, OAccountID, Country ) VALUES ('100000','42ded28c-6890-462e-8ee5-240af36e759b', 'I')
INSERT INTO Account (Licence, OAccountID, Country ) VALUES ('100000','dec33fe5-59ae-445c-83c1-db49bd528b0f', 'B')
INSERT INTO Account (Licence, OAccountID, Country ) VALUES ('100001','33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62', 'B')

-- BACS
INSERT INTO TranSet ([LedgerKey],[Licence],[OAccountID],[SubmittedDate]) VALUES
('6A5D3257-ABF9-49D1-93E6-E1B5563CF31C', '100000' , 'dec33fe5-59ae-445c-83c1-db49bd528b0f' , CONVERT(datetime, '25/08/2010', 103))
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('6A5D3257-ABF9-49D1-93E6-E1B5563CF31C', '3BFB7B38-8F82-4FC9-BE1D-1806618562EC', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('6A5D3257-ABF9-49D1-93E6-E1B5563CF31C', 'CA1B1961-5071-442C-B259-F71050162B40', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
-- BACS
INSERT INTO TranSet ([LedgerKey],[Licence],[OAccountID],[SubmittedDate]) VALUES
('986EFF83-26B1-414E-B95B-D4922656D12B', '100000' , 'dec33fe5-59ae-445c-83c1-db49bd528b0f' , CONVERT(datetime, '01/09/2010', 103))
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('986EFF83-26B1-414E-B95B-D4922656D12B', '755197F4-353C-47B7-B446-8333518804DC', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('986EFF83-26B1-414E-B95B-D4922656D12B', 'CD0355AA-A6BC-4C36-AE05-4AC9C6B59D04', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('986EFF83-26B1-414E-B95B-D4922656D12B', '4711F0A2-6BE1-4C20-AED8-BB20F5F3C266', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('986EFF83-26B1-414E-B95B-D4922656D12B', '82941AF1-67E8-42D4-B88D-BC54DABAA623', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
-- IRISH
INSERT INTO TranSet ([LedgerKey],[Licence],[OAccountID],[SubmittedDate]) VALUES
('4E955E2B-94E1-42E3-9AB7-B2256CBFD633', '100000' , '42ded28c-6890-462e-8ee5-240af36e759b' , CONVERT(datetime, '27/09/2010', 103))
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('4E955E2B-94E1-42E3-9AB7-B2256CBFD633', 'D984D2C7-DE3E-43A3-A5FD-7A4717420525', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('4E955E2B-94E1-42E3-9AB7-B2256CBFD633', '5EC3AD63-DE75-498E-94DE-A99A75715A88', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
--BACS
INSERT INTO TranSet ([LedgerKey],[Licence],[OAccountID],[SubmittedDate]) VALUES
('53E59CBD-9DA8-4454-9FFE-CD705A7A4489', '100001' , '33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62' , CONVERT(datetime, '20/09/2010', 103))
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('53E59CBD-9DA8-4454-9FFE-CD705A7A4489', '978995A1-CB24-4405-9498-796D4E4D3A5D', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
--BACS
INSERT INTO TranSet ([LedgerKey],[Licence],[OAccountID],[SubmittedDate]) VALUES
('D198F9C3-94BB-44BA-B80D-F54C44378967', '100001' , '33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62' , CONVERT(datetime, '20/09/2010', 103))
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('D198F9C3-94BB-44BA-B80D-F54C44378967', 'CA66CFA2-1873-4877-B023-6AE630294252', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')
INSERT INTO Transactions ([LedgerKey], [TransactionID], [DestSortCode], [DestAccountNumber], [DestAccountName], [DestBankRef], [TransValue]) VALUES
('D198F9C3-94BB-44BA-B80D-F54C44378967', 'CA1BB450-AE04-4738-ACC9-F81FF3AE5C66', '11-11-11', '22222222' , 'TEST' , 'TEST', '0.01')

Required Result

Licence B ICount IFiles
100000 4 2 1
100001 2 0 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources