Our SQL-backend family is contantly growing. Rax could already connect to SQLite, MySQL and PostgreSQL databases. Now we have also ported Rax to two major cloud databases: Microsoft Azure and AWS Redshift. The port to Azure gave us some headache due to problems with their ODBC driver for Linux. The port to Redshift was straightforward, as it’s using PostgreSQL’s syntax and ODBC driver.
Before Rax, it was difficult to compare the performance of various database systems on behavioral data, as each database vendor provides a completely different set of date and time functions, which are essential when analyzing behavioral data. With Rax, we can easily do this comparison, as Rax offers a uniform interface for time-related operations.
For testing, we use a real-life analysis: a computation of the TV reach. Below is a Rax script computing the reach of various TV channels:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
// Calculate the reach per channel during a given period {[|]}: PeriodOfInterest := {[(|)[(@)"2013-04-30",(^)"P4D"]]}; ^: TvReachThreshold := (^)"PT5M"; // Map the'TvExposures' table to a Rax set {[#:RespondentId, |:Timeslot, #:ChannelId]}: TvExposures := import [ (#)"RespondentId", (|)[(@)"StartTimeslot", (@)"EndTimeslot"], (#)"TvChannelId" ] "TvExposures"; // Map the 'RespondentWeights' table to a Rax set {[#:RespondentId, #:MediaUniverseId, |:Period, &:Weight]}: RespondentWeights := import [ (#)"RespondentId", (#)"MediaUniverseId", (|)[(@)"CAST(StartDate AS DATETIME)", (@)"CAST(EndDate AS DATETIME)"], (&)"Weight" ] "RespondentWeights"; // Cut the TvExposures set to the period of interest TvExposures := TvExposures @& PeriodOfInterest; // Add correct respondent weights to the TvExposures set {[#:RespondentId, |:Timeslot, #:ChannelId, &:Weight]} : TvExposuresWeighted := project [.RespondentId#1, .Timeslot, .ChannelId, .Weight] (TvExposures @& :[.RespondentId#1 == .RespondentId#2 && .MediaUniverseId == 100] RespondentWeights); // Sum the durations per respondent and channel {[^:totalDuration, #:RespondentId, #:ChannelId, &:Weight]} : Durations := Gsum [.#1]:[.#2, .#3, .#4] project [.Timeslot.absolute, .RespondentId, .ChannelId, .Weight] TvExposuresWeighted; // Filter very light watchers Durations := select [.totalDuration >= TvReachThreshold] Durations; // Compute the total size of the audience &: TotalWeight := Gsum [.#1] Gmean [.Weight]:[.RespondentId] (RespondentWeights @& PeriodOfInterest); // Finally, compute reach per channel {[&:Reach, &:SampleSize, #:ChannelId]} : ReachPerChannel := project [.#1/TotalWeight, .#1, .#2] Gsum [.Weight]:[.ChannelId] Durations; // And print the results `print ReachPerChannel; |
We’ve run this script on Rax/MySQL, Rax/PostgreSQL, Rax/Azure and Rax/Redshift. The script runs on all backends essentially without changes. For Postgres-like dialects (PostgreSQL, Redshift), we had to change one of the lines in the import statement (since import contains snippets of SQL):
|
1 |
(|)[(@)"CAST(StartDate AS DATETIME)", (@)"CAST(EndDate AS DATETIME)"] |
was changed to:
|
1 |
(|)[(@)"CAST(StartDate AS TIMESTAMP)", (@)"CAST(EndDate AS TIMESTAMP)"] |
The data size in this experiment was rather small: the TvExposures table has approximately 400000 rows. The runtimes of the script on various database systems are plotted below (the chart was, naturally, generated by Rax). For Redshift, we tried various cluster configurations.
Somewhat surprisingly, the Azure’s SQL Server was actually slower on this query than MySQL on my local machine (Mac mini with 2.3 GHz Intel Core i7 and 8GB of memory). PostgreSQL performs rather badly. It seems to spend a lot of time on the non-equi join between TvExposures and RespondentWeights. Azure and a single-node Redshift configuration perform similarly. Increasing Redshift’s cluster size and using larger machines gave a clear performance benefit. However, the largest cluster size that we used on Redshift was 2. Scaling beyond that simply didn’t make sense for the data size in this demo.
All in all, it was an interesting experiment. However, it will be even more interesting to compare the performance of this script on real Big Data. You will read about it in one of the following blog posts. Stay put.
And for the curious, this is the query generated from this script by Rax/Azure (don’t get scared):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 |
SELECT * FROM ( SELECT A.rownum AS rownum, (C01) / (25083.4) AS C01, C02 AS C02 FROM ( SELECT 0 AS rownum, SUM(C04) AS C01, C03 AS C02 FROM ( SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04 FROM ( SELECT 0 AS rownum, SUM(C01) AS C01, C02 AS C02, C03 AS C03, C04 AS C04 FROM ( SELECT A.rownum AS rownum, C26 AS C01, C01 AS C02, C27 AS C03, C28 AS C04 FROM ( SELECT A.rownum AS rownum, C01 AS C01, C02 AS C02, C03 AS C03, C04 AS C04, C05 AS C05, C06 AS C06, C07 AS C07, C08 AS C08, C09 AS C09, C10 AS C10, C11 AS C11, C12 AS C12, C13 AS C13, C14 AS C14, C15 AS C15, C16 AS C16, C17 AS C17, C18 AS C18, C19 AS C19, C20 AS C20, C21 AS C21, C22 AS C22, C23 AS C23, C24 AS C24, C25 AS C25, C26 AS C26, C27 AS C27, C30 AS C28 FROM ( SELECT 0 AS rownum, A.C01 AS C01, CASE WHEN (A.C02)>=(B.C03) THEN A.C02 ELSE B.C03 END AS C02, CASE WHEN (A.C02)>=(B.C03) THEN A.C03 ELSE B.C04 END AS C03, CASE WHEN (A.C02)>=(B.C03) THEN A.C04 ELSE B.C05 END AS C04, CASE WHEN (A.C02)>=(B.C03) THEN A.C05 ELSE B.C06 END AS C05, CASE WHEN (A.C02)>=(B.C03) THEN A.C06 ELSE B.C07 END AS C06, CASE WHEN (A.C02)>=(B.C03) THEN A.C07 ELSE B.C08 END AS C07, CASE WHEN (A.C02)>=(B.C03) THEN A.C08 ELSE B.C09 END AS C08, CASE WHEN (A.C02)>=(B.C03) THEN A.C09 ELSE B.C10 END AS C09, CASE WHEN (A.C02)>=(B.C03) THEN A.C10 ELSE B.C11 END AS C10, CASE WHEN (A.C02)>=(B.C03) THEN A.C11 ELSE B.C12 END AS C11, CASE WHEN (A.C02)>=(B.C03) THEN A.C12 ELSE B.C13 END AS C12, CASE WHEN (A.C02)>=(B.C03) THEN A.C13 ELSE B.C14 END AS C13, CASE WHEN (A.C14)<=(B.C15) THEN A.C14 ELSE B.C15 END AS C14, CASE WHEN (A.C14)<=(B.C15) THEN A.C15 ELSE B.C16 END AS C15, CASE WHEN (A.C14)<=(B.C15) THEN A.C16 ELSE B.C17 END AS C16, CASE WHEN (A.C14)<=(B.C15) THEN A.C17 ELSE B.C18 END AS C17, CASE WHEN (A.C14)<=(B.C15) THEN A.C18 ELSE B.C19 END AS C18, CASE WHEN (A.C14)<=(B.C15) THEN A.C19 ELSE B.C20 END AS C19, CASE WHEN (A.C14)<=(B.C15) THEN A.C20 ELSE B.C21 END AS C20, CASE WHEN (A.C14)<=(B.C15) THEN A.C21 ELSE B.C22 END AS C21, CASE WHEN (A.C14)<=(B.C15) THEN A.C22 ELSE B.C23 END AS C22, CASE WHEN (A.C14)<=(B.C15) THEN A.C23 ELSE B.C24 END AS C23, CASE WHEN (A.C14)<=(B.C15) THEN A.C24 ELSE B.C25 END AS C24, CASE WHEN (A.C14)<=(B.C15) THEN A.C25 ELSE B.C26 END AS C25, CASE WHEN (A.C14)<=(B.C15) THEN A.C14 ELSE B.C15 END - CASE WHEN (A.C02)>=(B.C03) THEN A.C02 ELSE B.C03 END AS C26, A.C27 AS C27, B.C01 AS C28, B.C02 AS C29, B.C28 AS C30 FROM ( SELECT 0 AS rownum, A.C01 AS C01, CASE WHEN (A.C02)>=(B.C01) THEN A.C02 ELSE B.C01 END AS C02, CASE WHEN (A.C02)>=(B.C01) THEN A.C03 ELSE B.C02 END AS C03, CASE WHEN (A.C02)>=(B.C01) THEN A.C04 ELSE B.C03 END AS C04, CASE WHEN (A.C02)>=(B.C01) THEN A.C05 ELSE B.C04 END AS C05, CASE WHEN (A.C02)>=(B.C01) THEN A.C06 ELSE B.C05 END AS C06, CASE WHEN (A.C02)>=(B.C01) THEN A.C07 ELSE B.C06 END AS C07, CASE WHEN (A.C02)>=(B.C01) THEN A.C08 ELSE B.C07 END AS C08, CASE WHEN (A.C02)>=(B.C01) THEN A.C09 ELSE B.C08 END AS C09, CASE WHEN (A.C02)>=(B.C01) THEN A.C10 ELSE B.C09 END AS C10, CASE WHEN (A.C02)>=(B.C01) THEN A.C11 ELSE B.C10 END AS C11, CASE WHEN (A.C02)>=(B.C01) THEN A.C12 ELSE B.C11 END AS C12, CASE WHEN (A.C02)>=(B.C01) THEN A.C13 ELSE B.C12 END AS C13, CASE WHEN (A.C14)<=(B.C13) THEN A.C14 ELSE B.C13 END AS C14, CASE WHEN (A.C14)<=(B.C13) THEN A.C15 ELSE B.C14 END AS C15, CASE WHEN (A.C14)<=(B.C13) THEN A.C16 ELSE B.C15 END AS C16, CASE WHEN (A.C14)<=(B.C13) THEN A.C17 ELSE B.C16 END AS C17, CASE WHEN (A.C14)<=(B.C13) THEN A.C18 ELSE B.C17 END AS C18, CASE WHEN (A.C14)<=(B.C13) THEN A.C19 ELSE B.C18 END AS C19, CASE WHEN (A.C14)<=(B.C13) THEN A.C20 ELSE B.C19 END AS C20, CASE WHEN (A.C14)<=(B.C13) THEN A.C21 ELSE B.C20 END AS C21, CASE WHEN (A.C14)<=(B.C13) THEN A.C22 ELSE B.C21 END AS C22, CASE WHEN (A.C14)<=(B.C13) THEN A.C23 ELSE B.C22 END AS C23, CASE WHEN (A.C14)<=(B.C13) THEN A.C24 ELSE B.C23 END AS C24, CASE WHEN (A.C14)<=(B.C13) THEN A.C25 ELSE B.C24 END AS C25, CASE WHEN (A.C14)<=(B.C13) THEN A.C14 ELSE B.C13 END - CASE WHEN (A.C02)>=(B.C01) THEN A.C02 ELSE B.C01 END AS C26, A.C27 AS C27 FROM ( SELECT 0 AS rownum, RespondentId AS C01, ((DATEPART(DAY, StartTimeslot))+FLOOR((153*((DATEPART(MONTH, StartTimeslot))+12*FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))+FLOOR(((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, StartTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, StartTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, StartTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, StartTimeslot))/100)/10000)/(24*60*60) AS C02, 0 AS C03, 0 AS C04, 0 AS C05, 0 AS C06, 0 AS C07, 0 AS C08, 0 AS C09, 0 AS C10, 0 AS C11, 0 AS C12, 0 AS C13, ((DATEPART(DAY, EndTimeslot))+FLOOR((153*((DATEPART(MONTH, EndTimeslot))+12*FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))+FLOOR(((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, EndTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, EndTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, EndTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, EndTimeslot))/100)/10000)/(24*60*60) AS C14, 0 AS C15, 0 AS C16, 0 AS C17, 0 AS C18, 0 AS C19, 0 AS C20, 0 AS C21, 0 AS C22, 0 AS C23, 0 AS C24, 0 AS C25, (((DATEPART(DAY, EndTimeslot))+FLOOR((153*((DATEPART(MONTH, EndTimeslot))+12*FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))+FLOOR(((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR, EndTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, EndTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, EndTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, EndTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, EndTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, EndTimeslot))/100)/10000)/(24*60*60))-(((DATEPART(DAY, StartTimeslot))+FLOOR((153*((DATEPART(MONTH, StartTimeslot))+12*FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12))-3)+2)/5)+365*((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))+FLOOR(((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))/4)-FLOOR(((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))/100)+FLOOR(((DATEPART(YEAR, StartTimeslot))+4800-FLOOR(((14-(DATEPART(MONTH, StartTimeslot)))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, StartTimeslot))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, StartTimeslot))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, StartTimeslot))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, StartTimeslot))/100)/10000)/(24*60*60)) AS C26 , TvChannelId AS C27 FROM TvExposures) AS A INNER JOIN ( SELECT 1 AS rownum, 2122339536000000 AS C01, 2013 AS C02, 3 AS C03, 28 AS C04, 0 AS C05, 0 AS C06, 0 AS C07, 0 AS C08, 18 AS C09, 0 AS C10, 2013 AS C11, 118 AS C12, 2122365456000000 AS C13, 2013 AS C14, 4 AS C15, 28 AS C16, 0 AS C17, 0 AS C18, 0 AS C19, 0 AS C20, 22 AS C21, 2 AS C22, 2013 AS C23, 148 AS C24, 25920000000 AS C25 ) AS B ON A.C14 > B.C01 AND A.C02 < B.C13 ) AS A INNER JOIN ( SELECT 0 AS rownum, RespondentId AS C01, MediaUniverseId AS C02, ((DATEPART(DAY, CAST(StartDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH, CAST(StartDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, CAST(StartDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, CAST(StartDate AS DATETIME)))/100)/10000)/(24*60*60) AS C03, 0 AS C04, 0 AS C05, 0 AS C06, 0 AS C07, 0 AS C08, 0 AS C09, 0 AS C10, 0 AS C11, 0 AS C12, 0 AS C13, 0 AS C14, ((DATEPART(DAY, CAST(EndDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH, CAST(EndDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, CAST(EndDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, CAST(EndDate AS DATETIME)))/100)/10000)/(24*60*60) AS C15, 0 AS C16, 0 AS C17, 0 AS C18, 0 AS C19, 0 AS C20, 0 AS C21, 0 AS C22, 0 AS C23, 0 AS C24, 0 AS C25, 0 AS C26, (((DATEPART(DAY, CAST(EndDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH, CAST(EndDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR, CAST(EndDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(EndDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, CAST(EndDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, CAST(EndDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, CAST(EndDate AS DATETIME)))/100)/10000)/(24*60*60))-(((DATEPART(DAY, CAST(StartDate AS DATETIME)))+FLOOR((153*((DATEPART(MONTH, CAST(StartDate AS DATETIME)))+12*FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12))-3)+2)/5)+365*((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))+FLOOR(((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))/4)-FLOOR(((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))/100)+FLOOR(((DATEPART(YEAR, CAST(StartDate AS DATETIME)))+4800-FLOOR(((14-(DATEPART(MONTH, CAST(StartDate AS DATETIME))))/12)))/400)-32045)*CAST(864000000 AS BIGINT)+(((DATEPART(HOUR, CAST(StartDate AS DATETIME)))-12)*CAST(864000000 AS BIGINT))/24+((DATEPART(MINUTE, CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT))/(24*60)+((DATEPART(SECOND, CAST(StartDate AS DATETIME)))*CAST(864000000 AS BIGINT)+((DATEPART(MICROSECOND, CAST(StartDate AS DATETIME)))/100)/10000)/(24*60*60)) AS C27 , Weight AS C28 FROM RespondentWeights ) AS B ON A.C14 > B.C03 AND A.C02 < B.C15 AND A.C01 = B.C01 AND B.C02 = 100 ) AS A ) AS A ) AS A GROUP BY C02, C03, C04) AS A WHERE (C01) >= (3000000) ) AS A GROUP BY C03) AS A ) AS T |