Advice on report

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Advice on report

Patrick Larkin-3
Hello -

I’m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.  

I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)

I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.  


PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
——
Patrick Larkin
Application Management Group
Information Technology
Bethlehem Area School District
https://www.beth.k12.pa.us

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Dave Bruhn
Are you doing any kind of logging in MySQL?  What mechanism do you believe is being used to update the fields - a Lasso page making the changes, or someone with direct MySQL access?

Dave
---
Dave Bruhn
Systems Analyst
North Carolina Hospital Association
PO Box 4449
Cary, NC 27519-4449
919-677-4145 (office)
[hidden email]
http://www.ncha.org

On Sep 2, 2014, at 3:00 PM, Patrick Larkin <[hidden email]> wrote:

> Hello -
>
> I’m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.  
>
> I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)
>
> I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.  
>
>
> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
> ——
> Patrick Larkin
> Application Management Group
> Information Technology
> Bethlehem Area School District
> https://www.beth.k12.pa.us
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Patrick Larkin-3
The data is replaced each night with all new data.  For example, a student might be in school A and tomorrow be in school B.  I need to know when a student changes schools.  

Table holds 15000 rows of 20 fields or so per record.  Each night, table gets wiped and repopulated with a fresh upload from our master database.


PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
——
Patrick Larkin
Application Management Group
Information Technology
Bethlehem Area School District
https://www.beth.k12.pa.us

On Sep 2, 2014, at 3:02 PM, Dave Bruhn <[hidden email]> wrote:

> Are you doing any kind of logging in MySQL?  What mechanism do you believe is being used to update the fields - a Lasso page making the changes, or someone with direct MySQL access?
>
> Dave
> ---
> Dave Bruhn
> Systems Analyst
> North Carolina Hospital Association
> PO Box 4449
> Cary, NC 27519-4449
> 919-677-4145 (office)
> [hidden email]
> http://www.ncha.org
>
> On Sep 2, 2014, at 3:00 PM, Patrick Larkin <[hidden email]> wrote:
>
>> Hello -
>>
>> I’m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.  
>>
>> I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)
>>
>> I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.  
>>
>>
>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>> ——
>> Patrick Larkin
>> Application Management Group
>> Information Technology
>> Bethlehem Area School District
>> https://www.beth.k12.pa.us
>>
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Trevor Borgmeier
In reply to this post by Patrick Larkin-3

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Advice on report

Rick Draper-2
In reply to this post by Patrick Larkin-3

> The data is replaced each night with all new data
Hi Patrick,

Do you control the replacement process?  If so, just copy the table to a new
name (mytable_previous) before the replacement occurs?  Or do this during
the day at some stage so that you have a comparison available?

VBR

Rick


#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Trevor Borgmeier
In reply to this post by Patrick Larkin-3
MySQL triggers would work good for this...

Just create a new table to hold the reports, and then set up a MySQL
trigger to log the change to the new table as needed...

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

-Trevor


on 9/2/14, 2:00 PM Patrick Larkin wrote:

> Hello -
>
> I�m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.
>
> I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)
>
> I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.
>
>
> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
> ��
> Patrick Larkin
> Application Management Group
> Information Technology
> Bethlehem Area School District
> https://www.beth.k12.pa.us
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>    the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>
>


ɹǝıǝɯƃɹoq ɹoʌǝɹʇ
#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Jolle Carlestam-2
In reply to this post by Trevor Borgmeier

> 2 sep 2014 kl. 21:28 skrev Trevor Borgmeier <[hidden email]>:
>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################

Nice hint, Trevor. :-)
And a good advice.

HDB
Jolle

Sent from a thin, flat, touchy device from an undetermined place in space.

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Patrick Larkin-3
In reply to this post by Trevor Borgmeier
That would work if the table wasn’t completely refreshed every night.  

I thought there might be a convenient way to compare two MySQL tables (yesterday and today).  I’ll keep digging.  Thanks.

PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
——
Patrick Larkin
Application Management Group
Information Technology
Bethlehem Area School District
https://www.beth.k12.pa.us

On Sep 2, 2014, at 4:33 PM, Trevor Borgmeier <[hidden email]> wrote:

> MySQL triggers would work good for this...
>
> Just create a new table to hold the reports, and then set up a MySQL trigger to log the change to the new table as needed...
>
> http://dev.mysql.com/doc/refman/5.0/en/triggers.html
>
> -Trevor
>
>
> on 9/2/14, 2:00 PM Patrick Larkin wrote:
>> Hello -
>>
>> I�m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.
>>
>> I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)
>>
>> I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.
>>
>>
>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>> ��
>> Patrick Larkin
>> Application Management Group
>> Information Technology
>> Bethlehem Area School District
>> https://www.beth.k12.pa.us
>>
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>>   the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>>
>>
>
>
> ɹǝıǝɯƃɹoq ɹoʌǝɹʇ
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Dave Bruhn
Is the table refresh done at a specific time every night?  Say, perhaps, by a cron job or something akin to that?

If so, I'd create a secondary table with the same schema as the original.  Then, write a script that clears out the secondary table and copies over all of the data from the original before the original table is updated.  Compare rows when the refresh is complete.

I think that's the only way to accomplish this.

Dave
---
Dave Bruhn
Systems Analyst
North Carolina Hospital Association
PO Box 4449
Cary, NC 27519-4449
919-677-4145 (office)
[hidden email]
http://www.ncha.org

On Sep 3, 2014, at 8:07 AM, Patrick Larkin <[hidden email]> wrote:

> That would work if the table wasn’t completely refreshed every night.  
>
> I thought there might be a convenient way to compare two MySQL tables (yesterday and today).  I’ll keep digging.  Thanks.
>
> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
> ——
> Patrick Larkin
> Application Management Group
> Information Technology
> Bethlehem Area School District
> https://www.beth.k12.pa.us
>
> On Sep 2, 2014, at 4:33 PM, Trevor Borgmeier <[hidden email]> wrote:
>
>> MySQL triggers would work good for this...
>>
>> Just create a new table to hold the reports, and then set up a MySQL trigger to log the change to the new table as needed...
>>
>> http://dev.mysql.com/doc/refman/5.0/en/triggers.html
>>
>> -Trevor
>>
>>
>> on 9/2/14, 2:00 PM Patrick Larkin wrote:
>>> Hello -
>>>
>>> I�m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.
>>>
>>> I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)
>>>
>>> I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.
>>>
>>>
>>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>>> ��
>>> Patrick Larkin
>>> Application Management Group
>>> Information Technology
>>> Bethlehem Area School District
>>> https://www.beth.k12.pa.us
>>>
>>> #############################################################
>>> Attend the Lasso Developer Conference 2014!
>>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>>> http://www.lassosoft.com/LDC-newmarket-2014
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>>  the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>>
>>>
>>
>>
>> ɹǝıǝɯƃɹoq ɹoʌǝɹʇ
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Patrick Larkin-3
Yes, it's a cron job with a mysqlimport.  It's the comparing rows that I'm trying to figure out.  There will be new rows, missing rows, and changed rows.

Sent from my iPhone

> On Sep 3, 2014, at 9:00 AM, Dave Bruhn <[hidden email]> wrote:
>
> Is the table refresh done at a specific time every night?  Say, perhaps, by a cron job or something akin to that?
>
> If so, I'd create a secondary table with the same schema as the original.  Then, write a script that clears out the secondary table and copies over all of the data from the original before the original table is updated.  Compare rows when the refresh is complete.
>
> I think that's the only way to accomplish this.
>
> Dave
> ---
> Dave Bruhn
> Systems Analyst
> North Carolina Hospital Association
> PO Box 4449
> Cary, NC 27519-4449
> 919-677-4145 (office)
> [hidden email]
> http://www.ncha.org
>
>> On Sep 3, 2014, at 8:07 AM, Patrick Larkin <[hidden email]> wrote:
>>
>> That would work if the table wasn’t completely refreshed every night.  
>>
>> I thought there might be a convenient way to compare two MySQL tables (yesterday and today).  I’ll keep digging.  Thanks.
>>
>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>> ——
>> Patrick Larkin
>> Application Management Group
>> Information Technology
>> Bethlehem Area School District
>> https://www.beth.k12.pa.us
>>
>>> On Sep 2, 2014, at 4:33 PM, Trevor Borgmeier <[hidden email]> wrote:
>>>
>>> MySQL triggers would work good for this...
>>>
>>> Just create a new table to hold the reports, and then set up a MySQL trigger to log the change to the new table as needed...
>>>
>>> http://dev.mysql.com/doc/refman/5.0/en/triggers.html
>>>
>>> -Trevor
>>>
>>>
>>>> on 9/2/14, 2:00 PM Patrick Larkin wrote:
>>>> Hello -
>>>>
>>>> I�m wondering if anyone would have any advice for me on producing a report that would look for changes in a MySQL database table - particularly in 2 or 3 fields.
>>>>
>>>> I have a table of 15,000 or so students and need a report that shows when a particular field has changed and then grab that record.  The issue is that the table is completely refreshed each evening.  A delete is performed and then the new data is imported from our master database.  (MS SQL)
>>>>
>>>> I could duplicate the old table but what is the most efficient way to do a compare?  Thanks for any insight.
>>>>
>>>>
>>>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>>>> ��
>>>> Patrick Larkin
>>>> Application Management Group
>>>> Information Technology
>>>> Bethlehem Area School District
>>>> https://www.beth.k12.pa.us
>>>>
>>>> #############################################################
>>>> Attend the Lasso Developer Conference 2014!
>>>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>>>> http://www.lassosoft.com/LDC-newmarket-2014
>>>>
>>>> #############################################################
>>>>
>>>> This message is sent to you because you are subscribed to
>>>> the mailing list Lasso [hidden email]
>>>> Official list archives available at http://www.lassotalk.com
>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>>
>>> ɹǝıǝɯƃɹoq ɹoʌǝɹʇ
>>> #############################################################
>>> Attend the Lasso Developer Conference 2014!
>>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>>> http://www.lassosoft.com/LDC-newmarket-2014
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Advice on report

Rick Draper-2
Hi Patrick,

Assuming there is a unique identifier for each record, can you create a hash value along with the other fields in the table and add this to a new column in the table.  Anywhere the hash values match, the record is unchanged - the others are either new or changed.



Very best regards,

Rick
>


#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Tami Williams-3
That assumes every field in the record is new?  What happens if only 1 field in the record is changed?

On Sep 3, 2014, at 3:29 PM, Rick Draper <[hidden email]> wrote:

> Hi Patrick,
>
> Assuming there is a unique identifier for each record, can you create a hash value along with the other fields in the table and add this to a new column in the table.  Anywhere the hash values match, the record is unchanged - the others are either new or changed.
>
>
>
> Very best regards,
>
> Rick
>>
>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Advice on report

Rick Draper-2
> That assumes every field in the record is new?  What happens if only 1
field in the record is changed?

Hi Tami,

Patrick said the table is effectively replaced each night, so if after the
table is in place a hash value is assigned to each record, that can be
compared to the hash value in the matching record of the previous night's
table.  There will be a day lost in implementation, but the workflow will
be:

1. Copy mytable_current to mytable_previous
2. Replace data in mytable_current
3. Run routine to create hash values for all records in mytable_current.hash
4. Look for any records where mytable_current.id = mytable_previous.id AND
mytable_current.hash <> mytable_previous.hash // they are changed


Very best regards,

Rick


#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Advice on report

Rick Draper-2
Just to clarify, the hash needs to be based on the concatenation of all
fields in the record (or similar approach) so given that there is a unique
ID, each hash will be unique and can be compared for the same ID from
previous table.



-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Rick Draper
Sent: Thursday, 4 September 2014 6:30 AM
To: [hidden email]
Subject: RE: Advice on report

> That assumes every field in the record is new?  What happens if only 1
field in the record is changed?

Hi Tami,

Patrick said the table is effectively replaced each night, so if after the
table is in place a hash value is assigned to each record, that can be
compared to the hash value in the matching record of the previous night's
table.  There will be a day lost in implementation, but the workflow will
be:

1. Copy mytable_current to mytable_previous
2. Replace data in mytable_current
3. Run routine to create hash values for all records in mytable_current.hash
4. Look for any records where mytable_current.id = mytable_previous.id AND
mytable_current.hash <> mytable_previous.hash // they are changed


Very best regards,

Rick


#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Patrick Larkin-3
Hi -

I did the following:

- Archive table of data as “table_yesterday"
- New data comes into table “table"
- I “inline” through each record grabbing the student id
- Inside the inline, I find the corresponding student in the archive
- If found, I note any building or grade change
- If not found, student is noted as a new student
- Write out findings

This works well but takes longer than I would like.  Creating an index on the student id fields cut the time by 10x but still takes 30 seconds or so to execute.  


PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
——
Patrick Larkin
Application Management Group
Information Technology
Bethlehem Area School District
https://www.beth.k12.pa.us

On Sep 3, 2014, at 6:12 PM, Rick Draper <[hidden email]> wrote:

> Just to clarify, the hash needs to be based on the concatenation of all
> fields in the record (or similar approach) so given that there is a unique
> ID, each hash will be unique and can be compared for the same ID from
> previous table.
>
>
>
> -----Original Message-----
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Rick Draper
> Sent: Thursday, 4 September 2014 6:30 AM
> To: [hidden email]
> Subject: RE: Advice on report
>
>> That assumes every field in the record is new?  What happens if only 1
> field in the record is changed?
>
> Hi Tami,
>
> Patrick said the table is effectively replaced each night, so if after the
> table is in place a hash value is assigned to each record, that can be
> compared to the hash value in the matching record of the previous night's
> table.  There will be a day lost in implementation, but the workflow will
> be:
>
> 1. Copy mytable_current to mytable_previous
> 2. Replace data in mytable_current
> 3. Run routine to create hash values for all records in mytable_current.hash
> 4. Look for any records where mytable_current.id = mytable_previous.id AND
> mytable_current.hash <> mytable_previous.hash // they are changed
>
>
> Very best regards,
>
> Rick
>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Mark Palmer
You might find it quicker to join the two tables on the student_id any
nulls in the joined table will be new students.

In the where clause you could do something:

WHERE news_grade != to old_grade

Leaves MySQL to do the hard work rather than Lasso, you'll need indexes
for the field that you join on and perhaps for the fields in the where
clause.

Regards

Mark Palmer
E: [hidden email]
T: 01902 620500 and 01285 610035
W: www.pageworks.co.uk
On 5 Sep 2014, at 17:01, Patrick Larkin wrote:

> Hi -
>
> I did the following:
>
> - Archive table of data as “table_yesterday"
> - New data comes into table “table"
> - I “inline” through each record grabbing the student id
> - Inside the inline, I find the corresponding student in the archive
> - If found, I note any building or grade change
> - If not found, student is noted as a new student
> - Write out findings
>
> This works well but takes longer than I would like.  Creating an index
> on the student id fields cut the time by 10x but still takes 30
> seconds or so to execute.
>
>
> PLEASE NOTE:  My e-mail address has changed to:  
> [hidden email]
> ——
> Patrick Larkin
> Application Management Group
> Information Technology
> Bethlehem Area School District
> https://www.beth.k12.pa.us
>
> On Sep 3, 2014, at 6:12 PM, Rick Draper <[hidden email]>
> wrote:
>
>> Just to clarify, the hash needs to be based on the concatenation of
>> all
>> fields in the record (or similar approach) so given that there is a
>> unique
>> ID, each hash will be unique and can be compared for the same ID from
>> previous table.
>>
>>
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of Rick Draper
>> Sent: Thursday, 4 September 2014 6:30 AM
>> To: [hidden email]
>> Subject: RE: Advice on report
>>
>>> That assumes every field in the record is new?  What happens if only
>>> 1
>> field in the record is changed?
>>
>> Hi Tami,
>>
>> Patrick said the table is effectively replaced each night, so if
>> after the
>> table is in place a hash value is assigned to each record, that can
>> be
>> compared to the hash value in the matching record of the previous
>> night's
>> table.  There will be a day lost in implementation, but the workflow
>> will
>> be:
>>
>> 1. Copy mytable_current to mytable_previous
>> 2. Replace data in mytable_current
>> 3. Run routine to create hash values for all records in
>> mytable_current.hash
>> 4. Look for any records where mytable_current.id =
>> mytable_previous.id AND
>> mytable_current.hash <> mytable_previous.hash // they are changed
>>
>>
>> Very best regards,
>>
>> Rick
>>
>>
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Patrick Larkin-3
Good, this is what I was looking for initially.  I’ll have to wrap my head around that, I’m not sure if I know how to do it.  Thanks.

PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
——
Patrick Larkin
Application Management Group
Information Technology
Bethlehem Area School District
https://www.beth.k12.pa.us

On Sep 5, 2014, at 12:47 PM, Mark Palmer <[hidden email]> wrote:

> You might find it quicker to join the two tables on the student_id any nulls in the joined table will be new students.
>
> In the where clause you could do something:
>
> WHERE news_grade != to old_grade
>
> Leaves MySQL to do the hard work rather than Lasso, you'll need indexes for the field that you join on and perhaps for the fields in the where clause.
>
> Regards
>
> Mark Palmer
> E: [hidden email]
> T: 01902 620500 and 01285 610035
> W: www.pageworks.co.uk
> On 5 Sep 2014, at 17:01, Patrick Larkin wrote:
>
>> Hi -
>>
>> I did the following:
>>
>> - Archive table of data as “table_yesterday"
>> - New data comes into table “table"
>> - I “inline” through each record grabbing the student id
>> - Inside the inline, I find the corresponding student in the archive
>> - If found, I note any building or grade change
>> - If not found, student is noted as a new student
>> - Write out findings
>>
>> This works well but takes longer than I would like.  Creating an index on the student id fields cut the time by 10x but still takes 30 seconds or so to execute.
>>
>>
>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>> ——
>> Patrick Larkin
>> Application Management Group
>> Information Technology
>> Bethlehem Area School District
>> https://www.beth.k12.pa.us
>>
>> On Sep 3, 2014, at 6:12 PM, Rick Draper <[hidden email]> wrote:
>>
>>> Just to clarify, the hash needs to be based on the concatenation of all
>>> fields in the record (or similar approach) so given that there is a unique
>>> ID, each hash will be unique and can be compared for the same ID from
>>> previous table.
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of Rick Draper
>>> Sent: Thursday, 4 September 2014 6:30 AM
>>> To: [hidden email]
>>> Subject: RE: Advice on report
>>>
>>>> That assumes every field in the record is new?  What happens if only 1
>>> field in the record is changed?
>>>
>>> Hi Tami,
>>>
>>> Patrick said the table is effectively replaced each night, so if after the
>>> table is in place a hash value is assigned to each record, that can be
>>> compared to the hash value in the matching record of the previous night's
>>> table.  There will be a day lost in implementation, but the workflow will
>>> be:
>>>
>>> 1. Copy mytable_current to mytable_previous
>>> 2. Replace data in mytable_current
>>> 3. Run routine to create hash values for all records in mytable_current.hash
>>> 4. Look for any records where mytable_current.id = mytable_previous.id AND
>>> mytable_current.hash <> mytable_previous.hash // they are changed
>>>
>>>
>>> Very best regards,
>>>
>>> Rick
>>>
>>>
>>> #############################################################
>>> Attend the Lasso Developer Conference 2014!
>>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>>> http://www.lassosoft.com/LDC-newmarket-2014
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: Advice on report

Patrick Larkin-3
In reply to this post by Mark Palmer
Any idea on the query structure for this?  

This doesn’t work:

select studentmaster.studentnumber from studentmaster INNER JOIN on studentmaster.studentnumber = studentmaster_yesterday.studentnumber where studentmaster.studentnumber <> studentmaster_yesterday.studentnumber

Or any derivation I come up with.  :(

PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
——
Patrick Larkin
Application Management Group
Information Technology
Bethlehem Area School District
https://www.beth.k12.pa.us

On Sep 5, 2014, at 12:47 PM, Mark Palmer <[hidden email]> wrote:

> You might find it quicker to join the two tables on the student_id any nulls in the joined table will be new students.
>
> In the where clause you could do something:
>
> WHERE news_grade != to old_grade
>
> Leaves MySQL to do the hard work rather than Lasso, you'll need indexes for the field that you join on and perhaps for the fields in the where clause.
>
> Regards
>
> Mark Palmer
> E: [hidden email]
> T: 01902 620500 and 01285 610035
> W: www.pageworks.co.uk
> On 5 Sep 2014, at 17:01, Patrick Larkin wrote:
>
>> Hi -
>>
>> I did the following:
>>
>> - Archive table of data as “table_yesterday"
>> - New data comes into table “table"
>> - I “inline” through each record grabbing the student id
>> - Inside the inline, I find the corresponding student in the archive
>> - If found, I note any building or grade change
>> - If not found, student is noted as a new student
>> - Write out findings
>>
>> This works well but takes longer than I would like.  Creating an index on the student id fields cut the time by 10x but still takes 30 seconds or so to execute.
>>
>>
>> PLEASE NOTE:  My e-mail address has changed to:  [hidden email]
>> ——
>> Patrick Larkin
>> Application Management Group
>> Information Technology
>> Bethlehem Area School District
>> https://www.beth.k12.pa.us
>>
>> On Sep 3, 2014, at 6:12 PM, Rick Draper <[hidden email]> wrote:
>>
>>> Just to clarify, the hash needs to be based on the concatenation of all
>>> fields in the record (or similar approach) so given that there is a unique
>>> ID, each hash will be unique and can be compared for the same ID from
>>> previous table.
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: [hidden email]
>>> [mailto:[hidden email]] On Behalf Of Rick Draper
>>> Sent: Thursday, 4 September 2014 6:30 AM
>>> To: [hidden email]
>>> Subject: RE: Advice on report
>>>
>>>> That assumes every field in the record is new?  What happens if only 1
>>> field in the record is changed?
>>>
>>> Hi Tami,
>>>
>>> Patrick said the table is effectively replaced each night, so if after the
>>> table is in place a hash value is assigned to each record, that can be
>>> compared to the hash value in the matching record of the previous night's
>>> table.  There will be a day lost in implementation, but the workflow will
>>> be:
>>>
>>> 1. Copy mytable_current to mytable_previous
>>> 2. Replace data in mytable_current
>>> 3. Run routine to create hash values for all records in mytable_current.hash
>>> 4. Look for any records where mytable_current.id = mytable_previous.id AND
>>> mytable_current.hash <> mytable_previous.hash // they are changed
>>>
>>>
>>> Very best regards,
>>>
>>> Rick
>>>
>>>
>>> #############################################################
>>> Attend the Lasso Developer Conference 2014!
>>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>>> http://www.lassosoft.com/LDC-newmarket-2014
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>> Attend the Lasso Developer Conference 2014!
>> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
>> http://www.lassosoft.com/LDC-newmarket-2014
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Advice on report

Rick Draper-2
select m.studentnumber
FROM studentmaster as m
LEFT JOIN
studentmaster_yesterday as y
ON
m.studentnumber = y.studentnumber
where
y.studentnumber IS NULL

Will find new ones

VBR

Rick



#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: Advice on report

R Sharma
In order to reuse a table also knownas self join alias the table name.

Select * from a join a as b on a.{fieldname}=b. {fieldname}

Roger
On Sep 8, 2014 2:52 PM, "Rick Draper" <[hidden email]> wrote:

> select m.studentnumber
> FROM studentmaster as m
> LEFT JOIN
> studentmaster_yesterday as y
> ON
> m.studentnumber = y.studentnumber
> where
> y.studentnumber IS NULL
>
> Will find new ones
>
> VBR
>
> Rick
>
>
>
> #############################################################
> Attend the Lasso Developer Conference 2014!
> October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
> http://www.lassosoft.com/LDC-newmarket-2014
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>
#############################################################
Attend the Lasso Developer Conference 2014!
October 1-3, 2014 at Treefrog HQ, Newmarket, Ontario, Canada
http://www.lassosoft.com/LDC-newmarket-2014

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
12