How to join 2 table based on date

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)

How to join 2 table based on date

Author

Topic

hariyadi
Starting Member

3 Posts

Posted - 2014-07-16 : 23:41:16

Hi all,
I have 2 tables, transaction and master:

Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....

Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....

I want to create query the output should be:

EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A

I create the a query:

SELECT Distinct tr.EmpID, tr.TransDate,
Groups=MAX(Groups) Over (Partition By tr.EmpID, tr.TransDate, ms.Groups)
FROM dbo.Transactions tr INNER JOIN dbo.[Master] ms
ON tr.EmpID=ms.EmpID AND tr.TransDate>= ms.EffectiveDateFr
ORDER BY tr.EmpID

the output:
EmpID TransDate Groups
00001 2014-01-01 00:00:00.000 A
00001 2014-01-02 00:00:00.000 A
00001 2014-01-03 00:00:00.000 A
00001 2014-01-04 00:00:00.000 A
00001 2014-01-05 00:00:00.000 A
00001 2014-01-05 00:00:00.000 B
00001 2014-01-06 00:00:00.000 A
00001 2014-01-06 00:00:00.000 B
00001 2014-01-15 00:00:00.000 A
00001 2014-01-15 00:00:00.000 B
00001 2014-01-15 00:00:00.000 C
00001 2014-02-01 00:00:00.000 A
00001 2014-02-01 00:00:00.000 B
00001 2014-02-01 00:00:00.000 C
00001 2014-02-02 00:00:00.000 A
00001 2014-02-02 00:00:00.000 B
00001 2014-02-02 00:00:00.000 C
00001 2014-02-20 00:00:00.000 A
00001 2014-02-20 00:00:00.000 B
00001 2014-02-20 00:00:00.000 C

the red color should be removed/excluded.

thanks for help.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-17 : 02:09:55

Question:
for 2014-02-01 , you have Groups "B" .It is correct or typo ?


DECLARE @Transactions TABLE
	(	
		EmpID CHAR(5),
		TransDate DATE)

DECLARE @Master TABLE
	(
		EmpID CHAR(5),
		EffectiveDateFr DATE,
		Groups CHAR(1) )

INSERT INTO @Transactions(EmpID,TransDate)
VALUES ('00001','1/1/2014')
		,('00001', '1/2/2014')
		,('00001', '1/3/2014')
		,('00001', '1/4/2014')
		,('00001', '1/5/2014')
		,('00001', '1/6/2014')
		,('00001', '1/15/2014')
		,('00001', '2/1/2014')
		,('00001', '2/2/2014')
		,('00001', '2/20/2014')

INSERT INTO @Master(EmpID, EffectiveDateFr, Groups)
VALUES('00001', '1/1/2014', 'A'),
	('00001', '1/5/2014' ,'B'),
	('00001', '1/9/2014' ,'C'),
	('00001', '2/1/2014' ,'B'),
	('00001', '2/20/2014' ,'A')


SELECT
	EmpID, TransDate, Groups
FROM 
	(
		SELECT  tr.EmpID, tr.TransDate, ms.Groups ,
				RN = Row_Number() Over (Partition By tr.EmpID, tr.TransDate ORDER BY  tr.EmpID, tr.TransDate ,ms.Groups DESC)
		FROM  
			@Transactions tr 
			INNER JOIN @Master ms
			ON tr.EmpID=ms.EmpID AND tr.TransDate>= ms.EffectiveDateFr
	)A

WHERE 
	A.RN = 1
ORDER BY 
	EmpID, TransDate, Groups

output:


EmpID	TransDate	Groups
00001	2014-01-01	A
00001	2014-01-02	A
00001	2014-01-03	A
00001	2014-01-04	A
00001	2014-01-05	B
00001	2014-01-06	B
00001	2014-01-15	C
00001	2014-02-01	C
00001	2014-02-02	C
00001	2014-02-20	C

sabinWeb MCP

hariyadi
Starting Member

3 Posts

Posted - 2014-07-17 : 02:49:20

stepson - sabinWeb MCP, thanks for you reply

I tried your query with my data, the result:

EmpID TransDate Groups
00001 2014-01-01 A
00001 2014-01-02 A
00001 2014-01-03 A
00001 2014-01-04 A
00001 2014-01-05 B
00001 2014-01-06 B
00001 2014-01-15 C
00001 2014-02-01 C
00001 2014-02-02 C
00001 2014-02-20 C
-------------------------------------
00001 2014-02-01 C should be B
00001 2014-02-02 C should be B

because in master Effectivedatefr 2014-02-01 is B

with your data:

INSERT INTO @Master(EmpID, EffectiveDateFr, Groups)
VALUES('00001', '1/1/2014', 'A'),
('00001', '1/5/2014' ,'B'),
('00001', '1/9/2014' ,'C'),
('00001', '2/1/2014' ,'B'),
('00001', '2/20/2014' ,'A') -- My value is C

the result:
EmpID TransDate Groups
00001 2014-01-01 A
00001 2014-01-02 A
00001 2014-01-03 A
00001 2014-01-04 A
00001 2014-01-05 B
00001 2014-01-06 B
00001 2014-01-15 C
00001 2014-02-01 C
00001 2014-02-02 C
00001 2014-02-20 C
Same as your reply

Thanks.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-17 : 03:07:27

[code]
SELECT
EmpID, TransDate, Groups
FROM
@Transactions tr
CROSS APPLY
(
SELECT TOP(1)
Groups
FROM
@Master ms
WHERE
tr.EmpID = ms.EmpID
AND tr.TransDate >= ms.EffectiveDateFr
ORDER BY
ms.EffectiveDateFr DESC )A
[/code]

sabinWeb MCP

hariyadi
Starting Member

3 Posts

Posted - 2014-07-17 : 03:17:23

hi stepson, thank you very much the result is perfect.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-17 : 03:23:20

Welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources