[Swansea Hackspace] SQL Help

[Emyr Morris]

I'll be there but I am guessing that you have probably tried all the
possibilities that I know - I'm at schoolboy level of SQL ;-)

On 18 May 2015 at 14:24, Graham Owens <grahamowensuk at googlemail.com> wrote:
> 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
>
> _______________________________________________
> Hackspace mailing list
> Hackspace at swansea.hackspace.org.uk
> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>



-- 
This e-mail and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed.

Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn
gyfrinachol ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy
atynt yn unig.