CREATE TABLE projects ( project_id INTEGER NOT NULL PRIMARY KEY,
short_desc VARCHAR(255),
long_desc LONG VARCHAR,
project_status VARCHAR(20),
creation_ts TIMESTAMP NOT NULL,
completion_ts TIMESTAMP )
DATEDIFF( WEEK, completion_ts, creation_ts )
によって、2 つのタイムスタンプ間の週の違いが得られるので、部分的には直接的ですが、プロジェクトが年を跨ってしまうものもあります。SELECT p.project_id, p.short_desc, p.creation_ts, p.completion_ts,
(IF p.completion_ts IS NULL THEN
ABS(DATEDIFF( WEEK, NOW(), p.creation_ts ))
ELSE
ABS(DATEDIFF( WEEK, p.completion_ts, p.creation_ts ))
ENDIF ) AS weeks_outstanding,
(IF p.project_status != 'Complete' OR weeks_outstanding = 0 OR weeks_outstanding > week_number THEN 1 ELSE 0 ENDIF) AS incomplete_projects,
(IF p.completion_ts IS NOT NULL AND (weeks_outstanding = 0 OR weeks_outstanding = week_number) THEN 1 ELSE 0 ENDIF) AS completed_projects,
(IF weeks_outstanding = 0 THEN
DATEPART( YEAR, p.creation_ts )
ELSE
DATEPART( YEAR, DATEADD( WEEK, RG.week_number, p.creation_ts) )
ENDIF) AS calendar_year,
(IF weeks_outstanding = 0 THEN
DATEPART( WEEK, p.creation_ts )
ELSE
DATEPART( WEEK, DATEADD( WEEK, RG.week_number, p.creation_ts) )
ENDIF) AS calendar_week
FROM
( SELECT (row_num - 1) AS week_number FROM RowGenerator) AS RG,
projects p
WHERE
weeks_outstanding >= RG.week_number
DATEPART
関数を WEEK
とともに使用するのは、1 年に 54 週まで可能であることを意味します。なぜならば DATEPART
は、週が日曜日からスタートすると定義するからです。FROM
句で利用できるプロシジャー) をサポートしているからです。You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
25 | |
17 | |
13 | |
7 | |
7 | |
7 | |
6 | |
6 | |
6 |