How can I translate the given query using AREL or Squeel:
SET @start = '2013-05-14';
SET @end = '2013-11-01';
SET @days = DATEDIFF(@end, @start);
SET @UnusedDays = 0;
SELECT @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at);
SELECT @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at);
SELECT @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall
FROM PERIODS
WHERE (@start >= start_at AND @start < end_at)
OR (end_at < @end AND start_at > @start)
OR (@end > start_at AND @end <= end_at);
A solution to use it as raw SQL would also be welcome as I could not get it running so far.
UPDATE: The period model and goals are described at SQL request to find if a period is fully covered
UPDATE2: If I try to use a raw sql query like:
query = %Q[
SET @start = '2013-05-14';
SET @end = '2013-11-01';
SET @days = DATEDIFF(@end, @start);
SET @UnusedDays = 0;
SELECT @UnusedDays := DATEDIFF(end_at,@end) FROM PERIODS WHERE (@end > start_at AND @end <= end_at);
SELECT @UnusedDays := @UnusedDays + DATEDIFF(@start, start_at) FROM PERIODS WHERE (@start >= start_at AND @start < end_at);
SELECT @days + @UnusedDays - SUM(DATEDIFF(end_at, start_at)) AS Shortfall
FROM PERIODS
WHERE (@start >= start_at AND @start < end_at)
OR (end_at < @end AND start_at > @start)
OR (@end > start_at AND @end <= end_at);
]
Period.connection.select_all(query).first.map{|k,v| v }.first == 0
I get the following error:
ActiveRecord::StatementInvalid:
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @end = '2013-11-01';
