Skip to content

Commit 0eecdb7

Browse files
parrtparrt
committed
manually fix CASE THEN clauses that intellij messed up. change model to 1.4.17 as it affects output. update output too.
1 parent 9764b45 commit 0eecdb7

80 files changed

Lines changed: 7143 additions & 55 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎corpus/sql2/training/ProgressQueries.sql‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -22,25 +22,25 @@ FROM
2222
, COUNT(CASE WHEN Status = 4 AND
2323
CONVERT(DATE, LoadReportDBEndDate) <
2424
CONVERT(DATE, GETDATE())
25-
THEN Status
25+
THEN Status
2626
ELSE NULL END) AS OldStatus4
2727
--, COUNT ( CASE WHEN Status = 4 AND LoadReportDBEndDate < GETDATE() THEN Status ELSE NULL END ) AS OldStatus4
2828
, COUNT(CASE WHEN Status = 0
29-
THEN Status
29+
THEN Status
3030
ELSE NULL END) AS Status0
3131
, COUNT(CASE WHEN Status = 1
32-
THEN Status
32+
THEN Status
3333
ELSE NULL END) AS Status1
3434
, COUNT(CASE WHEN Status = 2
35-
THEN Status
35+
THEN Status
3636
ELSE NULL END) AS Status2
3737
, COUNT(CASE WHEN Status = 3
38-
THEN Status
38+
THEN Status
3939
ELSE NULL END) AS Status3
4040
, COUNT(CASE WHEN Status = 4 AND
4141
DATEPART(DAY, LoadReportDBEndDate) =
4242
DATEPART(DAY, GETDATE())
43-
THEN Status
43+
THEN Status
4444
ELSE NULL END) AS Status4
4545
FROM dbo.ClientConnection
4646
GROUP BY SSISInstanceID
@@ -92,7 +92,7 @@ SELECT
9292
THEN 'Grand Total'
9393
ELSE CAST(Beta AS VARCHAR) END AS Beta
9494
, CASE WHEN SourceDB IS NULL
95-
THEN 'Beta Group Total'
95+
THEN 'Beta Group Total'
9696
ELSE SourceDB END AS SourceDB
9797
--, LoadStageDBStartDate
9898
--, LoadStageDBEndDate

‎corpus/sql2/training/SQLFilesAudit.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6,14 +6,14 @@ SELECT
66
THEN 1
77
ELSE 0 END AS bit) AS [IsPrimaryFile]
88
, CAST(CASE WHEN s.growth = 0
9-
THEN (CASE WHEN s.type = 2
10-
THEN 0
11-
ELSE 99 END)
9+
THEN (CASE WHEN s.type = 2
10+
THEN 0
11+
ELSE 99 END)
1212
ELSE s.is_percent_growth END AS INT) AS [GrowthType]
1313
, s.physical_name AS [FileName]
1414
, s.size * CONVERT(float, 8) AS [Size]
1515
, CASE WHEN s.max_size = -1
16-
THEN -1
16+
THEN -1
1717
ELSE s.max_size * CONVERT(float, 8) END AS [MaxSize]
1818
, s.file_id AS [ID]
1919
, 'Server[@Name=' + quotename(CAST(

‎corpus/sql2/training/dmart_bits_IAPPBO510.sql‎

Lines changed: 12 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -113,25 +113,25 @@ FROM
113113
, COUNT(CASE WHEN Status = 4 AND
114114
CONVERT(DATE, LoadReportDBEndDate) <
115115
CONVERT(DATE, GETDATE())
116-
THEN Status
116+
THEN Status
117117
ELSE NULL END) AS OldStatus4
118118
, COUNT(CASE WHEN Status = 0
119-
THEN Status
119+
THEN Status
120120
ELSE NULL END) AS Status0
121121
, COUNT(CASE WHEN Status = 1
122-
THEN Status
122+
THEN Status
123123
ELSE NULL END) AS Status1
124124
, COUNT(CASE WHEN Status = 2
125-
THEN Status
125+
THEN Status
126126
ELSE NULL END) AS Status2
127127
, COUNT(CASE WHEN Status = 3
128-
THEN Status
128+
THEN Status
129129
ELSE NULL END) AS Status3
130130
--, COUNT ( CASE WHEN Status = 4 THEN Status ELSE NULL END ) AS Status4
131131
, COUNT(CASE WHEN Status = 4 AND
132132
DATEPART(DAY, LoadReportDBEndDate) =
133133
DATEPART(DAY, GETDATE())
134-
THEN Status
134+
THEN Status
135135
ELSE NULL END) AS Status4
136136
FROM dbo.ClientConnection
137137
GROUP BY SSISInstanceID
@@ -162,24 +162,24 @@ FROM
162162
, COUNT(CASE WHEN Status = 4 AND
163163
CONVERT(DATE, LoadReportDBEndDate) <
164164
CONVERT(DATE, GETDATE())
165-
THEN Status
165+
THEN Status
166166
ELSE NULL END) AS OldStatus4
167167
, COUNT(CASE WHEN Status = 0
168-
THEN Status
168+
THEN Status
169169
ELSE NULL END) AS Status0
170170
, COUNT(CASE WHEN Status = 1
171-
THEN Status
171+
THEN Status
172172
ELSE NULL END) AS Status1
173173
, COUNT(CASE WHEN Status = 2
174-
THEN Status
174+
THEN Status
175175
ELSE NULL END) AS Status2
176176
, COUNT(CASE WHEN Status = 3
177-
THEN Status
177+
THEN Status
178178
ELSE NULL END) AS Status3
179179
, COUNT(CASE WHEN Status = 4 AND
180180
DATEPART(DAY, LoadReportDBEndDate) =
181181
DATEPART(DAY, GETDATE())
182-
THEN Status
182+
THEN Status
183183
ELSE NULL END) AS Status4
184184
FROM dbo.ClientConnection
185185
GROUP BY SSISInstanceID

‎corpus/sql2/training/dmart_bits_PSQLRPT24.sql‎

Lines changed: 22 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -82,19 +82,19 @@ FROM
8282
SELECT
8383
CONVERT(VARCHAR, SSISInstanceID) AS SSISInstanceID
8484
, COUNT(CASE WHEN Status = 0
85-
THEN Status
85+
THEN Status
8686
ELSE NULL END) AS Status0
8787
, COUNT(CASE WHEN Status = 1
88-
THEN Status
88+
THEN Status
8989
ELSE NULL END) AS Status1
9090
, COUNT(CASE WHEN Status = 2
91-
THEN Status
91+
THEN Status
9292
ELSE NULL END) AS Status2
9393
, COUNT(CASE WHEN Status = 3
94-
THEN Status
94+
THEN Status
9595
ELSE NULL END) AS Status3
9696
, COUNT(CASE WHEN Status = 4
97-
THEN Status
97+
THEN Status
9898
ELSE NULL END) AS Status4
9999
FROM dbo.ClientConnectionCDC
100100
GROUP BY SSISInstanceID
@@ -116,19 +116,19 @@ FROM
116116
SELECT
117117
CONVERT(VARCHAR, SSISInstanceID) AS SSISInstanceID
118118
, COUNT(CASE WHEN Status = 0
119-
THEN Status
119+
THEN Status
120120
ELSE NULL END) AS Status0
121121
, COUNT(CASE WHEN Status = 1
122-
THEN Status
122+
THEN Status
123123
ELSE NULL END) AS Status1
124124
, COUNT(CASE WHEN Status = 2
125-
THEN Status
125+
THEN Status
126126
ELSE NULL END) AS Status2
127127
, COUNT(CASE WHEN Status = 3
128-
THEN Status
128+
THEN Status
129129
ELSE NULL END) AS Status3
130130
, COUNT(CASE WHEN Status = 4
131-
THEN Status
131+
THEN Status
132132
ELSE NULL END) AS Status4
133133
FROM dbo.ClientConnection
134134
GROUP BY SSISInstanceID
@@ -158,23 +158,23 @@ FROM
158158
CONVERT(VARCHAR, SSISInstanceID) AS InstanceID
159159
, COUNT(CASE WHEN Status = 4 AND CONVERT(DATE, EndTimeExtract) <
160160
CONVERT(DATE, GETDATE())
161-
THEN Status
161+
THEN Status
162162
ELSE NULL END) AS OldStatus4
163163
, COUNT(CASE WHEN Status = 0
164-
THEN Status
164+
THEN Status
165165
ELSE NULL END) AS Status0
166166
, COUNT(CASE WHEN Status = 1
167-
THEN Status
167+
THEN Status
168168
ELSE NULL END) AS Status1
169169
, COUNT(CASE WHEN Status = 2
170-
THEN Status
170+
THEN Status
171171
ELSE NULL END) AS Status2
172172
, COUNT(CASE WHEN Status = 3
173-
THEN Status
173+
THEN Status
174174
ELSE NULL END) AS Status3
175175
, COUNT(CASE WHEN Status = 4 AND DATEPART(DAY, EndTimeExtract) =
176176
DATEPART(DAY, GETDATE())
177-
THEN Status
177+
THEN Status
178178
ELSE NULL END) AS Status4
179179
FROM dbo.ClientConnectionCDC
180180
GROUP BY SSISInstanceID
@@ -205,24 +205,24 @@ FROM
205205
, COUNT(CASE WHEN Status = 4 AND
206206
CONVERT(DATE, LoadReportDBEndDate) <
207207
CONVERT(DATE, GETDATE())
208-
THEN Status
208+
THEN Status
209209
ELSE NULL END) AS OldStatus4
210210
, COUNT(CASE WHEN Status = 0
211-
THEN Status
211+
THEN Status
212212
ELSE NULL END) AS Status0
213213
, COUNT(CASE WHEN Status = 1
214-
THEN Status
214+
THEN Status
215215
ELSE NULL END) AS Status1
216216
, COUNT(CASE WHEN Status = 2
217-
THEN Status
217+
THEN Status
218218
ELSE NULL END) AS Status2
219219
, COUNT(CASE WHEN Status = 3
220-
THEN Status
220+
THEN Status
221221
ELSE NULL END) AS Status3
222222
, COUNT(CASE WHEN Status = 4 AND
223223
DATEPART(DAY, LoadReportDBEndDate) =
224224
DATEPART(DAY, GETDATE())
225-
THEN Status
225+
THEN Status
226226
ELSE NULL END) AS Status4
227227
FROM dbo.ClientConnection
228228
GROUP BY SSISInstanceID

‎corpus/sql2/training/ex_GROUPBY.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -47,11 +47,11 @@ SELECT
4747
g.hair
4848
, (COUNT(*)) AS girl_count
4949
, (COUNT(CASE WHEN did_date = 1
50-
THEN did_date
50+
THEN did_date
5151
ELSE NULL
5252
END)) AS did_date_count
5353
, (COUNT(CASE WHEN did_date = 0
54-
THEN did_date
54+
THEN did_date
5555
ELSE NULL
5656
END)) AS did_not_date_count
5757
FROM girl g

‎corpus/sql2/training/ex_SUMbyColumn.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,6 @@ SELECT
1616
FROM t
1717
GROUP BY COL
1818
ORDER BY CASE WHEN Col IS NULL
19-
THEN 999
19+
THEN 999
2020
ELSE 0
2121
END

‎corpus/sql2/training/vwTableInfo.sql‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@ SELECT
4141
, Coalesce((
4242
SELECT Cast(v.low / 1024.0 AS FLOAT)
4343
* SUM(a.used_pages - CASE WHEN a.type <> 1
44-
THEN a.used_pages
44+
THEN a.used_pages
4545
WHEN p.index_id < 2
4646
THEN a.data_pages
4747
ELSE 0 END)
@@ -50,13 +50,13 @@ SELECT
5050
p.index_id = i.index_id
5151
JOIN sys.allocation_units AS a
5252
ON a.container_id = p.partition_id
53-
WHERE i.object_id = tbl.object_id)
54-
, 0.0) AS [IndexKB]
53+
WHERE i.object_id = tbl.object_id),
54+
0.0) AS [IndexKB]
5555

5656
, Coalesce((
5757
SELECT Cast(v.low / 1024.0 AS FLOAT)
5858
* SUM(CASE WHEN a.type <> 1
59-
THEN a.used_pages
59+
THEN a.used_pages
6060
WHEN p.index_id < 2
6161
THEN a.data_pages
6262
ELSE 0 END)
@@ -65,8 +65,8 @@ SELECT
6565
p.index_id = i.index_id
6666
JOIN sys.allocation_units AS a
6767
ON a.container_id = p.partition_id
68-
WHERE i.object_id = tbl.object_id)
69-
, 0.0) AS [DataKB]
68+
WHERE i.object_id = tbl.object_id),
69+
0.0) AS [DataKB]
7070
, tbl.create_date
7171
, tbl.modify_date
7272

‎output/sqlite/1.4.17/DriveSpace.sql‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
SELECT *
2+
FROM DriveInfo;
3+
--script to retrieve the values in MB from PS Script output
4+
5+
6+
SELECT
7+
RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) AS drivename
8+
, ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line) + 1
9+
,
10+
(CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) AS FLOAT), 0) AS 'capacity(MB)'
11+
, ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line) + 1
12+
,
13+
(CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) AS FLOAT), 0) AS 'freespace(MB)'
14+
, ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line) + 1
15+
,
16+
(CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) AS FLOAT) /
17+
1024, 0) AS 'capacity(GB)'
18+
, ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line) + 1
19+
,
20+
(CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) AS FLOAT) /
21+
1024, 0) AS 'freespace(GB)'
22+
FROM DriveInfo
23+
WHERE line LIKE '[A-Z][:]%'
24+
ORDER BY drivename

‎output/sqlite/1.4.17/Generate_Weekly_Perfmon.sql‎

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,72 @@
1+
INSERT INTO CountersDataDaily
2+
3+
SELECT
4+
server_id
5+
, CountersID
6+
, MinValue
7+
, MeanAvgValue
8+
, MaxValue
9+
, Day
10+
, GetDate() AS LastUpdate
11+
FROM (
12+
SELECT
13+
s.server_id
14+
, c.CountersID
15+
, min(cd.CounterValue) AS MinValue
16+
, (sum(cd.CounterValue) -
17+
min(cd.CounterValue) - max(cd.CounterValue)) / cast(count(*) - 2 AS FLOAT) AS MeanAvgValue
18+
, max(cd.CounterValue) AS MaxValue
19+
, convert(char(10), CounterDateTime,
20+
101) AS Day
21+
FROM Counters c
22+
INNER JOIN CountersData cd
23+
ON c.CountersID = cd.CountersID
24+
INNER JOIN newton.Statusdbot_server s
25+
ON s.server_id = cd.ServerID
26+
WHERE convert(char(10), CounterDateTime,
27+
101) >
28+
GetDate() - 3
29+
--where convert(char(10), CounterDateTime, 101) = (GetDate() - 6)
30+
GROUP BY
31+
s.server_id
32+
, c.CountersID
33+
, convert(char(10), CounterDateTime,
34+
101)
35+
) AS f
36+
37+
SELECT
38+
server_id
39+
, CountersID
40+
, MinValue
41+
, MeanAvgValue
42+
, MaxValue
43+
, Day
44+
, GetDate() AS LastUpdate
45+
FROM
46+
(
47+
SELECT
48+
s.server_id
49+
, c.CountersID
50+
, min(cdd.MeanAvgValue) AS MinValue
51+
, (sum(cdd.MeanAvgValue) -
52+
min(cdd.MeanAvgValue) - max(cdd.MeanAvgValue)) / cast(count(*) - 2 AS FLOAT) AS MeanAvgValue
53+
, max(cdd.MaxValue) AS MaxValue
54+
, convert(char(10), Date,
55+
101) AS Day
56+
FROM Counters c
57+
INNER JOIN CountersDataDaily cdd
58+
ON c.CountersID = cdd.CountersID
59+
INNER JOIN newton.Statusdbot_server s
60+
ON s.server_id = cdd.ServerID
61+
WHERE convert(char(10), Date,
62+
101) >
63+
GetDate() - 3
64+
GROUP BY
65+
s.server_id
66+
, c.CountersID
67+
, convert(char(10), Date,
68+
101)
69+
) AS f
70+
ORDER BY server_id, ContersID, Day
71+
72+
SELECT datepart(dw, GetDate())

0 commit comments

Comments
 (0)