July 16, 2014 at 9:28 pm
Hi forum,
I have 2 tables:
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
thanks for help.
July 16, 2014 at 9:45 pm
Please set these up as usable (ie: runnable) temp table inserts, and explain how you expect 1/5's value to go to 1/6's date. Your explanation doesn't describe the entire concern.
If you need more help in understanding how to setup your question to help us help you (we're volunteers), see the first link below, in my sig.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 16, 2014 at 10:31 pm
I created 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 X
00001 2014-01-05 00:00:00.000 B
00001 2014-01-06 00:00:00.000 A X
00001 2014-01-06 00:00:00.000 B X
00001 2014-01-15 00:00:00.000 A X
00001 2014-01-15 00:00:00.000 B X
00001 2014-01-15 00:00:00.000 C
00001 2014-02-01 00:00:00.000 A X
00001 2014-02-01 00:00:00.000 B
00001 2014-02-01 00:00:00.000 C X
00001 2014-02-02 00:00:00.000 A X
00001 2014-02-02 00:00:00.000 B X
00001 2014-02-02 00:00:00.000 C
00001 2014-02-20 00:00:00.000 A X
00001 2014-02-20 00:00:00.000 B X
00001 2014-02-20 00:00:00.000 C XX
the X and XX should be removed/excluded
Sorry for my previous question.
Thanks.
July 16, 2014 at 11:17 pm
JA quick code here, not a full solution, just to get you going;-)
😎
USE tempdb;
GO
;WITH EMP_TRAN AS
(SELECT EmpID, TransDate FROM (VALUES
('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')) AS X(EmpID, TransDate)
)
,MASTER_TABLE AS
(SELECT EmpID,EffectiveDateFr,[Group] FROM (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')) AS X(EmpID,EffectiveDateFr,[Group])
)
,MASTER_ADD_RID AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY MT.EffectiveDateFr) AS MT_RID
,MT.EmpID
,MT.EffectiveDateFr
,MT.[Group]
FROM MASTER_TABLE MT
)
,MASTER_PERIOD AS
(SELECT
MA1.MT_RID
,MA1.EmpID
,MA1.EffectiveDateFr AS FROM_DATE
,ISNULL(MA2.EffectiveDateFr,'1/1/2020') AS TO_DATE
,MA1.[Group]
FROM MASTER_ADD_RID MA1
LEFT OUTER JOIN MASTER_ADD_RID MA2
ON MA1.MT_RID = MA2.MT_RID - 1
)
SELECT
ET.EmpID
,ET.TransDate
,MT.[Group]
FROM EMP_TRAN ET
OUTER APPLY MASTER_PERIOD MT
WHERE ET.TransDate >= MT.FROM_DATE
AND ET.TransDate < MT.TO_DATE
ORDER BY ET.TransDate
Results
EmpID TransDate Group
----- --------- -----
00001 1/1/2014 A
00001 1/15/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 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 B
July 17, 2014 at 2:51 am
hi, Eirikur Eiriksson thank you for your reply.
I will try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply