June 7, 2012 at 4:55 am
Hi
Can someone please help me with this?
Raw Data
ID StartDate DEP EndDate
1 14/02/2011 A 14/02/2011
1 14/02/2011 A 15/02/2011
1 16/03/2011 A 25/04/2011
1 05/05/2011 A 05/05/2011
1 13/05/2011 B 16/05/2011
1 20/05/2011 B 20/05/2011
1 06/08/2011 B 08/08/2011
2 06/09/2011 B 08/09/2011
I want to pick up min startdate and max Enddate if within 14 days.
The Output should be
ID StartDate DEP EndDate
1 14/02/2011 A 15/02/2011
1 16/03/2011 B 20/05/2011
1 06/08/2011 B 08/08/2011
2 06/09/2011 B 08/09/2011
Many Thanks
June 7, 2012 at 6:45 am
I don't understand your requirements completely.
What should be within 14 days? Start dates? Start dates and end dates?
Can you clarify please?
For those interested in answering this question, here's sample data in a consumable format:
DECLARE @sampleData TABLE (
id int,
StartDate datetime,
DEP char(1),
EndDate datetime
)
SET DATEFORMAT DMY
INSERT INTO @sampleData VALUES(1,'14/02/2011','A','14/02/2011')
INSERT INTO @sampleData VALUES(1,'14/02/2011','A','15/02/2011')
INSERT INTO @sampleData VALUES(1,'16/03/2011','A','25/04/2011')
INSERT INTO @sampleData VALUES(1,'05/05/2011','A','05/05/2011')
INSERT INTO @sampleData VALUES(1,'13/05/2011','B','16/05/2011')
INSERT INTO @sampleData VALUES(1,'20/05/2011','B','20/05/2011')
INSERT INTO @sampleData VALUES(1,'06/08/2011','B','08/08/2011')
INSERT INTO @sampleData VALUES(2,'06/09/2011','B','08/09/2011')
-- Gianluca Sartori
June 7, 2012 at 6:52 am
Using the sample datab Gianluca provided, the below gets the expected result output
DECLARE @sampleData TABLE (
id int,
StartDate datetime,
DEP char(1),
EndDate datetime
)
SET DATEFORMAT DMY
INSERT INTO @sampleData VALUES(1,'14/02/2011','A','14/02/2011')
INSERT INTO @sampleData VALUES(1,'14/02/2011','A','15/02/2011')
INSERT INTO @sampleData VALUES(1,'16/03/2011','A','25/04/2011')
INSERT INTO @sampleData VALUES(1,'05/05/2011','A','05/05/2011')
INSERT INTO @sampleData VALUES(1,'13/05/2011','B','16/05/2011')
INSERT INTO @sampleData VALUES(1,'20/05/2011','B','20/05/2011')
INSERT INTO @sampleData VALUES(1,'06/08/2011','B','08/08/2011')
INSERT INTO @sampleData VALUES(2,'06/09/2011','B','08/09/2011')
select * from @sampleData where DATEDIFF(day,startdate,EndDate) between 1 and 14
June 7, 2012 at 10:19 am
I want to check End date is within 14 days of next row start date, If within 14 days than use first row start date and last row end date
The results will look like this
ID StartDate DEP EndDate
1 14/02/2011 A 15/02/2011
1 16/03/2011 B 20/05/2011
1 06/08/2011 B 08/08/2011
2 06/09/2011 B 08/09/2011
June 9, 2012 at 9:06 am
can someone please point me to wright direction??
June 10, 2012 at 8:04 pm
Looks like a slightly more complicated version of a gaps and islands type problem.
You may want to take a look at this link and see if it helps you.
http://www.sqlservercentral.com/articles/T-SQL/71550/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 11, 2012 at 12:45 am
Join the table to itself using row_number or some other unique id on id =id+1 (ie next row) where datediff a and b less than 14.
June 11, 2012 at 2:18 am
HUSRAF (6/7/2012)
HiCan someone please help me with this?
Raw Data
ID StartDate DEP EndDate
1 14/02/2011 A 14/02/2011
1 14/02/2011 A 15/02/2011
1 16/03/2011 A 25/04/2011
1 05/05/2011 A 05/05/2011
1 13/05/2011 B 16/05/2011
1 20/05/2011 B 20/05/2011
1 06/08/2011 B 08/08/2011
2 06/09/2011 B 08/09/2011
I want to pick up min startdate and max Enddate if within 14 days.
The Output should be
ID StartDate DEP EndDate
1 14/02/2011 A 15/02/2011
1 16/03/2011 B 20/05/2011
1 06/08/2011 B 08/08/2011
2 06/09/2011 B 08/09/2011
Many Thanks
Your problem description and the expected output don't match - the second row of your expected output doesn't exist in the sample data set. Please try to describe your problem more accurately. What are the partitions in the data within which you expect to calculate min startdate and max enddate?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2012 at 5:23 am
So I give you the link for gaps and islands and can't figure out how to make it work myself! What's up with that?!
So I am left with something that is truly ugly, and no doubt someone will come along and provide you a much better solution, but it seems that for now this is the best I can do. It does at least work.
DECLARE @sampleData TABLE (
id int,
StartDate datetime,
DEP char(1),
EndDate datetime
)
INSERT INTO @sampleData VALUES(1,'02/14/2011','A','02/14/2011')
INSERT INTO @sampleData VALUES(1,'02/14/2011','A','02/15/2011')
INSERT INTO @sampleData VALUES(1,'03/16/2011','A','04/25/2011')
INSERT INTO @sampleData VALUES(1,'05/05/2011','A','05/05/2011')
INSERT INTO @sampleData VALUES(1,'05/13/2011','B','05/16/2011')
INSERT INTO @sampleData VALUES(1,'05/20/2011','B','05/20/2011')
INSERT INTO @sampleData VALUES(1,'08/06/2011','B','08/08/2011')
INSERT INTO @sampleData VALUES(2,'09/06/2011','B','09/08/2011')
;WITH Data AS (
SELECT id, StartDate, DEP, EndDate
,n=ROW_NUMBER() OVER (PARTITION BY id ORDER BY StartDate)
FROM @sampleData),
Data2 AS (
SELECT id, StartDate, DEP, EndDate, n2=1
FROM Data
WHERE n = 1
UNION ALL
SELECT d1.id
,CASE WHEN d2.StartDate - d1.EndDate <=14 THEN d1.StartDate ELSE d2.StartDate END
,d2.DEP, d2.EndDate, n2+1
FROM Data2 d1
INNER JOIN Data d2 ON d1.id = d2.id and n2+1 = n),
Data3 AS (
SELECT id, StartDate, DEP, EndDate
,rk=ROW_NUMBER() OVER (PARTITION BY id, StartDate ORDER BY EndDate DESC)
FROM Data2)
SELECT id, StartDate, DEP, EndDate
FROM Data3
WHERE rk=1
OPTION(MAXRECURSION 0)
Calling all SQL MVP's out there! Surely you can make me look like an idiot on this one.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 11, 2012 at 5:36 am
HUSRAF (6/7/2012)
I want to check End date is within 14 days of next row start date, If within 14 days than use first row start date and last row end dateThe results will look like this
ID StartDate DEP EndDate
1 14/02/2011 A 15/02/2011
1 16/03/2011 B 20/05/2011
1 06/08/2011 B 08/08/2011
2 06/09/2011 B 08/09/2011
I don't understand why the row with ID = 2 should be included in the result set, because the startdate is long after the enddate of any preceeding row. The nearest I can get to this is the following:
SET DATEFORMAT DMY
DROP TABLE #RawData
-- Added RowID as a reference for the thread.
CREATE TABLE #RawData (RowID INT, ID INT, StartDate DATETIME, DEP CHAR(1), EndDate DATETIME)
INSERT INTO #RawData (RowID, ID, StartDate, DEP, EndDate)
SELECT 1, 1, '14/02/2011', 'A', '14/02/2011' UNION ALL
SELECT 2, 1, '14/02/2011', 'A', '15/02/2011' UNION ALL
SELECT 3, 1, '16/03/2011', 'A', '25/04/2011' UNION ALL
SELECT 4, 1, '05/05/2011', 'A', '05/05/2011' UNION ALL
SELECT 5, 1, '13/05/2011', 'B', '16/05/2011' UNION ALL
SELECT 6, 1, '20/05/2011', 'B', '20/05/2011' UNION ALL
SELECT 7, 1, '06/08/2011', 'B', '08/08/2011' UNION ALL
SELECT 8, 2, '06/09/2011', 'B', '08/09/2011'
;WITH OrderedData AS (
SELECT RowID, --rn = ROW_NUMBER() OVER(ORDER BY ID, DEP, Startdate),
ID, StartDate, DEP, EndDate
FROM #RawData
)
SELECT a.*, '#' '#', b.*
FROM OrderedData a
LEFT JOIN OrderedData b ON b.RowID = a.RowID + 1
AND DATEDIFF(dd, a.EndDate, b.StartDate) <= 14
I've used a left join so you can see how rows which match the requirements would be included in the output.
How does your requirement differ from this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply