June 6, 2014 at 12:01 am
Here is my tables
CREATE TABLE #templocation (
locationid int,
maintainancefalg varchar(10)
)
INSERT INTO #templocation (locationid) VALUES (1)
INSERT INTO #templocation (locationid) VALUES (2)
INSERT INTO #templocation (locationid) VALUES (3)
CREATE TABLE #templocationhistory (
historyid int ,
locationid int,
flag BIT,
createddate datetime,
modifieddate datetime
)
INSERT INTO #templocationhistory (historyid , locationid , flag , createddate) values (1,1,1,'2014-05-06')
INSERT INTO #templocationhistory (historyid , locationid , flag , modifieddate) values (2,1,0,'2014-07-07')
INSERT INTO #templocationhistory (historyid , locationid , flag , createddate) values (3,2,1,'2014-02-01')
--my output for #templocation should be below
-- maintainancefalg need to be updated based on the maxdate in ( createddate or modifieddate ) from the flag column in #templocationhistory
locationid maintainancefalg
10
2 1
3''
June 6, 2014 at 12:54 am
WITH CTE_AssignRowNumber AS
(
SELECT
historyid
,locationid
,flag
,createddate
,modifieddate
,RID = ROW_NUMBER() OVER (PARTITION BY locationid ORDER BY modifieddate DESC)
FROM #templocationhistory
)
UPDATE t
SET maintainancefalg = cte.flag
FROM #templocation t
JOIN
(
SELECT locationid, flag
FROM CTE_AssignRowNumber
WHERE RID = 1
) cte ON t.locationid = cte.locationid;
SELECT * FROM #templocation;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 7:09 am
WITH cte (locationid,flag,RID) AS (
SELECT locationid,flag,ROW_NUMBER() OVER (
PARTITION BY locationid
ORDER BY ISNULL(createddate,modifieddate) DESC)
FROM #templocationhistory)
UPDATE l
SET l.maintainancefalg = ISNULL(CAST(cte.flag as varchar(1)),'')
FROM #templocation l
LEFT JOIN cte ON cte.locationid = l.locationid AND cte.RID = 1
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply