Reply to: Calling all hardcore SQL experts! A challenge

[NickFitz]

Looping?

SQL was originally defined as a declarative language; declarative languages don't have loops. Loops lead to side effects, which are a Bad Thing.

It always annoys me when people refer to XSLT as having loops (which it doesn't).

[lilelvis2000]

Well done!

Although I would have used an outer join rather than the NOT EXISTS but given the size of the data sets you're querying I doubt that there would be that big an impact on performance

That depends on the plan - which would depend on the statistics and indexes and all sorts.....

[Spacecadet]

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!!!).

Lovely stuff this SQLite!



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

Well done!

Although I would have used an outer join rather than the NOT EXISTS but given the size of the data sets you're querying I doubt that there would be that big an impact on performance

[_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.

[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).

[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)

[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

[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.....

[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

[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?

[_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?

[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".

[Bob Dalek]

SQL Lite seems a mite restrictive.

[t0bytoo]

It ain't sql, but you've got to admin that Ruby is pretty sexy for this kind of thing:

(date_start..date_end).map{|d| d.month == 12 ? d : d.at_beginning_of_month}.uniq

[Spacecadet]

same but slightly different approach from London75's is to create a "dates" table which contains all dates which you could possibly be interested in (years 1900 to 2100), great thing about this is that it can be expanded to include lots of date type stuff

SQLDate as date -- primary key/ clustered index
DisplayDate as varchar -- you can have as many types of these as you like depending on the formats you want to display the date in
Weekday as boolean -- flag to specify a weekday/weekend
PublicHoliday as boolean -- public holiday flag
DayName as Varchar -- monday, tuesday, wednesday etc...
ShortDayName as Varchar -- Mon, Tue
MonthName as Varchar -- January, Feburary etc...
ShortMonthName as varchar
DayNum as int -- 1, 2, 3, .... 365
WeekNum as int -- 1, 2, 3, ...52
MonthNum as int -- 1, 2,... 12

-- then add some company specific columns
FinancialYear as int
FinancialPeriod as int
SalesYear as int
SalesPeriod as int
BudgetYear as int
BudgetPeriod as int

when joining on to another table with dates to get a full range of dates with nothing left out you can then use an outer join:

select *
from detailsTable A
full outer join DatesTable D
on A.dateCol = D.SQLDate

if you wanted only week days then you can filter on your dates table

where D.weekday = True

As you have the weekend and public holiday flags then these columns can be used be other systems for conditional formatting