CODEWITHSUNDEEP
sqlsql-serverrelational-database
Ganesh Satpute
Ganesh Satpute

Reputation: 3941

SQL : Return column value based on previous few rows values

I have a table something like this

NAME    TIME                    IsTrue
A       15-05-2015 02:00        0
B       15-05-2015 03:00        1
C       15-05-2015 06:00        0
D       15-05-2015 13:00        0
E       15-05-2015 23:00        0
F       16-05-2015 04:00        0
E       16-05-2015 07:00        1
G       16-05-2015 23:00        0

I am trying to write a query whose output should be something like this

NAME    TIME                    IsTrue
A       15-05-2015 02:00        0
B       15-05-2015 03:00        1
C       15-05-2015 06:00        1
D       15-05-2015 13:00        1
E       15-05-2015 23:00        1
F       16-05-2015 04:00        0
E       16-05-2015 07:00        1
G       16-05-2015 23:00        1

Here you can observe that value of IsTrue is set on the condition that value of any row in last 24 hours is 1.

I don't want to make changes in the table itself but just return the value. Could anybody help?

Thanks in advance,

Regards, Ganesh.

P.S. I'm using Microsoft SQL Server if that matters.

Upvotes: 0

Views: 203

Answers (3)

Deepshikha
Deepshikha

Reputation: 10264

You can write as:

-- now get all records with desired results
SELECT DISTINCT T3.NAME
      ,T3.[Time]
      ,case when T4.NAME  IS NULL THEN 0 ELSE 1 END AS IsTrue
FROM  @Test T3 
LEFT  JOIN 
(
-- get all rows for which Istrue condition holds true 
SELECT T1.NAME,T1.[Time]
FROM @Test T1
WHERE EXISTS ( SELECT IsTrue 
               FROM @Test T2
               WHERE DATEDIFF (hh,T2.Time,T1.Time) BETWEEN 0 AND 24 AND T2.IsTrue = 1)
) T4  ON T3.NAME = T4.NAME AND T3.[Time] = T4.[Time]  
ORDER BY T3.[Time]

DEMO

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

This is simplest, but performance-wise sub-optimal solution. It uses correlated sub-query to calculate on each row:

SELECT 
    [NAME]
  , [TIME]
  , (SELECT MAX(IsTrue+0) FROM TABLE1 it WHERE DATEADD(DD,1,it.TIME) > ot.TIME AND it.TIME <= ot.Time) [ISTRUE]
FROM Table1 ot

I don't how much data you have. It will work well enough with this sample, but if you have millions of rows, we might need to look for something better.

SQLFiddle DEMO

Note the +0 in MAX(IsTrue) to enable use aggregate functions on bit column

Upvotes: 1

Dhaval
Dhaval

Reputation: 2379

Try this 

with cte as
(
Select Row_Number() over(Partition by convert(date,Time) order by Time)as Rno,Name,Istrue,time from test
)
select Rno,Name,Time,Case when Rno>1 then 1 else 0 end as Istrue  from cte

Please Check Working DEMO

Upvotes: 1

Related Questions