T-SQL CASE WHEN take latest value

Question

T-SQL CASE WHEN take latest value

jSqlB

SSC-Addicted

Points: 436
More actions
January 21, 2016 at 9:10 am

#324196

I have a table of dates, companyIDs, and industry.
CREATE TABLE #mytable
(
date DATETIME,
companyID INT,
industry nvarchar(100)
)
If before 5/30/2000 some companyIDs had industry values that are a generic 'Corporate' then after 5/30/2000 became more specific such as 'Energy', 'Technology', 'Communications', etc.
What would be best T-SQL syntax to SELECT and GROUP BY date and industry, but for all companyIDs which have a industry of 'Corporate' use that companyID's latest industry value instead? So instead of 4/30/2000 Corporate, it would be 4/30/2000 Energy (or whatever the respective companyID latest industry was.
Here is the basic query I would like, but needs a subquery or CASE WHEN so industries that are 'Corporate' instead take the value of that companyIDs latest industry value:
SELECT m.date, m.industry
FROM #MyTable m
GROUP BY m.date m.industry
ORDER BY m.date DESC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

joeshu26 (1/21/2016)
I have a table of dates, companyIDs, and industry.
CREATE TABLE #mytable
(
date DATETIME,
companyID INT,
industry nvarchar(100)
)
If before 5/30/2000 some companyIDs had industry values that are a generic 'Corporate' then after 5/30/2000 became more specific such as 'Energy', 'Technology', 'Communications', etc.
What would be best T-SQL syntax to SELECT and GROUP BY date and industry, but for all companyIDs which have a industry of 'Corporate' use that companyID's latest industry value instead? So instead of 4/30/2000 Corporate, it would be 4/30/2000 Energy (or whatever the respective companyID latest industry was.
Here is the basic query I would like, but needs a subquery or CASE WHEN so industries that are 'Corporate' instead take the value of that companyIDs latest industry value:
SELECT m.date, m.industry
FROM #MyTable m
GROUP BY m.date m.industry
ORDER BY m.date DESC

Can you post some sample data (as inserts) for your temp table that represents the issue. Also, you should post what you expect as output. Once we know that we can help you here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

Are you looking for something like this?

SELECT m.date,

CASE WHEN m.date < '20000530' AND m.industry = 'Corporate'

THEN LAST_VALUE( m.industry) OVER(PARTITION BY m.companyID

ORDER BY date

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

ELSE m.industry END AS industry

FROM #MyTable m;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jSqlB SSC-Addicted Points: 436 More actions · Answer 3

INSERT INTO #myTable

(date, companyID, industry)

SELECT '20000430', '2', 'Corporate' UNION ALL

SELECT '20000531', '2', 'Corporate' UNION ALL

SELECT '20000630', '2', 'Energy' UNION ALL

SELECT '20000731', '2', 'Energy' UNION ALL

SELECT '20000430', '4', 'Corporate' UNION ALL

SELECT '20000531', '4', 'Corporate' UNION ALL

SELECT '20000630', '4', 'Technology' UNION ALL

SELECT '20000731', '4', 'Technology' UNION ALL

SELECT '20000731', '4', 'Technology'

I would like output to look like:

Date industry

2000-04-30 Energy

2000-05-31 Energy

2000-06-30 Energy

2000-07-31 Energy

2000-04-30 Technology

2000-05-31 Technology

2000-06-30 Technology

2000-07-31 Technology

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

Any questions, comments or improvements?

WITH CTE AS(

SELECT m.date,

CASE WHEN EXISTS( SELECT * FROM #mytable c

WHERE c.date < '20000530'

AND c.industry = 'Corporate'

AND c.companyID = m.companyID)

THEN LAST_VALUE( m.industry) OVER(PARTITION BY m.companyID

ORDER BY date

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

ELSE m.industry END AS industry

FROM #MyTable m

)

SELECT *

FROM CTE

GROUP BY date, industry

ORDER BY industry, date;

Why don't you normalize the table to have the industry in the companies table?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2