CODEWITHSUNDEEP
sqlsql-server
user9930055
user9930055

Reputation:

Correlated Sub query gone wrong

I have a table with 2 columns: Horse and RaceDate. I would like to create a third column that shows for each Horse the number of Racedates that are within 30 days of each RaceDate, that is within 30 days less than each racedate. I'm trying to do this by a correlated sub query but coz i'm comparing one field against a selection of 2 it obviously won't work. I really don't want to revert to treating it as a cursor, is there a solution to this? enter image description here

select I.Last30Days,O.Horse,O.RaceDate from Rep2.AuditTrail O
where dateadd(d,-30,O.Racedate) > select I.RaceDate, count(I.RaceDate) as 
Last30Days
from Rep2.AuditTrail I
where I.Horse = O.Horse
group by I.RaceDate

Upvotes: 1

Views: 56

Answers (2)

D-Shih
D-Shih

Reputation: 46219

If I understand correctly, you can use CROSS APPLY to make it.

create table AuditTrail (
  Horse varchar(50),
  Racedate date
);

INSERT INTO AuditTrail VALUES ('AA','2018-01-01');
INSERT INTO AuditTrail VALUES ('AA','2018-02-01');
INSERT INTO AuditTrail VALUES ('AA','2018-03-08');
INSERT INTO AuditTrail VALUES ('AA','2018-04-01');

Query 1:

select O.Horse,O.Racedate,COUNT(Last30Days) Last30Days
from Rep2.AuditTrail O 
CROSS APPLY (
    select I.RaceDate as Last30Days
    from Rep2.AuditTrail I
    where I.Horse = O.Horse AND  DATEDIFF(DAY,i.RaceDate,O.RaceDate)>30
) t1
group by O.Horse,O.Racedate

Results:

| Horse |   Racedate | Last30Days |
|-------|------------|------------|
|    AA | 2018-02-01 |          1 |
|    AA | 2018-03-08 |          2 |
|    AA | 2018-04-01 |          2 |

Upvotes: 2

Vinit
Vinit

Reputation: 2607

Try this query, using sub query in Select

select 
  O.Horse,
  O.RaceDate, 
  ( Select Count(*) 
    From AuditTrail 
    Where Horse=O.Horse 
      and DATEDIFF(DAY,RaceDate,O.RaceDate)<=30 ) as Last30Days
from Rep2.AuditTrail O

Upvotes: 1

Related Questions