[Swansea Hackspace] SQL Help

[Graham Owens]

Hi all,

Is anyone coming tonight that has good SQL skills?

Im having a problem with some joins and subquery type stuff.  Just to
clarify though, I can do what I am trying to achieve by using php as some
glue for the SQL, and now im up to a 25-35 seconds for the queries to
execute and my fake CRON doesnt like it - i get an email warning at 30, and
the process is killed at 35 seconds.

I believe it is achievable in MUCH less time doing all the work at the
database end - just now I have had to resort to creating a cache for the
results when it server load is low enough that it lets me process in under
30 seconds :(

if it helps the table format is something like this

table1
table1.id: autonum
active: bool
t2id_1: int
t2id_2: int
t2id_3: int
t2id_4: int
t2id_5: int
t2id_6: int

table2
table2.id: autonum
expected_date:  datetime
actual_date:   datetime
claim_date: datetime
value: datetime


what i need is
for every item in table1 that is active, get the items from table2 that
match t2id_1 ... t2id_6, from this i need a count and value  for the next
'x' months where the claim_date is blank

[month]['count'] = 3
[month]['value'] = 600

[month+n]['count'] = 7
[month+n]['value'] = 450

an so on..

I will have my laptop with me so i can demonstrate the actual database
format, and the results I desire if anyone can help :)

Thanks

G
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150518/382a2df6/attachment.html>