CODEWITHSUNDEEP
sqlsql-serverdatabase
SpanishBoy
SpanishBoy

Reputation: 2215

Join 2 tables by date intersecting ways

I need to join 2 tables an interesting way for me, please see details below. Table PROGRAMS:

STATION_INT | PROGRAM_NAME | AIR_DATE | START_TIME | END_TIME | SEQ_NUM
========================================================================
          1      '70S SHOW '  5/6/2015    32405000   32410000         1
          1      '80S SHOW '  5/6/2015    32415000   34300000         1  
........................................................................

Also I have a second table Ratings

STATION_INT | IMP_DATE | IMP_START_TIME | IMP_END_TIME | IMP_STATUS |   HH | F2_5   
==================================================================================
         1     5/6/2015        32400000       34199000           S    1422   8464
         1     5/6/2015        32400000       34199000           F    1677   9000
         1     5/6/2015        34199000       34310000           S    1522   9160
...................................................................................

Resulting table (PROGRAMS * RATINGS) should be like:

STATION_INT | PROGRAM_NAME | AIR_DATE | START_TIME | END_TIME | SEQ_NUM | IMP_DATE | IMP_START_TIME | IMP_END_TIME | HH_S | HH_F | F2_5_S | F2_5_F
========================================================================================================================================================
         1       '70S SHOW '  5/6/2015    32405000    32410000        1   5/6/2015         32400000       34199000   1422   1677     8464     9000    
         1       '80S SHOW '  5/6/2015    32415000    34300000        1   5/6/2015         32400000       34310000 (1422+1522)/2  --- (8464+9160)/2  ---

I have no clue how to calculate it in SQL... Please advise

Upvotes: 4

Views: 93

Answers (2)

Ionic
Ionic

Reputation: 3935

As I'm still not 100% sure of the question, I'll try my best with this answer.

SELECT p.station_int, p.program_name, p.air_date, p.start_Time, p.end_time, p.seq_Num, AVG(r.hh)
FROM programs as p
INNER JOIN ratings as r
    ON r.imp_date = p.imp_date
    AND (r.imp_start_time >= p.start_time
        AND r.imp_start_time <= p.end_time)
    AND (r.imp_end_time <= p.end_time
        AND r.imp_end_time >= p.start_time)
GROUP BY p.station_int, p.program_name, p.air_date, p.start_Time, p.end_time, p.seq_Num

Maybe this will do the thing your looking for.

Upvotes: 1

ughai
ughai

Reputation: 9890

You can use CROSS APPLY with conditional aggregation like this.

Query

SELECT * 
FROM PROGRAMS as P
CROSS APPLY (
SELECT 
    AVG(CASE WHEN R.IMP_STATUS = 'F' THEN HH END) as AVG_F_HH,
    AVG(CASE WHEN R.IMP_STATUS = 'F' THEN F2_5 END) as AVG_F_F2_5,
    AVG(CASE WHEN R.IMP_STATUS = 'S' THEN HH END) as AVG_S_HH,
    AVG(CASE WHEN R.IMP_STATUS = 'S' THEN F2_5 END) as AVG_S_F2_5
FROM Ratings as R 
WHERE R.IMP_DATE = P.AIR_DATE AND R.STATION_INT = P.STATION_INT
AND NOT
(R.IMP_START_TIME >= P.END_TIME OR P.START_TIME >= R.IMP_END_TIME)
) as R

Output

STATION_INT PROGRAM_NAME    AIR_DATE    START_TIME  END_TIME    SEQ_NUM AVG_F_HH    AVG_F_F2_5  AVG_S_HH    AVG_S_F2_5
1   70S SHOW    May, 06 2015 00:00:00   32405000    32410000    1   1677    9000    1422    8464
1   80S SHOW    May, 06 2015 00:00:00   32415000    34300000    2   1677    9000    1472    8812

SQL Fiddle

Upvotes: 1

Related Questions