How to get the Output based on Next Date if exists in the Table

Robymon

This is my table RowNumber TestDate 1 2014-01-01 00:00:00.000 2 2014-01-04 00:00:00.000 3 2014-01-05 00:00:00.000 4 2014-01-06 00:00:00.000 5 2014-01-10 00:00:00.000 I want to Make Out Put Like this RowNumber StartDate EndDate 1 2014-01-01 00:00:00.000 2014-01-01 00:00:00.000 2 2014-01-04 00:00:00.000 2014-01-06 00:00:00.000 3 2014-01-10 00:00:00.000 2014-01-10 00:00:00.000

Lost User

Then you really need to explain the criteria for selecting the various items. As displayed above it is not obvious how you choose which dates.

Mycroft Holmes

Try this, changing Modified and TableName to suit.

SELECT D.Dt, max(Modified)MaxDT,MIN(Modified)MinDT
FROM TableName N
INNER JOIN (SELECT DISTINCT CONVERT(DATE,Modified) Dt
FROM TableName ) D ON D.Dt = CONVERT(DATE,N.Modified)
GROUP BY Dt

Never underestimate the power of human stupidity RAH

Robymon

This sql not getting the correct Out put, it shows like this Dt MaxDT MinDT 2014-01-01 2014-01-01 00:00:00.000 2014-01-01 00:00:00.000 2014-01-04 2014-01-04 00:00:00.000 2014-01-04 00:00:00.000 2014-01-05 2014-01-05 00:00:00.000 2014-01-05 00:00:00.000 2014-01-06 2014-01-06 00:00:00.000 2014-01-06 00:00:00.000 2014-01-10 2014-01-10 00:00:00.000 2014-01-10 00:00:00.000 I want to get it like this way RowNumber StartDate EndDate 1 2014-01-01 00:00:00.000 2014-01-01 00:00:00.000 2 2014-01-04 00:00:00.000 2014-01-06 00:00:00.000 3 2014-01-10 00:00:00.000 2014-01-10 00:00:00.000

Mycroft Holmes

Then you are screwed, there is no indication that the 5th is not the end of a job. You have not supplied enough information.

Never underestimate the power of human stupidity RAH

Tim Carmichael

If I understand the problem correctly, a start date means the preceeding date in not in the table; an end date is the last successive date from a start date in the table or the start date if there are no successive dates. So... 2014-01-01 is a start date because 2013-12-31 doesn't exist; 2014-01-01 is the end date because 2014-01-02 doesn't exist. 2014-01-04 is a start date because 2014-01-03 doesn't exist, giving 2014-01-06 as the end date. And so on... Given that, provide the SQL statement you are using and someone may help; don't ask them to write everything for you.

Bernhard Hiller

How do you expect us to find out code for that transformation if you refuse to tell us the transformation rules?