Find the start and end date (set based) in T-SQL

how to calculate start date and end date in sql
sql + start date and end date
sql create start and end date
how to set start date and end date in sql
how to check current date is between start_date and end_date in mysql
how to get the start and end dates of all weeks between two dates in sql server
how to get year start date and end date in sql
sql select starting from date

I have the below.

Name    Date
A   2011-01-01 01:00:00.000
A   2011-02-01 02:00:00.000
A   2011-03-01 03:00:00.000
B   2011-04-01 04:00:00.000
A   2011-05-01 07:00:00.000

The desired output is

Name       StartDate                        EndDate
-------------------------------------------------------------------
A          2011-01-01 01:00:00.000         2011-04-01 04:00:00.000    
B          2011-04-01 04:00:00.000         2011-05-01 07:00:00.000    
A          2011-05-01 07:00:00.000         NULL

How to achieve the same using TSQL in a set based approach.

DDL is as under

DECLARE @t TABLE(PersonName VARCHAR(32), [Date] DATETIME) 
INSERT INTO @t VALUES('A', '2011-01-01 01:00:00') 
INSERT INTO @t VALUES('A', '2011-01-02 02:00:00') 
INSERT INTO @t VALUES('A', '2011-01-03 03:00:00') 
INSERT INTO @t VALUES('B', '2011-01-04 04:00:00') 
INSERT INTO @t VALUES('A', '2011-01-05 07:00:00')

Select * from @t
;WITH cte1
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY Date) -
                ROW_NUMBER() OVER (PARTITION BY PersonName
                ORDER BY Date) AS G
         FROM   @t),
     cte2
     AS (SELECT PersonName,
                MIN([Date]) StartDate,
                ROW_NUMBER() OVER (ORDER BY  MIN([Date])) AS rn
         FROM   cte1
         GROUP  BY PersonName,
                   G)
SELECT a.PersonName,
       a.StartDate,
       b.StartDate AS EndDate
FROM   cte2 a
       LEFT JOIN cte2 b
         ON a.rn + 1 = b.rn  

Because the result of CTEs are not generally materialised however you may well find you get better performance if you materialize the intermediate result yourself as below.

DECLARE @t2 TABLE (
  rn         INT IDENTITY(1, 1) PRIMARY KEY,
  PersonName VARCHAR(32),
  StartDate  DATETIME );

INSERT INTO @t2
SELECT PersonName,
       MIN([Date]) StartDate
FROM   (SELECT *,
               ROW_NUMBER() OVER (ORDER BY Date) -
               ROW_NUMBER() OVER (PARTITION BY PersonName
               ORDER BY Date) AS G
        FROM   @t) t
GROUP  BY PersonName,
          G
ORDER  BY StartDate

SELECT a.PersonName,
       a.StartDate,
       b.StartDate AS EndDate
FROM   @t2 a
       LEFT JOIN @t2 b
         ON a.rn + 1 = b.rn 

sql server - Find the start and end date (set based) in T-SQL, ;WITH cte1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Date) - ROW_NUMBER() OVER (PARTITION BY PersonName ORDER BY  Find the start and end date (set based) in T-SQL. How to achieve the same using TSQL in a set based approach. The end date of a record is the start date of

SELECT
  PersonName,
  StartDate = MIN(Date),
  EndDate
FROM (
  SELECT
    PersonName,
    Date,
    EndDate = (
      /* get the earliest date after current date
         associated with a different person */
      SELECT MIN(t1.Date)
      FROM @t AS t1
      WHERE t1.Date > t.Date
        AND t1.PersonName <> t.PersonName
    )
  FROM @t AS t
) s
GROUP BY PersonName, EndDate
ORDER BY 2

Basically, for every Date we find the nearest date after it such that is associated with a different PersonName. That gives us EndDate, which now distinguishes for us consecutive groups of dates for the same person.

Now we only need to group the data by PersonName & EndDate and get the minimal Date in every group as StartDate. And yes, sort the data by StartDate, of course.

SQL Calendar Table based on Start and End Date Help , I'm given a Start Date and an End Date and I'm trying to return a table After doing research on the web I found the following SQL query that gives For a work-table free, sort-free, purely set-based way of handling this you  How to create a start date and end date according to month and year format? How to make a query for adding start and end date of the month? The following should give you the last day of the current month on sql-server-2000: To find the last day of the month for a given month try: This should do what you want.

Get a row number so you will know where the previous record is. Then, take a record and the next record after it. When the state changes we have a candidate row.

select 
  state, 
  min(start_timestamp),
  max(end_timestamp)

from
(
    select
        first.state, 
        first.timestamp_ as start_timestamp,
        second.timestamp_ as end_timestamp

        from
        (
            select
                *, row_number() over (order by timestamp_) as id
            from test
        ) as first

        left outer join
        (
            select
                *, row_number() over (order by timestamp_) as id
            from test
        ) as second
        on 
            first.id = second.id - 1 
            and first.state != second.state
) as agg
group by state
    having max(end_timestamp) is not null 

union

-- last row wont have a ending row
--(select state, timestamp_, null from test order by timestamp_ desc limit 1)
    -- I think it something like this for sql server
     (select top state, timestamp_, null from test order by timestamp_ desc)

order by 2
;

Tested with PostgreSQL but should work with SQL Server as well

Identifying Start Dates not Aligned with End Dates on a Prior Row , In a prior article published by SQL Server Central, Creating a Date Range from Multiple Rows Based on a Single Date, Drawing upon my previous SSC article, we'll construct a table with rows containing effective start and end dates, SELECT ProductID=n -- Adjust some start dates back to get overlaps  Get Start Date & End Date by Custom Date DECLARE @Date NVARCHAR(50)='05/19/2019' SELECT DATEADD(DAY,CASE WHEN DATEPART(WEEKDAY, @Date)=1 THEN -6 ELSE 2 - DATEPART(WEEKDAY, @Date) END, CAST(@Date AS DATE)) [Week_Start_Date] ,DATEADD(DAY,CASE WHEN DATEPART(WEEKDAY, @Date)=1 THEN 0 ELSE 8 - DATEPART(WEEKDAY, @Date) END, CAST(@Date AS DATE)) [Week_End_Date]

The other answer with the cte is a good one. Another option would be to iterate over the collection in any case. It's not set based, but it is another way to do it.

You will need to iterate to either A. assign a unique id to each record that corresponds to its transaction, or B. to actually get your output.

TSQL is not ideal for iterating over records, especially if you have a lot, and so I would recommend some other way of doing it, a small .net program or something that is better at iterating.

[Solved] Sql: Select between two dates ( start and end date , Try below and let me know if this works for your requirement. Hide Copy Code. SELECT * FROM MyTable WHERE ((StartDate > @StartDate  This blog helps in displaying the start of the week date span and end of the week date span, which will be displayed, using SQLl query. Here, I used some date time function to find out the week's start and end date simultaneously. It returns a particular date with the particular number interval added to a particular date part of the date.

There's a very quick way to do this using a bit of Gaps and Islands theory:

WITH CTE as (SELECT PersonName, [Date]
                   , Row_Number() over (ORDER BY [Date])
                     - Row_Number() over (ORDER BY PersonName, [Date]) as Island
             FROM @t)

Select PersonName, Min([Date]), Max([Date])
from CTE
GROUP BY Island, PersonName
ORDER BY Min([Date])

SQL Server: Find Week Start And End DateTime, Finding week start and end date time from a given date time. SET DATEFIRST 7; /*setting week start to 'Sunday'*/ DECLARE @dateTimeNow  That row’s implied end date is the day before the start date of the following row (at the end of that day). If there is no following row, the end date is NULL.

SQL Pattern: Summarizing entities with a start/end date over time , SQL Pattern: Summarizing entities with a start/end date over time entity was valid, and then you can just aggregate over the date to get your answer. O(e*d) intermediate result set - Not only do the number of comparisons  DATEDIFF (Transact-SQL) 07/18/2019; 8 minutes to read +12; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

DATEDIFF (Transact-SQL), Returns the numerical difference between a start and end date based on datepart​. See Date and Time Data Types and Functions (Transact-SQL) for an overview the startdate and enddate, expressed in the boundary set by datepart. This case means that if we start at startdate '2036-03-01', and then  If the date is 3 March 2009, the end date should be 3 March 2009 not 6 March 2009. Based on the OP's requirements. But, I must admit, much simplier and cleaner.

Handling Business-Related Date Tasks in T-SQL, If the date is not found in the Holidays table, the return value is set to 1 (true), You simply begin with a starting point and move through the dates based on the  How to Get the Month Name from a Date in SQL Server How to Get First and Last Day of a Year in SQL Server Difference Between GETDATE and CURRENT_TIMESTAMP in SQL Server

Comments
  • I don't understand how you are figuring the desired output. How do you decide what the end date is? For example, in the desired output you have a record: name start date end date A 2011-01-01 01:00:00.000 2011-04-01 04:00:00.000 But the input had that date 2011-04-01 04:00:00.000 associated with name B. How do we determine what the end date should be for a record. What is the definition of a record?
  • The end date of a record is the start date of another record. So A's startd date is 2011-01-01 01:00:00.000 but B's is 2011-04-01 04:00:00.000. So A's end date is 2011-04-01 04:00:00.000. Similarly The A that is following B, is having a start date as 2011-05-01 07:00:00.000 which is the end date for B.
  • But how do you know which record to choose as the end date of a particular record?
  • The first diffrence found in the Name. i.e.e A is appearing 3 times in the beginning and then B came on the 4th row. So 1 transaction is over. On the 5th row again A came. So from 4th to 5th there is a new transaction
  • Unfortunately, without some sort of logic for determining how you are choosing end dates and consolidating the data into the output, I don't think there is any way anyone can help write the SQL to get the output your desiring. There has to be some sort of logic to follow to get from A (the input) to B (the output).