May 25, 2016 at 11:28 am
Hi there,
I have a table that contains CompanyID, Current CompanyName
CREATE TABLE temp.Company_CurrentInfo(CompanyID INT, CompanyName Varchar(500))
INSERT INTO temp.Company_CurrentInfo VALUES(1,'Kay Enterprises')
INSERT INTO temp.Company_CurrentInfo VALUES(2,'RIDGE PARTNERS')
I also have a history table that keeps track of Company Name changes over the years
CREATE TABLE temp.Company_HistoryInfo(CompanyID INT, PreviousCompanyName Varchar(500),NameYear CHAR(4));
INSERT INTO temp.Company_HistoryInfo VALUES(1,'Kay Enterprises',2015)
INSERT INTO temp.Company_HistoryInfo VALUES(1,'The Kay INC',2014)
INSERT INTO temp.Company_HistoryInfo VALUES(1,'Kay INC',2013)
INSERT INTO temp.Company_HistoryInfo VALUES(1,'Kay Group',2012)
INSERT INTO temp.Company_HistoryInfo VALUES(2,'RIDGE',2015)
INSERT INTO temp.Company_HistoryInfo VALUES(2,'The RIDGE PARTNERS',2014)
INSERT INTO temp.Company_HistoryInfo VALUES(2,'Medical Company of RIDGE',2013)
INSERT INTO temp.Company_HistoryInfo VALUES(2,'RIDGE Dental',2012)
INSERT INTO temp.Company_HistoryInfo VALUES(2,'RIDGE LLC',2011)
INSERT INTO temp.Company_HistoryInfo VALUES(2,'Short Term Disability company at Ridge',2010)
I want to Print
CompanyID, CompanyCurrentName , two CompanyHistoryNames and the rule for CompanyHistoryname is,
a) it should not be same as CompanyCurrentName
b) first 3 Characters of CompanyCurrentName != first three characters of PreviousCompanyName
and I only pick 2 top most by NameYear
CompanyID CompanyName CompanyPrevName1 CompanyPrevName2
1 Kay Enterprises The Kay INC
2 RIDGE PARTNERS The RIDGE PARTNERS Medical Company of RIDGE
This is the query I have but it returns Null for both Previous names
SELECT * FROM
(
SELECT AA.companyID as companyid
,AA.CompanyName as Companyname
,BB.PreviousCompanyName AS HistoryName
,NameYear
FROM temp.Company_CurrentInfo AA
LEFT JOIN temp.Company_HistoryInfo BB
ON AA.CompanyID=BB.companyID
WHERE AA.CompanyName != BB.PreviousCompanyName and LEFT(AA.CompanyName,3) != LEFT(BB.PreviousCompanyName,3)
) as j
PIVOT (MAX(HistoryName) FOR CompanyName in ([CompanyPrevName1],[CompanyPrevName2])) as qry
Any help is appreciated.
Thanks
Rs
May 25, 2016 at 12:34 pm
Try the following:
; WITH chi_nm AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY chi.CompanyID, LEFT(chi.PreviousCompanyName, 3) ORDER BY chi.NameYear DESC) AS rn
FROM (
VALUES
(1,'Kay Enterprises',2015),
(1,'The Kay INC',2014),
(1,'Kay INC',2013),
(1,'Kay Group',2012),
(2,'RIDGE',2015),
(2,'The RIDGE PARTNERS',2014),
(2,'Medical Company of RIDGE',2013),
(2,'RIDGE Dental',2012),
(2,'RIDGE LLC',2011),
(2,'Short Term Disability company at Ridge',2010)
) chi(CompanyID, PreviousCompanyName, NameYear)
)
, chi_ord AS (
SELECT chi_nm.CompanyID, chi_nm.PreviousCompanyName, ROW_NUMBER() OVER(PARTITION BY chi_nm.CompanyID ORDER BY chi_nm.NameYear DESC) AS rn
FROM chi_nm
WHERE rn = 1
)
SELECT chi_ord.CompanyID
,MAX(CASE WHEN rn = 1 THEN PreviousCompanyName END) AS CompanyName1
,MAX(CASE WHEN rn = 2 THEN PreviousCompanyName END) AS CompanyName2
,MAX(CASE WHEN rn = 3 THEN PreviousCompanyName END) AS CompanyName3
FROM chi_ord
GROUP BY chi_ord.CompanyID
Note that if the company names are the same, then the first three characters will also be the same, so we only have to test for the second condition. The first CTE orders the names within each unique three-letter starting string. The second one only takes the most recent record within each group and then reorders them by year. The final one does the crosstab("pivot") based on that new ranking.
Drew
PS: I've used a table value constructor rather than your temporary table.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 25, 2016 at 12:47 pm
Also, the reason that you are getting NULLs is that the following code is looking for cases where the CompanyName field is equal to the literal strings 'CompanyPrevName1' and 'CompanyPrevName2'. Since the CompanyName field never contains either of those literal values, it never satisfies the conditions.
PIVOT (MAX(HistoryName) FOR CompanyName in ([CompanyPrevName1],[CompanyPrevName2]))
In other words, the above code is roughly equivalent to the following:
MAX(CASE WHEN CompanyName = 'CompanyPrevName1' THEN HistoryName END) AS CompanyPrevName1,
MAX(CASE WHEN CompanyName = 'CompanyPrevName2' THEN HistoryName END) AS CompanyPrevName2
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 26, 2016 at 7:13 am
Oh OK. I understand why my query didn't work.
Thanks for suggesting crosstab ..I haven't used this much before.
If I want print all CompanyPrevName instead of just 2, how can I generalize the query?
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply