April 25, 2013 at 1:30 am
Hi All,
Need your help to create a Left Out join on 2 tables, which will be based on the Period Code.
Below are the tables, not that challenge is that, we need to get the Max of the Period From table 2 which should be Less than or equal to the Table 1 Period - 6 Month.
Example: Row ADS 01.2013 should have the row in table 2 with ADS and Period in (01.2013,12.2012,11.2012,10.2012,09.2012,08.2012)
Table1:
Name Period
ABD 01.2013
BCD 02.2013
ADS 03.2013
AZD 04.2013
Table 2:
Name Period
ABD 02.2013
ABD 08.2012
BCD 02.2013
ADS 04.2012
Please need help on the same....!
AZD 04.2013
April 25, 2013 at 5:45 am
the way that the period is stored makes this more troublesome, if the period and year were split out then this would make it easier. I've included a script below which I think does what you require but it is not pretty as I have had to extract the Year and Period elements in order to work out what -6 months would be, I have handled this part in a CTE and included a column to show the minimum period required to check for. The process to then get the max period is done via an OUTER APPLY rather than a LEFT OUTER JOIN.
CREATE TABLE #table1
(Name varchar(4),
Period varchar(10)
)
INSERT INTO #table1
VALUES('ABD','01.2013'),
('BCD','02.2013'),
('ADS','03.2013'),
('AZD','04.2013')
CREATE TABLE #table2
(Name varchar(4),
Period varchar(10)
)
INSERT INTO #table2
VALUES('ABD','02.2013'),
('ABD','08.2012'),
('BCD','02.2013'),
('ADS','04.2012')
-- convert to CTE with period and year converted
WITH Tab1 as
(
SELECTname,
Period as [OriginalPeriod],
--LEFT(PERIOD,CHARINDEX('.',Period)-1) as [Period],
--RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period)) as [Year],
RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period)) +LEFT(PERIOD,CHARINDEX('.',Period)-1) as [ConvertedPeriod]
,CAST(CASEWHEN LEFT(PERIOD,CHARINDEX('.',Period)-1) -6 < 1
THEN RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period))-1
ELSE RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period))
ENDAS VARCHAR(4)) +
RIGHT('00' +CAST(CASE WHEN LEFT(PERIOD,CHARINDEX('.',Period)-1) -6 < 1
THEN 12 -(6- LEFT(PERIOD,CHARINDEX('.',Period)-1) )
ELSE LEFT(PERIOD,CHARINDEX('.',Period)-1) -6
ENDas varchar(4)),2)
as [MinPeriod]
FROM #table1 as t1
)
,
Tab2 as (
SELECTname,
--LEFT(PERIOD,CHARINDEX('.',Period)-1) as [Period],
--RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period)) as [Year],
RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period)) +LEFT(PERIOD,CHARINDEX('.',Period)-1) as [ConvertedPeriod]
FROM #table2 as t1
)
SELECT b1.Name,b1.[OriginalPeriod],
RIGHT(bb.[ConvertedPeriod],2)+'.'+left(bb.[ConvertedPeriod],4) as [Tab2MaxPeriod]
FROM Tab1 as b1
OUTER APPLY
(SELECT MAX(b2.[ConvertedPeriod]) as [ConvertedPeriod]
FROM Tab2 as b2
WHERE b1.[Name] = b2.[Name]
and b2.[ConvertedPeriod] >= b1.[minperiod]
and b2.[ConvertedPeriod] <= b1.[ConvertedPeriod]
) as bb
April 25, 2013 at 6:47 am
SELECT *
FROM #Table1 t1
CROSS APPLY (
SELECT TheDate = CONVERT(DATE,'01/'+LEFT(t1.Period,2)+'/'+RIGHT(t1.Period,4),103)
) x
OUTER APPLY (
SELECT *
FROM #Table2 t2
WHERE t2.Name = t1.Name
AND CONVERT(DATE,'01/'+LEFT(t2.Period,2)+'/'+RIGHT(t2.Period,4),103)
BETWEEN DATEADD(mm,-6,x.TheDate) AND x.TheDate
) y
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 25, 2013 at 7:31 am
You guys are the Best....!
Thanks The issue is resolved....!
April 25, 2013 at 7:37 am
Here's an alternative version which might be faster:
-- inline tally table has 7 rows = "6 months back"
;WITH iTally (n) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6)
SELECT *
FROM #Table1 t1
OUTER APPLY (
SELECT *
FROM (
SELECT TheStartDate = DATEADD(mm,0-n,CONVERT(DATE,
'01/'+LEFT(t1.Period,2)+'/'+RIGHT(t1.Period,4),103)
)
FROM iTally
) d
CROSS APPLY (SELECT MatchPeriod = RIGHT('0'+CAST(MONTH(TheStartDate) AS VARCHAR(2)),2) + '.' +
CAST(YEAR(TheStartDate) AS CHAR(4))) x
INNER JOIN #Table2 t2 ON t2.Name = t1.Name AND t2.Period = x.MatchPeriod
) y
- because the periods are reconstructed for the table on the RHS, permitting the use of an index.
Edit: simplification.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply