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
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page

hariyadi
Starting Member

3 Posts

Posted - 2014-07-17 : 03:17:23
hi stepson, thank you very much the result is perfect.
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

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


sabinWeb MCP
Go to Top of Page
   

- Advertisement -