|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-12 : 16:56:04
|
Ken writes "Using the script below to set up the sample table/data, this query is supposed to join the transaction record with the client record whos last_update_date is closest to the transactions txn_date, with out going over. It almost works... please see what I am doing wrong.
SELECT t.AcctNo AS TxnAcct
, t.TXN_DATE
, c.ACCOUNT AS ClientAcct
, c.LAST_DATE
FROM Transaction_2001 t
LEFT OUTER JOIN Client c
ON t.AcctNo = c.ACCOUNT
AND t.TXN_DATE >
(SELECT COALESCE((Select MAX(c1.Last_Date)
FROM client c1 WHERE c1.Last_Date < COALESCE (c.Last_Date, '1/1/9999')
AND c1.ACCOUNT = t.AcctNo), '1/1/1900'))
AND t.TXN_DATE <
(SELECT COALESCE((select MIN(c2.Last_Date)
FROM client c2 WHERE c2.Last_Date > COALESCE (c.Last_Date, '1/1/1900')
AND c2.ACCOUNT = t.AcctNo), '1/1/9999'))
-- Create the Client table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Client]
CREATE TABLE [dbo].[Client] ( [ACCOUNT] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATE] [datetime] NOT NULL , [NAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PREF_VENDOR] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PROD_TOLLERANCE] [decimal](5, 2) NULL , [LAST_DATE] [datetime] NULL , [AGREEMT_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED ( [ACCOUNT], [STATUS], [DATE] ) WITH FILLFACTOR = 90 ON [PRIMARY]
exec sp_addextendedproperty N'MS_Description', N'Clients Account Number', N'user', N'dbo', N'table', N'Client', N'column', N'ACCOUNT' exec sp_addextendedproperty N'MS_Description', N'Denotes Active Record (A) or History Record (H)', N'user', N'dbo', N'table', N'Client', N'column', N'STATUS' exec sp_addextendedproperty N'MS_Description', N'Date Active Record (A) or History Record (H) was added', N'user', N'dbo', N'table', N'Client', N'column', N'DATE' exec sp_addextendedproperty N'MS_Description', N'Denotes Client Record Last Update Date', N'user', N'dbo', N'table', N'Client', N'column', N'LAST_DATE'
-- Insert some sample client data
INSERT [dbo].[Client] Values('ABC', 'A', '05/25/1994', 'JOE SCHMOE', 'Y', '.00', '04/18/2000', 'Y') INSERT [dbo].[Client] Values('ABC', 'H', '05/25/1994', 'JOE SCHMOE', 'N', '.00', NULL, 'Y') INSERT [dbo].[Client] Values('ABC', 'H', '05/13/1999', 'JOE SCHMOE', 'N', '.00', '05/25/1994', 'Y') INSERT [dbo].[Client] Values('ABC', 'H', '04/18/2000', 'JOE SCHMOE', 'N', '.00', '05/13/1999', 'Y')
INSERT [dbo].[Client] Values('DEF', 'A', '05/25/1999', 'JOHN PUBLIC', 'Y', '.00', '06/11/1999', 'Y') INSERT [dbo].[Client] Values('DEF', 'H', '06/11/1999', 'JOHN PUBLIC', 'Y', '.00', NULL, 'Y')
INSERT [dbo].[Client] Values('GHI', 'A', '05/25/1994', 'KAREN SMITH', 'Y', 7.50, '04/18/2000', 'Y') INSERT [dbo].[Client] Values('GHI', 'H', '05/13/1999', 'KAREN SMITH', 'N', 7.50, NULL, 'Y') INSERT [dbo].[Client] Values('GHI', 'H', '04/18/2000', 'KAREN SMITH', 'N', 7.50, '05/13/1999', 'Y')
INSERT [dbo].[Client] Values('JKL', 'A', '05/25/1994', 'VAN ORTON', 'Y', 5.00, '05/13/1999', 'Y') INSERT [dbo].[Client] Values('JKL', 'H', '05/13/1999', 'VAN ORTON', 'Y', 5.00, NULL, 'Y')
-- Create the Transaction table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Transaction_2001]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Transaction_2001]
CREATE TABLE [dbo].[Transaction_2001] ( [TXN_DATE] [datetime] NULL , [SEQUENCE_NO] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FILLER] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AcctNo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
|
|
Need insight to behavior of this query