Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help on creating Left outer Join Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 1:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
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
Post #1446323
Posted Thursday, April 25, 2013 5:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:58 AM
Points: 1,240, Visits: 962
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
(
SELECT name,
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(CASE WHEN LEFT(PERIOD,CHARINDEX('.',Period)-1) -6 < 1
THEN RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period))-1
ELSE RIGHT(PERIOD,LEN(PEriod)-CHARINDEX('.',Period))
END AS 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
END as varchar(4)),2)
as [MinPeriod]
FROM #table1 as t1
)
,
Tab2 as (
SELECT name,
-- 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






Post #1446432
Posted Thursday, April 25, 2013 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446465
Posted Thursday, April 25, 2013 7:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 06, 2014 11:47 PM
Points: 86, Visits: 214
You guys are the Best....!

Thanks The issue is resolved....!
Post #1446490
Posted Thursday, April 25, 2013 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 6,748, Visits: 12,843
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1446496
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse