November 16, 2010 at 9:43 pm
G'day all,
I need a push in the right direction here - I've looked around other posts, but I have a feeling I need a multi-pronged approach.
The friendly context is there is parent information in our database (mceetya if anyone is familiar with it) that holds school education, non school education and occupation information in the form of an intiger 1-9. I need to sanitise the information I have and write it back to the database.
The information is parent based, but for reasons I cant fathom, the company that designed our database records the data per student. The problem is that the family data needs to be written against each student belonging to the same family for me to do demographic reports correctly and the data is out of sync.
Warm example of data I'm working with.
FamilyKey StudentKey Parent1School Parent1NonSchool Parent1Occ lastwrite
brothdj alex123 1 3 5 2010-01-01
brothdj roger321 null null null null
brothdj steven213 null null 3 2010-10-01
My task is to backfill the parent data, against each of the student records. There are some considerations I have. Alex's record is 100% filled out so a good candidate to update the other two students with, but Steven has a more recent write date on the one piece of information actually filled in. I dont want to overwrite 'good' data unless necessary.
I have no idea what butchery of joins and case statements I'm going to have to use to create a 'master' row that is made up of quality checked fields. It is true to say it is not always the oldest or the latest written mceetya record the best filled out.
Here are my workings with the actual sql code:
select student.family, student.stkey, mceetya.mse_status, mceetya.mnse_status, mceetya.mocc_group, student.mceetya_date
from st as student
left join stmbd as mceetya on mceetya.skey = student.stkey
where (student.status = 'full' or student.status = 'part')
order by student.family asc
Here is the data that the code above produces
family stkey mse_status mnse_statusmocc_group mceetya_date
AIREYKA AID04063SH NULL NULL NULL NULL
AIREYKA AIN02680SH 3 8 2 16/03/2006
BAGGETTOAF BAA02693SH 3 8 2 16/03/2006
BAGGETTOAF BAD06082SH NULL NULL NULL NULL
BELLJK BES02593SH 2 5 2 16/03/2006
BELLJK BED06086SH 3 NULL 5 10/10/2007
I would very much appreciate a kick in the right direction. Thank you all for your time!
Sorry for the way the data lines up, I've tried to fix as much as possible.
- DamienB
November 22, 2010 at 5:53 pm
Hi All,
Another half day of thinking and testing provided me with some type of usable result 🙂
I ended up writing different select statements with different WHERE conditions and joining them to a select that used a case statement to manage preferences.
when preference1.data is null then preference2.data else preference1.data etc
I can see lots of reads on this post, and I dont blame anyone for not being able to slog through my question. Thank you to all who gave it a whirl 🙂
- Damien
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply