score:2
Accepted answer
The most straightforward way to do this is to use a subquery:
select A.number, a.start_date, a.end_date,
CASE WHEN start_date < dateadd(d,30,(select TOP(1) b.end_date
from mytable B
where B.number < A.number
order by B.number desc)) then 1 else 0 end Criteria
from mytable A
Note: If the start date is the 29th day following the previous row's end date, Criteria
becomes 1. By the 30th day onwards, it is 0. Tweak the 30
in the query as required.
Sample:
create table mytable (
Number int primary key,
start_date datetime,
end_date datetime);
insert mytable
select 1, '2011-06-07', '2011-07-10' union all
select 2, '2011-10-11', '2011-10-27' union all
select 3, '2011-10-26', '2011-10-29' union all
select 4, '2011-10-29', '2011-11-15'
Result:
number start_date end_date Criteria
1 2011-06-07 00:00:00.000 2011-07-10 00:00:00.000 0
2 2011-10-11 00:00:00.000 2011-10-27 00:00:00.000 0
3 2011-10-26 00:00:00.000 2011-10-29 00:00:00.000 1
4 2011-10-29 00:00:00.000 2011-11-15 00:00:00.000 0
Read More
- SQL query stuck - comparison on different lines
- Query to list SQL Server stored procedures along with lines of code for each procedure
- How do you join tables from two different SQL Server instances in one SQL query
- Multiple aggregate functions in one SQL query from the same table using different conditions
- Tool for comparison of SQL Server query plans?
- Why would the exact same SQL query result with a different execution plan when executed via the sp_executeSQL procedure?
score:0
A more readable way is create a function and send the correct dates:
Function:
create function [dbo].[CompareDates] (
@START_DATE datetime,
@PREVIOUS_END_DATE datetime
)
RETURNS int
AS
BEGIN
if @START_DATE < @PREVIOUS_END_DATE
return 1
return 0
END
Query (using subquery):
declare @dates table
(
number int,
start datetime,
end_date datetime
)
insert into @dates values
(1, '2011-06-07 00:00:00.000', '2011-07-10 00:00:00.000'),
(2, '2011-10-11 00:00:00.000', '2011-10-27 00:00:00.000'),
(3, '2011-10-26 00:00:00.000', '2011-10-29 00:00:00.000'),
(4, '2011-10-29 00:00:00.000', '2011-11-15 00:00:00.000')
select *, dbo.CompareDates(dates.end_date, dates.previous_end_date) from
(
select number, start, end_date,
(select TOP 1 end_date
from @dates d2
where d2.number < d1.number
order by d2.number desc) as previous_end_date
from @dates d1
) dates
score:1
Try using case
like this:
create view vDates as
select Number,start_date,end_date,
case
when start_date<end_date
then 0
else 1
end as Criteria
from tab
SQL Fiddle Demo
More questions
- stuck with one query in SQL Server
- Query Across different database - Same table SQL Server 2012
- Very different execution times of SQL query in C# and SQL Server Management Studio
- SQL Date Range Query - Table Comparison
- SQL query counting the number of results where different conditions hold
- Comparison Query to Compare Two SQL Server Tables
- SQL join Query Between Two Different Database File
- Need SQL Query for accessing different serial numbers present in a table
- Getting data from different database on different server with one SQL Server query
- Can SQL return different results for two runs of the same query using ORDER BY?
- SQL Server different design query performance
- Using Python to Query multiple SQL databases on different servers
- SQL query to extract text from a column and store it to a different column in the same record
- SQL Server query against two linked databases using different collations
- Executing a SQL Query multiple times with different parameters
- How to get SQL results as Different sets in SQL Query
- SQL Server query through view and query through base tables get different execution plan
- SQL query to compute calculations from different rows of the same table in SQL server
- SQL Comparison query
- Stuck with a somewhat complex SQL query
- SQL Database Query selecting different corresponding values for one value
- SQL Query - Get Sums from Two Different Levels of Hierarchy
- SQL query with datetime value shows different result
- SQL query and MS Access query returns different data
More questions with similar tag
- Flatten table hierarchy
- How to insert weekdays names in sql column based on given date?
- SQL Service Broker and .NET Windows Service - Best practices?
- Connecting android with MS SQL SERVER 2008
- EXCEPT returning unpredictable results
- How to configure Tomcat 7 for SQL Server 2008 for use in java servlet programs?
- Storing data in non alphabetical order
- SQL Server 2008 Query to Delete All Rows Where Values Are Not Found in 3 Other tables
- How can I delete a record, child records and grandchildren records?
- SQL conditional variables in select query
- Query to know when is the last time stored procedure got executed?
- How to order by date when the column is not date formatted?
- tsql - sp_executesql difference
- Commiting outer transaction
- SQL Query (pl/sql) for a complicated logic
- Problems with SQL Inner join
- Putting comma separated values into separate or different columns
- BCP Ultility: How to get number of record imported
- What does this number mean? in sqldbx connection with remote database
- SQL Server is trying to use two paths for backup location
- How to set the maxrecursion option for a CTE inside a Table-Valued-Function
- Possible steps to improve SQL Server query performance
- SQL Server error: Why getting error 26 when doing a role system lookup in .NET
- Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY
- Entity Framework generate extra foreign keys in the database
- Flush result set
- Natural full outer join?
- Table clusters in SQLServer
- How do I make ALTER COLUMN idempotent?
- left join two tables on a non-unique column in right table
Source:
stackoverflow.com