April 20, 2017 at 11:31 am
Hi all,
I'm having a moment, and I can't seem to get the query right. I'm trying to get the actual start and end dates for each rev_id.
Basically, each row has the same start date, but I want the start date to be the end date of the previous revision, based on the end date by article_id.
Here's some sample data:
IF OBJECT_ID('tData') IS NOT NULL
DROP TABLE tData
GO
CREATE TABLE tData
(nArticle_Id INTEGER NOT NULL,
nRev_Id INTEGER NOT NULL PRIMARY KEY,
dStart DATETIME NOT NULL,
dEnd DATETIME NULL)
GO
INSERT tData VALUES (6457, 51, '2015-01-08 08:11:40.000', '2015-01-08 08:23:02.020')
INSERT tData VALUES (6457, 95, '2015-01-08 08:11:40.000', '2015-01-08 10:28:18.173')
INSERT tData VALUES (6457, 82, '2015-01-08 08:11:40.000', '2015-02-05 13:59:13.027')
INSERT tData VALUES (6457, 102, '2015-01-08 08:11:40.000', NULL)
GO
And here are the expected results (sorry, not having much luck with the formatting):
nArticle_Id nRev_Id dStart dEnd
----------- ----------- ----------------------- -----------------------
6457 51 2015-01-08 08:11:40.000 2015-01-08 08:23:02.020
6457 82 2015-01-08 08:23:02.020 2015-02-05 13:59:13.027
6457 95 2015-02-05 13:59:13.027 2015-01-08 10:28:18.173
6457 102 2015-01-08 10:28:18.173 NULL
Can anyone help?
TIA,
Paul
April 20, 2017 at 11:51 am
DECLARE @tData TABLE
(
nArticle_Id int not null,
nRev_Id int not null PRIMARY KEY,
dStart datetime2 not null,
dEnd datetime2 null
);
INSERT into @tData
VALUES
(6457, 51, '2015-01-08 08:11:40.000', '2015-01-08 08:23:02.020'),
(6457, 95, '2015-01-08 08:11:40.000', '2015-01-08 10:28:18.173'),
(6457, 82, '2015-01-08 08:11:40.000', '2015-02-05 13:59:13.027'),
(6457, 102, '2015-01-08 08:11:40.000', NULL)
;
SELECT
t.nArticle_Id,
t.nRev_Id,
t.dStart,
(SELECT t2.dEnd FROM @tData t2 WHERE t2.nArticle_Id = t.nArticle_Id and t2.nRev_Id = (SELECT MAX(t3.nRev_Id) FROM @tdata t3 WHERE t3.nRev_id < t.nRev_ID)) as RealStartDate,
t.dEnd
FROM @tData t
;
April 20, 2017 at 12:12 pm
You can use the LAG function to return the previous end date - something like LAG(dEnd,1) OVER(PARTITION BY nArticle_Id ORDER BY nRev_Id) As NewStartDate
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 20, 2017 at 12:21 pm
it would be easier on a newer version of SQL Server, but for 2008 how about this?
SELECT nArticle_Id, nRev_Id, dStart, dEnd, ROW_NUMBER() OVER (PARTITION BY nArticle_Id ORDER BY nRev_Id) AS sort
INTO #sorted
FROM tData;
CREATE UNIQUE CLUSTERED INDEX IX_sorted ON #sorted (nArticle_Id, sort);
SELECT c.nArticle_Id, c.nRev_Id, ISNULL(p.dEnd, c.dStart), c.dEnd
FROM #sorted c
LEFT OUTER JOIN #sorted p ON c.nArticle_Id = p.nArticle_Id AND c.sort = p.sort + 1;
DROP TABLE #sorted;
April 20, 2017 at 12:22 pm
All:
I goofed on the expected results in the OP: here's how they should appear.
The PK (nrev_Id) can be out of calendar sequence (because users), so that can't be used for ordering.
nArticle_Id nRev_Id dPublished dExpired
6457 51 2015-01-08 08:11:40.000 2015-01-08 08:23:02.020
6457 95 2015-01-08 08:23:02.020 2015-01-08 10:28:18.173
6457 82 2015-01-08 10:28:18.173 2015-02-05 13:59:13.027
6457 102 2015-02-05 13:59:13.027 NULL
Bill, Thanks for taking the time to post the query, and if nothing else, pointing me in the right direction. I think I'll be able to get it now.
Jeffrey: LAG would appear to be ideal it, but is not available until SQL 2012
April 20, 2017 at 12:28 pm
Thanks Chris, that did it when I changed the ORDER BY to use the date in the ROW_NUMBER()
Much appreciated!
April 21, 2017 at 12:45 pm
Jeffrey Williams 3188 - Thursday, April 20, 2017 12:12 PMYou can use the LAG function to return the previous end date - something like LAG(dEnd,1) OVER(PARTITION BY nArticle_Id ORDER BY nRev_Id) As NewStartDate
This was posted in a SQL 2008 forum, and LAG was introduced in SQL 2012.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 31, 2017 at 7:02 am
Chris Harshman - Thursday, April 20, 2017 12:21 PMit would be easier on a newer version of SQL Server, but for 2008 how about this?
SELECT nArticle_Id, nRev_Id, dStart, dEnd, ROW_NUMBER() OVER (PARTITION BY nArticle_Id ORDER BY nRev_Id) AS sort
INTO #sorted
FROM tData;CREATE UNIQUE CLUSTERED INDEX IX_sorted ON #sorted (nArticle_Id, sort);
SELECT c.nArticle_Id, c.nRev_Id, ISNULL(p.dEnd, c.dStart), c.dEnd
FROM #sorted c
LEFT OUTER JOIN #sorted p ON c.nArticle_Id = p.nArticle_Id AND c.sort = p.sort + 1;DROP TABLE #sorted;
Hi Chris,
Just wanted to know, why the index was created. Please explain
May 31, 2017 at 9:39 am
VSSGeorge - Wednesday, May 31, 2017 7:02 AMHi Chris,
Just wanted to know, why the index was created. Please explain
The index isn't completely necessary, but depending on the number of distinct nArticle_ID values, and how many rows there are per nArticle_ID, then the index will help the self join of the #sorted table perform better.
June 1, 2017 at 12:47 am
This query would perform much better:SELECT t.nArticle_Id,
t.nRev_Id,
isnull(pt.dEnd,t.dStart) Start,
t.dEnd
FROM @tData t
outer apply (select top 1 dEnd from @tData p
where p.nArticle_Id = t.nArticle_Id and p.nRev_Id < t.nRev_Id
order by p.nRev_Id desc) pt
_____________
Code for TallyGenerator
June 4, 2017 at 9:22 pm
Now that there are multiple solutions for this problem, I have to ask, if the "current" start date will be replaced with the previous end date, why is there any need for the start date column in the table to being with?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2017 at 7:58 am
In short,
- the guy who modeled this insisted on doing it (t)his way. We let him. He doesn't work here anymore.
- I'm trying to get the "real" date ranges (as in, a given revision was live between X and Y dates) for each revision. For reasons that I have yet to fathom, users want the create date to inherit from previous revisions of the same article - in some cases. In other cases, the current date at the time of revision is what they want.
- Finally, I was pretty sure I was looking at an on-coming Triangular Join (thanks, Jeff!). As we know, works fine with a few hundred rows, not so much with a few hundred thousand.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply