April 9, 2013 at 8:45 am
Someone at one schools has reversed the dates associated with vaccine doses for various students. For example, a particular vaccine requires 5 doses with doses 1 having the earliest date and subsequent doses having newer dates. In a lot of cases, someone has completely reversed the date with the dose, e.g. Doses 5 is the oldest date and doses 1 has the newest date.
Using Stu_ID 5846 as an example, dose 5 should have dose 1 date, dose 4 should have dose 2 date and dose 3 is correct.
Does someone have a SQL example that would update the dose 1 with the earliest date and subsequent doses with appropriate dates?
DATA:
STU_ID_Dose_1Dose_2Dose_3Dose_4Dose_5
58465/6/20038/25/199911/16/19989/11/19987/17/1998
411112/16/20033/26/20034/15/19982/13/199812/8/1997
25484/24/20038/12/19986/23/19984/13/1998
251184/26/199910/5/19987/1/19984/30/1998
251197/29/20024/26/199910/5/19987/1/19984/30/1998
54548/5/20035/22/200011/3/19981/29/1998
319369/17/20031/28/20011/28/20005/11/199912/11/1998
255723/20/20029/11/200012/15/19989/24/19986/10/1998
60044/2/200410/9/20001/10/200011/8/19999/8/1999
63773/25/200411/17/20002/16/200012/17/199910/18/1999
53279/24/19999/28/19987/27/19985/28/1998
996611/10/20058/15/200312/29/199811/2/19988/31/1998
994812/2/200510/30/20031/14/19981/20/199712/20/1996
49791/28/20036/22/19999/8/19986/30/19984/30/1998
46444/1/200310/13/19984/8/19982/10/199811/15/1997
77496/2/20043/15/19995/18/19997/19/19994/18/2001
49808/13/19996/10/19998/26/19987/15/19984/13/1998
54728/13/20034/27/20026/17/19994/8/1999
74376/2/20045/5/20008/16/19995/21/19993/7/1999
81504/28/20042/1/20006/17/19992/13/199912/19/1998
81514/28/20042/1/20006/17/19992/13/199912/19/1998
243707/14/200411/16/20002/4/200012/6/199910/5/1999
49813/7/20037/28/199912/4/19989/15/19987/16/1998
686663/21/20032/15/20004/15/19992/17/199912/7/1998
54518/12/200310/17/20003/26/19992/18/199812/11/1997
623332/24/20046/19/20007/27/19994/30/19992/26/1999
54608/12/20037/27/20001/4/20004/4/1998
April 9, 2013 at 8:50 am
For documentation purposes I would create a table with the same schema, populate it with the "wrong" rows then, just run an update statement on the core table updating columns as needed from documentation table where pk is the same.
I strongly suggest to test the whole procedure in a test database before doing it on production.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply