[Swansea Hackspace] SQL Help
Emyr Morris
em at preseli.com
Wed May 20 21:19:01 BST 2015
is this the perfect time to run a CRON job to calculate who is due a
review... producing a new table... and then query the results of the cron
job?
I'm thinking run the Cron every 24 hours and then any day that a manager
wants a report it can be fetched easily
That would simplify the SQL a lot
On 20 May 2015 at 20:35, Graham Owens <grahamowensuk at googlemail.com> wrote:
> Yeah deffo,
>
> Im so glad we had that chat on Monday, there were some bits that I knew,
> but some awesome additional info - you will also be please to not that I
> have been through and fixed ALL the date usages in my code so that
> everything is handled on the database end. :)
>
> If you want a challenge, this is my next problem
> http://stackoverflow.com/questions/30358486/mysql-query-using-id-in-subquery-or-join
>
> Same as before, I have something that works, using PHP in the middle to
> pass in variables to the sub-queries but its very inefficient and makes the
> PHP almost unreadable.
>
> Thanks,
>
> G
>
>
>
> On 20 May 2015 at 20:28, Emyr Morris <em at preseli.com> wrote:
>
>> it is amazing how a bit of tidying up makes such a difference! Chuffed
>> for you.
>>
>> Smashes that old saying "A problem shared is a problem halved"!
>>
>> Emyr
>>
>>
>>
>> On 20 May 2015 at 17:03, Graham Owens <grahamowensuk at googlemail.com>
>> wrote:
>>
>>> Better??
>>>
>>> Column Type Null Default Comments xxxx_id int(11) No
>>> xxxx_modified timestamp No CURRENT_TIMESTAMP xxxx_created timestamp
>>> No 0000-00-00 00:00:00 xxxx_title varchar(16) No xxxx_first
>>> varchar(50) No xxxx_last varchar(50) No xxxx_dob date No
>>> xxxx_active int(4) No 0 xxxx_start_date date No
>>> xxxx_exp_end_date date No xxxx_start_payment_value decimal(10,2)
>>> No xxxx_start_payment_claim_date date Yes *NULL*
>>> xxxx_outcome_payment_value decimal(10,2) No
>>> xxxx_outcome_payment_claim_date date Yes *NULL*
>>>
>>> Column Type Null Default Comments ms_id int(11) No xxxx_id
>>> int(11) No ms_weight tinyint(4) No ms_exp_date date No
>>> ms_act_date date Yes *NULL* ms_claim_date date Yes *NULL*
>>> ms_value decimal(6,2) No
>>>
>>>
>>> SELECT EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`)
>>> AS period, COUNT(*) as ms_count, IFNULL(SUM(`milestone_value`),0) as
>>> ms_value,
>>> (
>>> SELECT COUNT(*)
>>> FROM `xxxx`
>>> WHERE EXTRACT(YEAR_MONTH FROM `xxxx_exp_end_date`)=EXTRACT(YEAR_MONTH
>>> FROM `calendar_month`.`calendar_month_date`) AND
>>> `xxxx_outcome_payment_claim_date` IS NULL
>>> ) as fin_count,
>>> IFNULL((
>>> SELECT SUM(`xxxx_outcome_payment_value`)
>>> FROM `xxxx`
>>> WHERE EXTRACT(YEAR_MONTH FROM `xxxx_exp_end_date`)=EXTRACT(YEAR_MONTH
>>> FROM `calendar_month`.`calendar_month_date`) AND
>>> `xxxx_outcome_payment_claim_date` IS NULL
>>> ),0) as fin_value
>>> FROM `ms`
>>> RIGHT JOIN `calendar_month`
>>> ON EXTRACT(YEAR_MONTH FROM `ms`.`ms_exp_date`)=EXTRACT(YEAR_MONTH FROM
>>> `calendar_month`.`calendar_month_date`)
>>> WHERE `calendar_month`.`calendar_month_date` BETWEEN DATE_FORMAT(NOW()
>>> ,'%Y-%m-01') AND DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-01'),INTERVAL 12 MONTH)
>>> AND `ms`.`ms_claim_date` IS NULL
>>> GROUP BY EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`)
>>> LIMIT 12
>>>
>>> Managed to get it down from *35.6576* seconds to *0.0139* seconds [?]
>>>
>>> Thanks everyone. Can this be made any more efficient, table row counts
>>> for 'ms' just now are 10's of thousands, but will be into the several
>>> hundred thousand (possibly millions within a year)
>>>
>>>
>>>
>>> On 18 May 2015 at 16:00, Emyr Morris <em at preseli.com> wrote:
>>>
>>>> I can show you some real world examples later of working tables full of
>>>> data. I find it much easier to explain that way
>>>>
>>>>
>>>> On Monday, May 18, 2015, Graham Owens <grahamowensuk at googlemail.com>
>>>> wrote:
>>>>
>>>>> Justin, I think you might be right - we can thrash it out a bit more
>>>>> later
>>>>>
>>>>> Thanks for all the input, once we have a chat later - and formalize
>>>>> the problem better (there are normalization rules????? - just kidding - i
>>>>> was between a rock and a hard place, this works - just needs improved)
>>>>>
>>>>> If it still is giving me problems, we can pick up the conversation
>>>>> then.
>>>>>
>>>>> Thanks
>>>>>
>>>>> G
>>>>>
>>>>> On 18 May 2015 at 15:06, Justin Mitchell <justin at discordia.org.uk>
>>>>> wrote:
>>>>>
>>>>>> On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
>>>>>>
>>>>>>
>>>>>> > 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
>>>>>>
>>>>>> There are several questions about the data structure that need to be
>>>>>> asked here, will one table2 entry only be used once, or could it be
>>>>>> referenced by multiple table1 entries ?
>>>>>>
>>>>>> if the former, then the t2id_* fields are unnessary
>>>>>> if the latter, then they should be replaced with a link table.
>>>>>>
>>>>>> but the list might not be the place to thrash this out, will see you
>>>>>> later.
>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> 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.
>>>>
>>>>
>>>> _______________________________________________
>>>> Hackspace mailing list
>>>> Hackspace at swansea.hackspace.org.uk
>>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>>
>>>>
>>> On 18 May 2015 at 16:00, Emyr Morris <em at preseli.com> wrote:
>>>
>>>> I can show you some real world examples later of working tables full of
>>>> data. I find it much easier to explain that way
>>>>
>>>>
>>>> On Monday, May 18, 2015, Graham Owens <grahamowensuk at googlemail.com>
>>>> wrote:
>>>>
>>>>> Justin, I think you might be right - we can thrash it out a bit more
>>>>> later
>>>>>
>>>>> Thanks for all the input, once we have a chat later - and formalize
>>>>> the problem better (there are normalization rules????? - just kidding - i
>>>>> was between a rock and a hard place, this works - just needs improved)
>>>>>
>>>>> If it still is giving me problems, we can pick up the conversation
>>>>> then.
>>>>>
>>>>> Thanks
>>>>>
>>>>> G
>>>>>
>>>>> On 18 May 2015 at 15:06, Justin Mitchell <justin at discordia.org.uk>
>>>>> wrote:
>>>>>
>>>>>> On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
>>>>>>
>>>>>>
>>>>>> > 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
>>>>>>
>>>>>> There are several questions about the data structure that need to be
>>>>>> asked here, will one table2 entry only be used once, or could it be
>>>>>> referenced by multiple table1 entries ?
>>>>>>
>>>>>> if the former, then the t2id_* fields are unnessary
>>>>>> if the latter, then they should be replaced with a link table.
>>>>>>
>>>>>> but the list might not be the place to thrash this out, will see you
>>>>>> later.
>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> 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.
>>>>
>>>>
>>>> _______________________________________________
>>>> Hackspace mailing list
>>>> Hackspace at swansea.hackspace.org.uk
>>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>>
>>>>
>>>
>>> _______________________________________________
>>> 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.
>>
>> _______________________________________________
>> Hackspace mailing list
>> Hackspace at swansea.hackspace.org.uk
>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>
>>
>
> _______________________________________________
> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/9c69ad20/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 360.gif
Type: image/gif
Size: 453 bytes
Desc: not available
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/9c69ad20/attachment.gif>
More information about the Hackspace
mailing list