BWT try this.

SELECT

P

.HH_ID,

(SELECT TOP 1 S.Student_ID

FROM...

Gilamonster - this will work in most cases.  There is however a problem where the there may be more than one record per HH_ID and Grade combination on the join. ...

Going down the route of adding the date and time together for comparision is fine, HOWEVER SQL server will not make effective use of indexes that are defined for the...

I can see your problem.  There are three parts to your data. 

1) That between date1+1day and date2  (date1 is the date part not including time, note date1 < date2)

2&3)...