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:
// 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):
(|)[(@)"CAST(StartDate AS DATETIME)", (@)"CAST(EndDate AS DATETIME)"]
was changed to:
(|)[(@)"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):
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