[Swansea Hackspace] SQL Help
Graham Owens
grahamowensuk at googlemail.com
Wed May 20 21:58:06 BST 2015
I added a sql fiddle to the post including tables and sample data :)
On 20 May 2015 21:21, "Emyr Morris" <em at preseli.com> wrote:
> 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.
>
> _______________________________________________
> Hackspace mailing list
> Hackspace at swansea.hackspace.org.uk
> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/db0ccc65/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/db0ccc65/attachment.gif>
More information about the Hackspace
mailing list