CODEWITHSUNDEEP
sql-serversql-server-2008
mns
mns

Reputation: 683

datediff between multiple records in SQL

I have one scenario where i want to get a count if date difference between the two dates is <=14 days.That is in first table i have to filter records where any one value of DATE1 values are <=14 days of DATE2.

For Ex:
 q1="SELECT DATE1 FROM DATE1_TABLE";
 q2="SELECT DATE2 FROM DATE2_TABLE";

 My simple query :

 SELECT 
    COUNT(*) 
 FROM 
     DATE1_TABLE WHERE DATEDIFF(DD,DATE1,(SELECT DATE2 FROM DATE2_TABLE))<=14

But i have multiple records in both the tables,but i want to choose any record having this difference then it will get a count >0.So,it is throwing error subquery returned more than one record.I want some solutions for this.I am using SQL SERVER 2008

NOTE:I can't use join here.because i wanted results from two different queries. Thanks in advance.

Upvotes: 0

Views: 2130

Answers (2)

Kapil Singh
Kapil Singh

Reputation: 107

You can use TOP 1 clause in your query..

SELECT TOP 1 *
    FROM DATE1_TABLE 
     WHERE DATEDIFF(DD,DATE1,(SELECT DATE2 FROM DATE2_TABLE))<=14

Upvotes: 1

user170442
user170442

Reputation:

You cannot use SELECT which will return multiple values where function expects scalar value... however you can join those tables:

SELECT 
  COUNT(DISTINCT dt1.*)
FROM DATE1_TABLE dt1 INNER JOIN DATE2_TABLE dt2 ON DATEDIFF(DD,DATE1,DATE2)<=14

This query will join tables on values only when they are within 14 days and count on unique values from DATE1_TABLE. I have no idea if is it performance wise.

Upvotes: 0

Related Questions