July 31, 2014 at 9:21 am
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 try the SQL script below 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 greatly appreciated.
July 31, 2014 at 10:08 am
Awesome job posting consumable ddl and sample data!!!! I wish everyone would create posts like that. It really makes this easy to work with.
I am not quite sure what you are looking for but this matches your posted desired output.
select t.entity_id
, t.effective_date
, t.company_name
, t.last_accounts_date
, t2.s_code
, t2.s_code_description
, t2.ineffective_date
from Tab1 t
join tab2 t2 on t2.entity_id = t.entity_id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2014 at 12:23 pm
Hi Sean,
I try the sql script but it was producing 6 rows of data instead of 3, please see the attached picture.
thanks for helping out.
July 31, 2014 at 12:30 pm
azdeji (7/31/2014)
Hi Sean,I try the sql script but it was producing 6 rows of data instead of 3, please see the attached picture.
thanks for helping out.
Maybe you have more data than what you posted. Perhaps you ran your insert statements twice? It returns the EXACT same rows as your desired output table when starting with an empty table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply