Calling all hardcore SQL experts! A challenge

[Bob Dalek]

SQL Lite seems a mite restrictive.

[Moscow Mule]

SQL Lite seems a mite restrictive.

Maybe that's where they got the "Lite" moniker?

It's a bit hard to sell a product as "SQL Restrictive" but bean counters love "Lite" as they equate it to "Cheap".

[_V_]

SQL Lite seems a mite restrictive.

It's actually VERY good. It just sticks to the bare essentials and no bloated loops, cursors and other non-SQL constructs. Lets face it, what's a loop doing in a SQL engine?

[Bob Dalek]

It's actually VERY good. It just sticks to the bare essentials and no bloated loops, cursors and other non-SQL constructs. Lets face it, what's a loop doing in a SQL engine?

Looping?

[PerlOfWisdom]

Exactly to your spec - but you're not going to like it!

select
dateadd (d,(((d0*10+d1)*10+d2)*10+d3)*10+d4,'1/1/2008') as date
from
(select 0 as d0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 as d1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 as d2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 as d3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 as d4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
where
dateadd (d,(((d0*10+d1)*10+d2)*10+d3)*10+d4,'1/1/2008') < '1/1/2009'
order by date

[BrilloPad]

select
dateadd (d,(((d0*10+d1)*10+d2)*10+d3)*10+d4,'1/1/2008') as date
from
(select 0 as d0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 as d1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 as d2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 as d3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 as d4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
where
dateadd (d,(((d0*10+d1)*10+d2)*10+d3)*10+d4,'1/1/2008') < '1/1/2009'
order by date

This is is type of obstrufication I was referring to earlier - its a very elegant solution - but not exactly maintainable. Either it should be heavily commented (I wanted to use a for loop but sqllite is tulip) or you could just leave it and be indespensible.....

[Spacecadet]

It's actually VERY good. It just sticks to the bare essentials and no bloated loops, cursors and other non-SQL constructs. Lets face it, what's a loop doing in a SQL engine?

If you know what you're doing and with a bit of thought you can pretty much do any if not all* data manipulation without using cursors.


*I've still to find something I can't do in a single SQL statement

[Spacecadet]

select
dateadd (d,(((d0*10+d1)*10+d2)*10+d3)*10+d4,'1/1/2008') as date
from
(select 0 as d0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 as d1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 as d2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 as d3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 as d4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
where
dateadd (d,(((d0*10+d1)*10+d2)*10+d3)*10+d4,'1/1/2008') < '1/1/2009'
order by date

SQL Server allows you to manage that easier with a CTE (common table expression)

[PerlOfWisdom]

This is is type of obstrufication I was referring to earlier - its a very elegant solution - but not exactly maintainable. Either it should be heavily commented (I wanted to use a for loop but sqllite is tulip) or you could just leave it and be indespensible.....

Any database person who couldn't maintain that without comments should sell his computer to a second hand shop (Ebay would be too much of a challenge).

[_V_]

Sorted

Thanks for the advice folks. Using a sequence table to join on to expand the date range I managed to get the whole calculation done in a single SELECT (look no loops required!!!).

SELECT a.refNumber, propertyName, type, location, bedrooms, sleeps, bathrooms, poolType, carEssential, case when length(specialOffer) > 0 then 'Y' else 'N' end as hasSpecialOffer, specialOffer, externalAvailabilityCalendarUrl, price
FROM accommodation a
INNER JOIN
(select refNumber, sum(price) as price
      from
     (select a.refNumber as refNumber,  (select price  / 7.00
      from pricingPeriods p
      inner join pricing pr
       on p.name = pr.period
      where pr.refNumber = a.refNumber
      and date( @startDate ,'start of year','+' || (startDay - 1) || ' days', '+' || (startMonth - 1) || ' months') <= s.date and date( @endDate,'start of year','+' || (endDay - 1) || ' days', '+' || (endMonth - 1) || ' months') >= s.date
       order by pricingId desc) as price
      from
      (select date(@startDate,'+' || id || ' days') as date
     from sequence s
     where id < (julianday(@endDate) - julianday(@startDate))) as s
      join accommodation a) as b
     group by refNumber) as z
ON a.refNumber = z.refNumber
WHERE a.isGroupFlag = 'N'
AND a.activeFlag = 'A'
AND a.location = @location
AND a.bedrooms >= @bedrooms
AND a.sleeps >= @sleeps
AND NOT EXISTS (SELECT 'X'
FROM accommodationCalendarEntries c
WHERE c.refNumber = a.refNumber
AND c.entryType IN ('Booked','Pending')
AND ((c.fromDate <= @startDate AND date(c.toDate,'-1 day') >= @startDate)OR (c.fromDate <= date(@endDate,'-1 day') AND date(c.toDate,'-1 day') >= date(@endDate,'-1 day'))OR (@startDate <= c.fromDate AND date(@endDate,'-1 day') >= c.fromDate)
OR (@startDate <= date(c.toDate,'-1 day') AND date(@endDate,'-1 day') >= date(c.toDate,'-1 day')))
)

Lovely stuff this SQLite!



PS. The permies haven't got a clue how this works. Which is nice.