SQL Server : Table Variable Update

Question

I am trying to create a table variable in SQL Server that contains 3 columns of data that have been grouped by quarter. I have succeeded in selecting the appropriate data for each of the 3 columns but do not know how to update the contents of the table variable with each additional query.

Therefore, rather than having a table with 4 rows (one for each quarter) and 3 columns of data, I have 12 rows (4 quarters duplicated 3 times) and lots of nulls in the data columns.

How can I create the 4 row only version of the table with no NULLS?

Query:

DECLARE @temp TABLE (oYear INT, oQuarter INT, chin FLOAT, press FLOAT, pull FLOAT)
DECLARE @sDate AS DATE, @eDate AS DATE, @athName AS VARCGAR(50), 
        @chin1RM AS FLOAT, @press1RM AS FLOAT, @pull1RM AS FLOAT;

SET @sDate = '2016-10-01';
SET @eDate = '2017-10-01';

SET @athName = 'Steve';

INSERT INTO @temp (oYear, oQuarter, chin)
    SELECT 
        DATEPART(YEAR, Day.Date), 
        DATEPART(QUARTER, Day.Date), 
        MAX(Pred1RMBMF) 
    FROM 
        GymLoad 
    JOIN 
        Session ON Session.id = GymLoad.SessionID 
    JOIN 
        Day ON Day.id = Session.DayID 
    JOIN 
        Athlete ON Athlete.id = Day.AthleteID 
    WHERE 
        (ExerciseID = 200 OR ExerciseID = 198) 
        AND FullName = @athName 
        AND Day.Date >= @sDate 
        AND Day.Date <= @eDate 
    GROUP BY 
        DATEPART(YEAR,Day.Date), DATEPART(QUARTER,Day.Date);

INSERT INTO @temp (oYear, oQuarter, press)
    SELECT 
        DATEPART(YEAR, Day.Date), 
        DATEPART(QUARTER, Day.Date), 
        MAX(Pred1RMBMF) 
    FROM 
        GymLoad
    JOIN 
        Session ON Session.id = GymLoad.SessionID 
    JOIN 
        Day ON Day.id = Session.DayID 
    JOIN 
        Athlete ON Athlete.id = Day.AthleteID 
    WHERE 
        (ExerciseID = 164 OR ExerciseID = 174) 
        AND FullName = @athName 
        AND Day.Date >= @sDate  
        AND Day.Date <= @eDate 
    GROUP BY 
        DATEPART(YEAR, Day.Date), DATEPART(QUARTER, Day.Date);

INSERT INTO @temp (oYear, oQuarter, pull)
    SELECT 
        DATEPART(YEAR, Day.Date), 
        DATEPART(QUARTER, Day.Date), 
        MAX(Pred1RMBMF) 
    FROM 
        GymLoad
    JOIN 
        Session ON Session.id = GymLoad.SessionID 
    JOIN 
        Day ON Day.id = Session.DayID 
    JOIN 
        Athlete ON Athlete.id = Day.AthleteID 
    WHERE 
        (ExerciseID = 183 OR ExerciseID = 183) 
        AND FullName = @athName 
        AND Day.Date >= @sDate  
        AND Day.Date <= @eDate 
    GROUP BY 
        DATEPART(YEAR, Day.Date), DATEPART(QUARTER, Day.Date);

SELECT * FROM @Temp

Current returned result set from the query:

oYear   oQuarter    chin    press   pull
------------------------------------------
2017    1          104.02   NULL    NULL
2017    2          102.22   NULL    NULL
2017    3          100.54   NULL    NULL
2016    4          104.3    NULL    NULL
2017    1          NULL     81.61   NULL
2017    2          NULL     71.26   NULL
2017    3          NULL     75.86   NULL
2016    4          NULL     80      NULL
2017    1          NULL     NULL    91.95
2017    2          NULL     NULL    68.97
2017    3          NULL     NULL    73.75
2016    4          NULL NULL    85.33

Any help would be most appreciated.

Answer 1

Aggregate by year and quarter and use an aggregate function (e.g. MAX) on the data columns to remove the nulls.

SELECT
    oYear,
    oQuarter,
    MAX(chin) AS chin,
    MAX(press) AS press,
    MAX(pull) AS pull
FROM @Table
GROUP BY
    oYear,
    oQuarter;