英文:
Query to retrieve incomplete records by month
问题
在给定的Oracle数据库示例数据中：
| ID | Step | StartDate | CompDate |
| ----- | ---- | -------- | --------- |
| 98775 | 4.24 | 28-Jun-23 | |
| 98776 | 4.31 | 28-Jun-23 | 5-Jul-23 |
| 98777 | 4.24 | 28-Jun-23 | |
| 98845 | 4.31 | 26-Jul-23 | 2-Aug-23 |
| 98846 | 4.31 | 27-Jul-23 | 27-Jul-23 |
| 98847 | 4.24 | 31-Jul-23 | |
| 98848 | 4.31 | 31-Jul-23 | 2-Aug-23 |
| 98853 | 4.31 | 3-Aug-23 | 3-Aug-23 |
| 98854 | 4.24 | 3-Aug-23 | |
| 98855 | 4.23 | 3-Aug-23 | |
您需要一个查询，以按月返回剩余记录。查询应返回以下值：
为了解释我需要什么，在上面的示例数据中，6月期间创建了3条记录。在这3条记录中，没有一条在6月份完成，所以6月份的剩余记录数为3。在7月份，创建了4条记录，1条来自6月，1条来自7月完成。这意味着在7月底，从6月和7月创建的记录中仍有5条未完成。8月份，创建了3条记录，2条来自7月完成，1条来自8月完成。8月份的剩余计数为5。我需要每个月尚未完成的任务的运行总数。
|ColumnName|Records|
|----------|-------|
|2023-06|3|
|2023-07|5|
|2023-08|5|
我已经尝试过这个查询，但计数不正确：
WITH record_creations AS (
SELECT
TO_CHAR(startdate, 'YYYY-MM') AS month,
COUNT(id) AS created_records
FROM
table1
WHERE id in (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
GROUP BY
TO_CHAR(startdate, 'YYYY-MM')
),
records_incomplete AS (
SELECT
TO_CHAR(compdate, 'YYYY-MM') AS month,
COUNT(id) AS remaining_records
FROM
table1
WHERE
compdate IS NULL
and id in (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
GROUP BY
TO_CHAR(compdate, 'YYYY-MM')
)
SELECT
NVL(c.month, i.month) AS ColumnName,
NVL(created_records, 0) - NVL(remaining_records, 0) AS records
FROM
record_creations c
FULL OUTER JOIN records_incomplete i ON c.month = i.month
ORDER BY
NVL(c.month, i.month)
这个查询返回以下结果：
|ColumnName|Records|
|----------|-------|
|2023-06|3|
|2023-07|4|
|2023-08|3|
如果您想要修复这个查询以获得正确的计数，请让我知道，我可以提供更多帮助。
英文:
Given this sample data in an Oracle database:
| ID | Step | StartDate | CompDate |
|---|---|---|---|
| 98775 | 4.24 | 28-Jun-23 | |
| 98776 | 4.31 | 28-Jun-23 | 5-Jul-23 |
| 98777 | 4.24 | 28-Jun-23 | |
| 98845 | 4.31 | 26-Jul-23 | 2-Aug-23 |
| 98846 | 4.31 | 27-Jul-23 | 27-Jul-23 |
| 98847 | 4.24 | 31-Jul-23 | |
| 98848 | 4.31 | 31-Jul-23 | 2-Aug-23 |
| 98853 | 4.31 | 3-Aug-23 | 3-Aug-23 |
| 98854 | 4.24 | 3-Aug-23 | |
| 98855 | 4.23 | 3-Aug-23 |
I need a query that will return the remaining records by month. The query should return values like this:
To explain what I need, in the sample data above, 3 records were created during June. Of those 3 records, 0 were completed during June, so the remaining records count for June is 3. In July, 4 records were created, 1 from June and 1 from July were completed. This means that, at the end of July, 5 records remained from the ones created during June and July. In August, 3 records were created, 2 from July were completed, and 1 from August was completed. The remaining count for August is 5. I need a running total of tasks not yet completed for each month.
| ColumnName | Records |
|---|---|
| 2023-06 | 3 |
| 2023-07 | 5 |
| 2023-08 | 5 |
I've tried this query but the counts are off:
WITH record_creations AS (
SELECT
TO_CHAR(startdate, 'YYYY-MM') AS month,
COUNT(id) AS created_records
FROM
table1
WHERE id in (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
GROUP BY
TO_CHAR(startdate, 'YYYY-MM')
),
records_incomplete AS (
SELECT
TO_CHAR(compdate, 'YYYY-MM') AS month,
COUNT(id) AS remaining_records
FROM
table1
WHERE
compdate IS NULL
and id in (98775,98776,98777,98845,98846,98847,98848,98853,98854,98855)
GROUP BY
TO_CHAR(compdate, 'YYYY-MM')
)
SELECT
NVL(c.month, i.month) AS ColumnName,
NVL(created_records, 0) - NVL(remaining_records, 0) AS records
FROM
record_creations c
FULL OUTER JOIN records_incomplete i ON c.month = i.month
ORDER BY
NVL(c.month, i.month)
This query returns this:
| ColumnName | Records |
|---|---|
| 2023-06 | 3 |
| 2023-07 | 4 |
| 2023-08 | 3 |
答案1
得分: 0
你可以使用`UNPIVOT`操作来将日期进行转换，然后计算每个月开始的数量减去完成的数量的累积总数：
```sql
SELECT DISTINCT
TO_CHAR(dt, 'YYYY-MM') AS 月份,
SUM(active) OVER (ORDER BY TO_CHAR(dt, 'YYYY-MM')) AS 未完成数量
FROM (
SELECT startdate, compdate
FROM table_name
)
UNPIVOT (dt FOR active IN (startdate AS 1, compdate AS -1))
ORDER BY 月份;
对于示例数据：
CREATE TABLE table_name (ID, Step, StartDate, CompDate) AS
SELECT 98775, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98776, 4.31, DATE '2023-06-28', DATE '2023-07-05' FROM DUAL UNION ALL
SELECT 98777, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98845, 4.31, DATE '2023-07-26', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98846, 4.31, DATE '2023-07-27', DATE '2023-07-27' FROM DUAL UNION ALL
SELECT 98847, 4.24, DATE '2023-07-31', NULL FROM DUAL UNION ALL
SELECT 98848, 4.31, DATE '2023-07-31', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98853, 4.31, DATE '2023-08-03', DATE '2023-08-03' FROM DUAL UNION ALL
SELECT 98854, 4.24, DATE '2023-08-03', NULL FROM DUAL UNION ALL
SELECT 98855, 4.23, DATE '2023-08-03', NULL FROM DUAL;
输出结果：
| 月份 | 未完成数量 |
|---|---|
| 2023-06 | 3 |
| 2023-07 | 5 |
| 2023-08 | 5 |
<details>
<summary>英文:</summary>
You can `UNPIVOT` the dates and then calculate a running total of the number started minus the number completed for each month:
```lang-sql
SELECT DISTINCT
TO_CHAR(dt, 'YYYY-MM') AS month,
SUM(active) OVER (ORDER BY TO_CHAR(dt, 'YYYY-MM')) AS outstanding
FROM (
SELECT startdate, compdate
FROM table_name
)
UNPIVOT (dt FOR active IN (startdate AS 1, compdate AS -1))
ORDER BY month;
Which, for the sample data:
CREATE TABLE table_name (ID, Step, StartDate, CompDate) AS
SELECT 98775, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98776, 4.31, DATE '2023-06-28', DATE '2023-07-05' FROM DUAL UNION ALL
SELECT 98777, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98845, 4.31, DATE '2023-07-26', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98846, 4.31, DATE '2023-07-27', DATE '2023-07-27' FROM DUAL UNION ALL
SELECT 98847, 4.24, DATE '2023-07-31', NULL FROM DUAL UNION ALL
SELECT 98848, 4.31, DATE '2023-07-31', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98853, 4.31, DATE '2023-08-03', DATE '2023-08-03' FROM DUAL UNION ALL
SELECT 98854, 4.24, DATE '2023-08-03', NULL FROM DUAL UNION ALL
SELECT 98855, 4.23, DATE '2023-08-03', NULL FROM DUAL;
Outputs:
| MONTH | OUTSTANDING |
|---|---|
| 2023-06 | 3 |
| 2023-07 | 5 |
| 2023-08 | 5 |
答案2
得分: 0
以下是翻译好的内容：
可以使用递归查询来生成所有的月份，然后计算在每个月底之前已经开始但尚未完成的项目数量：
```lang-sql
WITH months (month, endmonth) AS (
SELECT MIN(TRUNC(startdate, 'MM')),
MAX(TRUNC(compdate, 'MM'))
FROM table_name
UNION ALL
SELECT ADD_MONTHS(month, 1),
endmonth
FROM months
WHERE month < endmonth
)
SELECT TO_CHAR(month, 'YYYY-MM') AS month,
( SELECT COUNT(*)
FROM table_name
WHERE startdate < ADD_MONTHS(m.month, 1)
AND ( compdate >= ADD_MONTHS(m.month, 1)
OR compdate IS NULL )
) AS outstanding
FROM months m
对于样本数据：
CREATE TABLE table_name (ID, Step, StartDate, CompDate) AS
SELECT 98775, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98776, 4.31, DATE '2023-06-28', DATE '2023-07-05' FROM DUAL UNION ALL
SELECT 98777, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98845, 4.31, DATE '2023-07-26', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98846, 4.31, DATE '2023-07-27', DATE '2023-07-27' FROM DUAL UNION ALL
SELECT 98847, 4.24, DATE '2023-07-31', NULL FROM DUAL UNION ALL
SELECT 98848, 4.31, DATE '2023-07-31', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98853, 4.31, DATE '2023-08-03', DATE '2023-08-03' FROM DUAL UNION ALL
SELECT 98854, 4.24, DATE '2023-08-03', NULL FROM DUAL UNION ALL
SELECT 98855, 4.23, DATE '2023-08-03', NULL FROM DUAL;
输出结果：
| 月份 | OUTSTANDING |
|---|---|
| 2023-06 | 3 |
| 2023-07 | 5 |
| 2023-08 | 5 |
[fiddle](https://dbfiddle.uk/S-lP7gj6)
<details>
<summary>英文:</summary>
You can use a recursive query to generate all the months and then count how many projects have started before the end of each month but have not yet finished:
```lang-sql
WITH months (month, endmonth) AS (
SELECT MIN(TRUNC(startdate, 'MM')),
MAX(TRUNC(compdate, 'MM'))
FROM table_name
UNION ALL
SELECT ADD_MONTHS(month, 1),
endmonth
FROM months
WHERE month < endmonth
)
SELECT TO_CHAR(month, 'YYYY-MM') AS month,
( SELECT COUNT(*)
FROM table_name
WHERE startdate < ADD_MONTHS(m.month, 1)
AND ( compdate >= ADD_MONTHS(m.month, 1)
OR compdate IS NULL )
) AS outstanding
FROM months m
Which, for the sample data:
CREATE TABLE table_name (ID, Step, StartDate, CompDate) AS
SELECT 98775, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98776, 4.31, DATE '2023-06-28', DATE '2023-07-05' FROM DUAL UNION ALL
SELECT 98777, 4.24, DATE '2023-06-28', NULL FROM DUAL UNION ALL
SELECT 98845, 4.31, DATE '2023-07-26', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98846, 4.31, DATE '2023-07-27', DATE '2023-07-27' FROM DUAL UNION ALL
SELECT 98847, 4.24, DATE '2023-07-31', NULL FROM DUAL UNION ALL
SELECT 98848, 4.31, DATE '2023-07-31', DATE '2023-08-02' FROM DUAL UNION ALL
SELECT 98853, 4.31, DATE '2023-08-03', DATE '2023-08-03' FROM DUAL UNION ALL
SELECT 98854, 4.24, DATE '2023-08-03', NULL FROM DUAL UNION ALL
SELECT 98855, 4.23, DATE '2023-08-03', NULL FROM DUAL;
Outputs:
| MONTH | OUTSTANDING |
|---|---|
| 2023-06 | 3 |
| 2023-07 | 5 |
| 2023-08 | 5 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论