回答編集履歴
2
修正
answer
CHANGED
|
@@ -6,11 +6,11 @@
|
|
|
6
6
|
WITH cte (
|
|
7
7
|
dt
|
|
8
8
|
) AS (
|
|
9
|
-
SELECT '2017-
|
|
9
|
+
SELECT CAST('2017-xx-xx 00:00:00' AS datetime) AS dt
|
|
10
10
|
UNION ALL
|
|
11
11
|
SELECT DATEADD(hour, 1, cte.dt)
|
|
12
12
|
FROM cte
|
|
13
|
-
WHERE cte.dt < '
|
|
13
|
+
WHERE cte.dt < '2017-yy-yy 00:00:00'
|
|
14
14
|
)
|
|
15
15
|
SELECT
|
|
16
16
|
CONVERT (NVARCHAR, cte.dt, 111) AS 日
|
|
@@ -22,15 +22,13 @@
|
|
|
22
22
|
ON CONVERT(NVARCHAR, cte.dt, 111) = CONVERT(NVARCHAR, t.日付項目, 111)
|
|
23
23
|
AND DATEPART(hour, cte.dt) = DATEPART(hour, t.日付項目)
|
|
24
24
|
AND t.条件1 = xxxx
|
|
25
|
-
WHERE
|
|
26
|
-
cte.dt >= '2017-xx-xx 00:00:00'
|
|
27
|
-
AND cte.dt < '2017-yy-yy 00:00:00'
|
|
28
25
|
GROUP BY
|
|
29
26
|
CONVERT(NVARCHAR, cte.dt, 111)
|
|
30
27
|
,DATEPART(hour, cte.dt)
|
|
31
28
|
ORDER BY
|
|
32
29
|
日
|
|
33
30
|
,時間
|
|
31
|
+
OPTION (MAXRECURSION 0)
|
|
34
32
|
```
|
|
35
33
|
|
|
36
34
|
※テストしてないので文法エラーがあるかも
|
1
修正
answer
CHANGED
|
@@ -15,7 +15,7 @@
|
|
|
15
15
|
SELECT
|
|
16
16
|
CONVERT (NVARCHAR, cte.dt, 111) AS 日
|
|
17
17
|
,DATEPART (hour, cte.dt) AS 時間
|
|
18
|
-
,COUNT(
|
|
18
|
+
,COUNT(t.日付項目) AS 件数
|
|
19
19
|
FROM
|
|
20
20
|
cte
|
|
21
21
|
LEFT OUTER JOIN テーブル名 AS t
|