Hi Everyone
I would appreciate your help on this.
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date)
The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]
from Tab2 t1
LEFT OUTER JOIN tab1 t2
ON t1.entity_id = t2.entity_id AND
t2.effective_date BETWEEN t1.effective_date AND ISNULL(t1.ineffective_date, '2900-12-31')
where t1.entity_id = 19
I used the Between to catch any date that might not not match up but it not working.
would a pivot/unpivot be a better option?
CREATE TABLE [dbo].[Tab2](
[entity_id] [int] NOT NULL,
[sequence] [tinyint] NOT NULL,
[effective_date] [datetime2](7) NOT NULL,
[s_g_description] [varchar](255) NULL,
[s_code_description] [varchar](255) NULL,
[s_sec_code] [char](1) NOT NULL,
[s_sect_description] [varchar](255) NULL,
[s_code] [char](6) NOT NULL,
[sic_group_code] [smallint] NOT NULL,
[ineffective_date] [datetime2](7) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Tab2] ([entity_id], [sequence], [effective_date], [s_g_description], [s_code_description], [s_sec_code], [s_sect_description], [s_code], [sic_group_code], [ineffective_date]) VALUES (19, 1, CAST(0x0700A775F696D3380B AS DateTime2), N'UK2007', N'Other service activities','S','activities1' ,N'80808 ', 3, NULL)
/****** Object: Table [dbo].[Table1] Script Date: 07/31/2014 14:59:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tab1](
[entity_id] [int] NOT NULL,
[effective_date] [datetime2](7) NOT NULL,
[company_name] [nvarchar](500) NULL,
[last_accounts_date] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Tab1] ([entity_id], [effective_date], [company_name], [last_accounts_date]) VALUES (19, CAST(0x0700A775F69643370B AS DateTime2), N'Spaces (Hotel2) Limited', CAST(0xE8340B00 AS Date))
INSERT [dbo].[Tab1] ([entity_id], [effective_date], [company_name], [last_accounts_date]) VALUES (19, CAST(0x070047AD841DBD370B AS DateTime2), N'Spaces (Hotel2) Limited', CAST(0x56360B00 AS Date))
INSERT [dbo].[Tab1] ([entity_id], [effective_date], [company_name], [last_accounts_date]) VALUES (19, CAST(0x0780910D46096C380B AS DateTime2), N'Spaces (Hotel2) Limited', CAST(0xC3370B00 AS Date))
/****** Object: Table [dbo].[results1] Script Date: 07/31/2014 14:59:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Wantedresults](
[entity_id] [int] NOT NULL,
[effective_date] [datetime2](7) NOT NULL,
[company_name] [nvarchar](500) NULL,
[last_accounts_date] [date] NULL,
[s_code] [char](6) NULL,
[s_code_description] [varchar](255) NULL,
[ineffective_date] [datetime2](7) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Wantedresults] ([entity_id], [effective_date], [company_name], [last_accounts_date], [s_code], [s_code_description], [ineffective_date]) VALUES (19, CAST(0x0700A775F69643370B AS DateTime2), N'Spaces (Hotel2) Limited', CAST(0xE8340B00 AS Date), '80808 ', 'Other service activities', NULL)
INSERT [dbo].[Wantedresults] ([entity_id], [effective_date], [company_name], [last_accounts_date], [s_code], [s_code_description], [ineffective_date]) VALUES (19, CAST(0x070047AD841DBD370B AS DateTime2), N'Spaces (Hotel2) Limited', CAST(0x56360B00 AS Date), '80808 ', 'Other service activities', NULL)
INSERT [dbo].[Wantedresults] ([entity_id], [effective_date], [company_name], [last_accounts_date], [s_code], [s_code_description], [ineffective_date]) VALUES (19, CAST(0x0780910D46096C380B AS DateTime2), N'Spaces (Hotel2) Limited', CAST(0xC3370B00 AS Date), '80808 ', 'Other service activities', NULL)
Any help would be greatly appreciated.
Unless I really got it wrong, the sample data you provided should not return any records as the Tab2 effective_date is greater than the values in Tab1 effective_date, hence nothing will fall between Tab2's effective and ineffective dates.
😎
You might want to use apply instead of the join, something along these lines
;WITH TAB2_BASE AS
(
SELECT
T2.entity_id
,T2.sequence
,T2.effective_date
,T2.s_g_description
,T2.s_code_description
,T2.s_sec_code
,T2.s_sect_description
,T2.s_code
,T2.sic_group_code
,ISNULL(T2.ineffective_date,'2900-12-31 00:00:00.000') AS ineffective_date
FROM dbo.Tab2 T2
)
SELECT
*
FROM TAB2_BASE T2
CROSS APPLY dbo.Tab1 T1
WHERE T2.entity_id = T1.entity_id
AND T1.effective_date BETWEEN T2.effective_date AND T2.ineffective_date
August 13, 2014 at 11:17 am
I didn't delve deeply into the data, but I generally avoid using BETWEEN when working with datetimes. For example a query with DateTimeColumn Between '2000-01-01' and '2000-12-31' does not return all the rows for the year 2000 because '2001-12-31' is really '2000-12-31 00:00:00.000'. Whereas DateTimeColumn >= '2000-01-01' and DateTimeColumn < '2001-01-01' DOES return all the rows for the year 2000. So I'd write the query:
SELECT
t2.[entity_id],
t2.[effective_date],
[company_name],
[last_accounts_date],
[s_code],
[s_code_description],
[ineffective_date]
FROM
Tab2 t2
LEFT OUTER JOIN tab1 t1
ON t1.entity_id = t2.entity_id AND
t1.effective_date >= t2.effective_date AND
t1.effective_date < ISNULL(t2.ineffective_date, '2900-12-31')
WHERE
t1.entity_id = 19
Eirikur is correct though, the tab2 effective_date is later than any of the effective dates in tab1 so you won't get any data returned from your query or mine.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply