I have a table as follows

I'd like this for the dates to be added for each individual id and type of group. So it should look something like:

My code is as follows:

WITH t as (SELECT
Id,
Group,
Name,
min(Date) as MinDate
max(Date) as MaxDate
FROM recordTable
GROUP BY Id,Group,Name

SELECT t.Id,
 t.Group,
 t.Name,
 c.Days,
 (SELECT LAST_VALUE(ScoreCount) FROM recordTable WHERE t.Id = recordTable.Id AND t.Group = recordTable.Group)
FROM t
LEFT JOIN calendar c ON c.Days BETWEEN t.MinDate AND t.MaxDate

calendar is the table that contains individual dates for the year 2022, so they can be joined. Everything works, except for the ScoreCount, which Last_Value isn't actually doing what I want it to do. How can I fix this?

CodePudding user response：

You can simply try reversing the order of your joined tables -

WITH t as (SELECT Id,
                  Group,
                  Name,
                  min(Date) as MinDate,
                  max(Date) as MaxDate
             FROM recordTable
            GROUP BY Id,Group,Name
          )
SELECT t.Id,
       t.Group,
       t.Name,
       c.Days,
       (SELECT LAST_VALUE(ScoreCount) OVER(<your over clause is missing>)
          FROM recordTable 
         WHERE t.Id = recordTable.Id
           AND t.Group = recordTable.Group)
  FROM calendar c
  LEFT JOIN t ON c.Days BETWEEN t.MinDate AND t.MaxDate

Although I have not tested the query yet this will give you an idea to proceed further.

CodePudding user response：

You don't need the last_value, you can get the first value

WITH t as (
SELECT
[Id],
[Group],
[Name],
min([Date]) as MinDate,
max([Date]) as MaxDate
FROM recordTable
GROUP BY [Id],[Group],[Name]
)

SELECT 
 t.Id,
 t.[Group],
 t.[Name],
 c.[Date],
 (SELECT top 1 ScoreCount 
        from recordTable x 
        where x.[Date] <= c.[Days]
        and  x.[Group] = t.[Group] 
        and x.[Name] = t.[Name]
        order by x.[Date] desc
) ScoreCount
FROM t
LEFT JOIN calendar c ON c.[Days] BETWEEN t.MinDate AND t.MaxDate