Totals with Cross Apply

Question

I have 2 tables below and need get totals per each row from table 1 as new columns

Table Programs:

AIR_DATE              START_TIME    END_TIME
-------------------------------------------
1/26/2015 12:00:00 AM   27000000    28800000
1/26/2015 12:00:00 AM   28800000    32400000
1/26/2015 12:00:00 AM   34200000    34280000

Table Ratings:

IMPRESSION_DATE   IMP_START_TIME    IMP_END_TIME  HH    F2_5  IMP_STATUS
--------------------------------------------------------------------
1/26/2015 12:00:00 AM   27000000    27899000      8685  4415   F
1/26/2015 12:00:00 AM   27900000    28799000      8690  4421   F 
1/26/2015 12:00:00 AM   28800000    29699000      9900  4410   F
1/26/2015 12:00:00 AM   29700000    30599000      9906  4414   F
1/26/2015 12:00:00 AM   30600000    31499000      6925  2580   F
1/26/2015 12:00:00 AM   31500000    32399000      6928  2588   F
1/26/2015 12:00:00 AM   32400000    33299000      9988  17     F
1/26/2015 12:00:00 AM   33300000    34199000      9998  19     F
1/26/2015 12:00:00 AM   34200000    35099000       781  2457   F

I am looking for how to get target result set as:

AIR_DATE    START_TIME  END_TIME  IMP_DATE IMP_START_TIME  IMP_END_TIME  HH_F  F2_5_F
1/26/2015   27000000    28800000  1/26/2015      27000000   28799000  8687.5   4418
1/26/2015   28800000    30600000  1/26/2015     28800000    30599000 8414.75   3498
1/26/2015   34200000    34280000  1/26/2015     34200000    35099000     781   2457

My example of query, but It didn't help me:)

SELECT *
FROM PROGRAMS as P
    CROSS APPLY (
           SELECT 
              MIN(CASE WHEN R.IMP_STATUS = 'F' THEN IMP_START_TIME END) as IMP_START_TIME,
              MAX(CASE WHEN R.IMP_STATUS = 'F' THEN IMP_END_TIME END) as IMP_END_TIME,

              AVG(CASE WHEN R.IMP_STATUS = 'F' THEN HH END) as HH_F,
              AVG(CASE WHEN R.IMP_STATUS = 'F' THEN F2_5 END) as F2_5_F
            FROM RATINGS as R  
            WHERE R.IMPRESSION_DATE = P.AIR_DATE 
              AND R.IMP_START_TIME <= P.START_TIME 
              AND P.END_TIME <= R.IMP_END_TIME) as R

RDBS: MS SQL SERVER 2008 R2 SQL Fiddler example here: http://sqlfiddle.com/#!3/68fa5/2

Answer 1

If you change the time limiting condition in the cross apply to:

WHERE R.IMPRESSION_DATE = P.AIR_DATE 
  AND 
  (
    (R.IMP_START_TIME >= P.START_TIME AND R.IMP_END_TIME <= P.END_TIME) 
    OR
    (R.IMP_START_TIME <= P.START_TIME AND R.IMP_END_TIME >= P.END_TIME)
  )

you would get a result like:

AIR_DATE    START_TIME  END_TIME    IMP_START_TIME  IMP_END_TIME        HH_F    F2_5_F
2015-01-26 00:00:00.000 27000000    28800000    27000000    28799000    8687    4418
2015-01-26 00:00:00.000 28800000    32400000    28800000    32399000    8414    3498
2015-01-26 00:00:00.000 34200000    34280000    34200000    35099000    781     2457

which looks to be correct :)