每周日期小组

发布于 2025-01-31 08:04:18 字数 543 浏览 2 评论 0原文

我想对数据进行分组，其中每周的日期存储在不同的表格中。我可以通过datepart进行每周的日期，然后我可以每周进行分组，但是问题是每周的日期，我一直在周六某个星期开始，这是我一直在改变的某个时候。。例如：

2022-03-27 00:00:00.000
2022-04-03 00:00:00.000
2022-04-10 00:00:00.000
2022-04-17 00:00:00.000
2022-04-24 00:00:00.000
2022-04-30 00:00:00.000
2022-05-08 00:00:00.000
2022-05-15 00:00:00.000
2022-05-22 00:00:00.000

以上日期是应生成报告的周日期。例如：从2022-03-27 00：00：00：00.000到2022-04-03 00：00：00：00.000它将是报告中的一排。 00：00：00.000。这将是第二行，依此类推。

我可以通过在日期使用datePart，然后计算一周的一周来做到这一点，但是由于我的日期，我的日期并不统一

。有建议吗？

原文

I want to group data weekly where weekly dates are stored in different table.I can do it by doing datepart and then i can group them by week but the problem is weekly dates which i have are keep changing sometime the week starts from saturday sometime sunday.
for example :

2022-03-27 00:00:00.000
2022-04-03 00:00:00.000
2022-04-10 00:00:00.000
2022-04-17 00:00:00.000
2022-04-24 00:00:00.000
2022-04-30 00:00:00.000
2022-05-08 00:00:00.000
2022-05-15 00:00:00.000
2022-05-22 00:00:00.000

The above dates are week dates on which report should be generated ..
for example : from 2022-03-27 00:00:00.000 to 2022-04-03 00:00:00.000 it will be one row in report .from 2022-04-03 00:00:00.000 to 2022-04-10 00:00:00.000 .it will be second row and so on.

I am able to do it by using datepart on date and then calculating week of year but due to dates in my case are not uniform its missing data for some dates ..

How can i achieve that ? any advice ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流心雨 2025-02-07 08:04:18

听起来好像是任意日期的一周是该日期或之前的几周表中的最新日期。

因此，您可以做这样的事情

SELECT d.TheDate, w.StartOfWeek
FROM TheData d
    INNER JOIN (
        -- Make a lookup table.
        SELECT d.TheDate, MAX(w.StartOfWeek) AS StartOfWeek
        FROM TheData d
            INNER JOIN Weeks w 
              ON d.TheDate >= w.StartOfWeek
        GROUP BY d.TheDate
    ) w
        ON d.TheDate = w.TheDate

，假设Weeks都包含整个星期，并且没有任何差距...

Sounds like the week of an arbitrary date is the latest date in the weeks table that is on or before that date.

So you could do something like this

SELECT d.TheDate, w.StartOfWeek
FROM TheData d
    INNER JOIN (
        -- Make a lookup table.
        SELECT d.TheDate, MAX(w.StartOfWeek) AS StartOfWeek
        FROM TheData d
            INNER JOIN Weeks w 
              ON d.TheDate >= w.StartOfWeek
        GROUP BY d.TheDate
    ) w
        ON d.TheDate = w.TheDate

That's assumin that Weeks contains all weeks and doesn't have any gaps...

回复收藏 0 原文

~没有更多了~