February 28, 2014 at 10:57 am
In t-sql 2012, I have a table that I need to join to itself server items based upon customer number, cust_date, and attribute id.
The attrribute id values are 53, 54, and 55. There are the same attribute ids that can occur lots of times during the year so the cust_date can change.
I need to join rows in the table to itself several times where the cust_date is the same and the most current date.
Thus can you show me how to join the table to itself with selecting the maximum cust_date and the for attribute value = 53, 54, and 55?
February 28, 2014 at 11:52 am
without DDL, sample data, and expected results it makes it difficult to give you an answer. Check the link in my signature on how to post to get the best help.
that being said, using a CTE to get the max date for each attribute then join to CTE should get you started.
CREATE TABLE #tmp(id int,
dt date);
INSERT INTO #tmp
VALUES(53,'2014-01-01'),
(53,'2013-01-01'),
(54,'2010-01-01'),
(54,'2012-01-01'),
(55,'2011-01-01'),
(55,'2013-01-01');
WITH cte
AS (SELECT id,
MAX(dt)AS maxdt
FROM #tmp
GROUP BY id)
SELECT a.id,
a.dt
FROM #tmp AS a
INNER JOIN cte AS b
ON a.id = b.id
AND a.dt = b.maxdt;
DROP TABLE #tmp;
March 25, 2014 at 1:46 pm
Using Bob's DDL, here's a Window function version
CREATE TABLE #tmp(id int,
dt date);
INSERT INTO #tmp
VALUES(53,'2014-01-01'),
(53,'2013-01-01'),
(54,'2010-01-01'),
(54,'2012-01-01'),
(55,'2011-01-01'),
(55,'2013-01-01');
SELECT
id
,dt
FROM
(
select
id
,LAST_VALUE(dt) OVER
(
PARTITION BY id
ORDER BY dt
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS dt
,ROW_NUMBER() OVER
(
PARTITION BY id
ORDER BY dt DESC
) AS ID_RID
from #tmp
) AS X
WHERE X.ID_RID = 1;
drop table #tmp;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply