SQL Server SQL filling missing date entries, and including previous date's counts

gv8xihay 于 2023-10-15 发布在其他

关注(0)|答案(3)|浏览(85)

I have a table as follows
| Date | Id | Group | Name | ScoreCount |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 2022-06-20 | 1 | Athlete | Adam | 52 |
| 2022-06-23 | 1 | Athlete | Adam | 77 |
| 2022-06-25 | 1 | Athlete | Adam | 79 |
| 2022-06-19 | 1 | Employee | Adam | 65 |
| 2022-06-22 | 1 | Employee | Adam | 28 |

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

Date	Id	Group	Name	ScoreCount
2022-06-20	1	Athlete	Adam	52
2022-06-21	1	Athlete	Adam	52
2022-06-22	1	Athlete	Adam	52
2022-06-23	1	Athlete	Adam	77
2022-06-24	1	Athlete	Adam	77
2022-06-25	1	Athlete	Adam	79
2022-06-19	1	Employee	Adam	65
2022-06-20	1	Employee	Adam	65
2022-06-21	1	Employee	Adam	65
2022-06-22	1	Employee	Adam	28

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?

sql-server

来源：https://stackoverflow.com/questions/72732318/sql-filling-missing-date-entries-and-including-previous-dates-counts

3条答案

按热度按时间

8fsztsew1#

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.

赞(0）回复(0）举报 2023-10-15

uujelgoq2#

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

赞(0）回复(0）举报 2023-10-15

eyh26e7m3#

I came across post since I had a similar issue. The only difference is that I needed to get multiple values to be filled. I dont think either of the solutions work for me since I would need multiple selects.

This is what I came up with using rownumber

with CTE as 
(
   Select
      c.days Date,
      r.Id,
      r.Group,
      r.Name,
      r.ScoreCount,
      rn = row_number() over ( Partition by r.Id, c.days ORDER BY r.Date
   FROM calendar c
   LEFT JOIN recordTable r on r.Date <= c.days
)
SELECT * FROM CTE WHERE rn = 1

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL filling missing date entries, and including previous date's counts

3条答案

相关问题

热门标签

最新问答