[Swansea Hackspace] SQL Help
Emyr Morris
em at preseli.com
Wed May 20 21:20:45 BST 2015
Again... is the 'review' data stored in the 'candidate' table or in a sub
table?
If you record the reviews in a sub table 'review-logs' then it would be
easier to fetch results or the lack of results
On 20 May 2015 at 21:19, Emyr Morris <em at preseli.com> wrote:
> 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.
>
--
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/60a887d5/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/60a887d5/attachment.gif>
More information about the Hackspace
mailing list