英文:
SQL Counter with condition
问题
我在SQL表中存储了一个年度日历，每天（日期）都有两个标记：假日和周末。
当特定日期是假日时，假日条目为'1'
当特定日期是周末时，周末条目为'1'
当特定日期既是假日又是周末时，假日和周末条目都为'1'
现在，我正在尝试做以下事情：
- 计算每个月的工作日数量
- 如果日期是假日、周末或两者都是，那一天的计数应该等于前一天的日期
以下是示例：
| 日期 | 年份 | 月份 | 日 | 是否周末 | 是否假日 | 计数 |
|---|---|---|---|---|---|---|
| 2023.01.01 | 2023 | 1 | 1 | 1 | 1 | 0 |
| 2023.01.02 | 2023 | 1 | 2 | 0 | 1 | 0 |
| 2023.01.03 | 2023 | 1 | 3 | 0 | 0 | 1 |
| 2023.01.04 | 2023 | 1 | 4 | 0 | 0 | 2 |
| 2023.01.05 | 2023 | 1 | 5 | 0 | 0 | 3 |
| 2023.01.06 | 2023 | 1 | 6 | 0 | 0 | 4 |
| 2023.01.07 | 2023 | 1 | 7 | 1 | 0 | 4 |
| 2023.01.08 | 2023 | 1 | 8 | 1 | 0 | 4 |
| 2023.01.09 | 2023 | 1 | 9 | 0 | 0 | 5 |
| 2023.01.10 | 2023 | 1 | 10 | 0 | 0 | 5 |
到目前为止，我尝试过使用ROW_NUMBER()和设置@Counter，但没有成功。
英文:
I have a yearly calendar stored in an sql table amd each day (date) has two markers: Holiday & Weekend.
When the specific date is a holiday, the Holiday entry is '1'
When the specific date is a weekend, the Weekend entry is '1'
When the specific date is a holiday & weekend, both Holiday and Weekend entries are '1'
Now, what I am trying to do is the followig:
- Count number of working days in each month
- if date is holiday, weekend or both, the count of that day should be equal to the date before
Below is an example:
| Date | Year | Month | Day | IsWeekend | IsHoliday | Counter |
|---|---|---|---|---|---|---|
| 2023.01.01 | 2023 | 1 | 1 | 1 | 1 | 0 |
| 2023.01.02 | 2023 | 1 | 2 | 0 | 1 | 0 |
| 2023.01.03 | 2023 | 1 | 3 | 0 | 0 | 1 |
| 2023.01.04 | 2023 | 1 | 4 | 0 | 0 | 2 |
| 2023.01.05 | 2023 | 1 | 5 | 0 | 0 | 3 |
| 2023.01.06 | 2023 | 1 | 6 | 0 | 0 | 4 |
| 2023.01.07 | 2023 | 1 | 7 | 1 | 0 | 4 |
| 2023.01.08 | 2023 | 1 | 8 | 1 | 0 | 4 |
| 2023.01.09 | 2023 | 1 | 9 | 0 | 0 | 5 |
| 2023.01.10 | 2023 | 1 | 10 | 0 | 0 | 5 |
So far, I have tried ROW_NUMBER() and setting a @Counter but with no luck
答案1
得分: 1
COUNT(CASE WHEN IsHoliday = 0 AND IsWeekend = 0 THEN 1 END) OVER(ORDER BY "Date"):
在"Date"字段排序的情况下，统计IsHoliday等于0且IsWeekend等于0的数量。
英文:
COUNT(CASE WHEN IsHoliday = 0 AND IsWeekend = 0 THEN 1 END) OVER(ORDER BY "Date")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论