• paul.s.lach (7/30/2015)


    Try the following, the concept is:

    1. do a cross join of all courses and terms

    2. left join the results of the cross join to the marks you supplied such that every mark for every course appears in every term from the term where taken forward

    3. use ROW_NUMBER() to determine the highest mark

    4. calculate the GPA

    IF OBJECT_ID('tempdb..#GPA') IS NOT NULL DROP TABLE #GPA;

    CREATE TABLE [#GPA]([term] int,

    [course] varchar(10),

    [mark] float);

    INSERT INTO [#GPA]

    VALUES (1,'math',3),

    (1,'eng',4),

    (1,'hist',4.5),

    (1,'anth',4.5),

    (2,'comp',3),

    (2,'math',4),

    (2,'bio',4.5),

    (2,'chem',4.5),

    (3,'soc',4.5),

    (3,'math',4.5),

    (3,'bio',4),

    (3,'chem',4);

    WITH [Courses] ([course])

    AS (SELECT DISTINCT [course]

    FROM [#GPA]

    ),

    [Terms] ([term])

    AS (SELECT DISTINCT [term]

    FROM [#GPA]

    ),

    [CourseTerms] ([term], [course])

    AS (SELECT [t].[term], [c].[course]

    FROM [Courses] AS c CROSS JOIN [Terms] AS t

    ),

    [Marks] ([term], [course], [mark])

    AS (SELECT [ct].[term], [ct].[course], [g1].[mark]

    FROM [CourseTerms] AS ct LEFT OUTER JOIN [#GPA] AS [g1] ON [g1].[term] <= [ct].[term]

    AND [g1].[course] = [ct].[course]

    ),

    [OrderedMarks] ([term], [course], [mark], SeqNo)

    AS (

    SELECT [m].[term], [m].[course], [m].[mark]

    , ROW_NUMBER() OVER (PARTITION BY [m].[term], [m].[course] ORDER BY [m].[mark] DESC) AS [SeqNo]

    FROM [Marks] AS m

    ),

    [BestMarks] ([term], [course], [mark])

    AS (

    SELECT [om].[term], [om].[course], [om].[mark]

    FROM [OrderedMarks] AS om

    WHERE [om].[SeqNo] = 1

    )

    SELECT [bm].[term]

    , SUM(CASE WHEN [bm].[mark] IS NOT NULL THEN [bm].[mark] ELSE 0 END) / CAST(SUM(CASE WHEN [bm].[mark] IS NOT NULL THEN 1 ELSE 0 END) AS FLOAT) AS [GPA]

    FROM [BestMarks] AS bm

    GROUP BY [bm].[term]

    ORDER BY [bm].[term];

    Thanks Paul, this solution does give the correct results.

    The solution provided by Jacob earlier, which also give correct results, would probably perform better on a larger scale. Jacob's solution does 4 reads to the source table while your solution does 28 for the sample data.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]