Find the start and end date (set based) in T-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
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
- 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).