I recently needed to see what zip codes in Michigan were missing from a table of zip codes that I have in a database. First I had to get an official list of zip codes for Michigan then came up with this script to figure out which of the official codes were not in the table.
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY a.id ) NUMBER FROM syscomments a CROSS JOIN syscomments b ) c WHERE c.NUMBER IN (48001,48002,48003,48004,48005,48006,48007,48009, 48012,48014,48015,48017,48021,48022,48023,48025,48026,48027,48028,48030,48032, 48033,48034,48035,48036,48037,48038,48039,48040,48041,48042,48043,48044,48045, 48046,48047,48048,48049,48050,48051,48054,48059,48060,48061,48062,48063,48064, 48065,48066,48067,48068,48069,48070,48071,48072,48073,48074,48075,48076,48079, 48080,48081,48082,48083,48084,48085,48086,48088,48089,48090,48091,48092,48093, 48094,48095,48096,48097,48098,48099,48101,48103,48104,48105,48106,48107,48108, 48109,48110,48111,48112,48113,48114,48115,48116,48117,48118,48120,48121,48122, 48123,48124,48125,48126,48127,48128,48130,48131,48133,48134,48135,48136,48137, 48138,48139,48140,48141,48143,48144,48145,48146,48150,48151,48152,48153,48154, 48157,48158,48159,48160,48161,48162,48164,48165,48166,48167,48168,48169,48170, 48173,48174,48175,48176,48177,48178,48179,48180,48182,48183,48184,48185,48186, 48187,48188,48189,48190,48191,48192,48193,48195,48197,48198,48201,48202,48203, 48204,48205,48206,48207,48208,48209,48210,48211,48212,48213,48214,48215,48216, 48217,48218,48219,48220,48221,48222,48223,48224,48225,48226,48227,48228,48229, 48230,48231,48232,48233,48234,48235,48236,48237,48238,48239,48240,48242,48243, 48244,48255,48260,48264,48265,48266,48267,48268,48269,48272,48275,48277,48278, 48279,48288,48301,48302,48303,48304,48306,48307,48308,48309,48310,48311,48312, 48313,48314,48315,48316,48317,48318,48320,48321,48322,48323,48324,48325,48326, 48327,48328,48329,48330,48331,48332,48333,48334,48335,48336,48340,48341,48342, 48343,48346,48347,48348,48350,48353,48356,48357,48359,48360,48361,48362,48363, 48366,48367,48370,48371,48374,48375,48376,48377,48380,48381,48382,48383,48386, 48387,48390,48391,48393,48397,48401,48410,48411,48412,48413,48414,48415,48416, 48417,48418,48419,48420,48421,48422,48423,48426,48427,48428,48429,48430,48432, 48433,48434,48435,48436,48437,48438,48439,48440,48441,48442,48444,48445,48446, 48449,48450,48451,48453,48454,48455,48456,48457,48458,48460,48461,48462,48463, 48464,48465,48466,48467,48468,48469,48470,48471,48472,48473,48475,48476,48480, 48501,48502,48503,48504,48505,48506,48507,48509,48519,48529,48531,48532,48550, 48551,48552,48553,48554,48555,48556,48557,48559,48601,48602,48603,48604,48605, 48606,48607,48608,48609,48610,48611,48612,48613,48614,48615,48616,48617,48618, 48619,48620,48621,48622,48623,48624,48625,48626,48627,48628,48629,48630,48631, 48632,48633,48634,48635,48636,48637,48638,48640,48641,48642,48647,48649,48650, 48651,48652,48653,48654,48655,48656,48657,48658,48659,48661,48662,48663,48667, 48670,48674,48686,48701,48703,48705,48706,48707,48708,48710,48720,48721,48722, 48723,48724,48725,48726,48727,48728,48729,48730,48731,48732,48733,48734,48735, 48736,48737,48738,48739,48740,48741,48742,48743,48744,48745,48746,48747,48748, 48749,48750,48754,48755,48756,48757,48758,48759,48760,48761,48762,48763,48764, 48765,48766,48767,48768,48769,48770,48787,48801,48802,48804,48805,48806,48807, 48808,48809,48811,48812,48813,48815,48816,48817,48818,48819,48820,48821,48822, 48823,48824,48825,48826,48827,48829,48830,48831,48832,48833,48834,48835,48836, 48837,48838,48840,48841,48842,48843,48844,48845,48846,48847,48848,48849,48850, 48851,48852,48853,48854,48855,48856,48857,48858,48859,48860,48861,48862,48863, 48864,48865,48866,48867,48870,48871,48872,48873,48874,48875,48876,48877,48878, 48879,48880,48881,48882,48883,48884,48885,48886,48887,48888,48889,48890,48891, 48892,48893,48894,48895,48896,48897,48901,48906,48908,48909,48910,48911,48912, 48913,48915,48916,48917,48918,48919,48921,48922,48924,48929,48930,48933,48937, 48950,48951,48956,48980,49001,49002,49003,49004,49005,49006,49007,49008,49009, 49010,49011,49012,49013,49014,49015,49016,49017,49018,49019,49020,49021,49022, 49023,49024,49026,49027,49028,49029,49030,49031,49032,49033,49034,49035,49036, 49037,49038,49039,49040,49041,49042,49043,49045,49046,49047,49048,49050,49051, 49052,49053,49055,49056,49057,49058,49060,49061,49062,49063,49064,49065,49066, 49067,49068,49069,49070,49071,49072,49073,49074,49075,49076,49077,49078,49079, 49080,49081,49082,49083,49084,49085,49087,49088,49089,49090,49091,49092,49093, 49094,49095,49096,49097,49098,49099,49101,49102,49103,49104,49106,49107,49111, 49112,49113,49115,49116,49117,49119,49120,49121,49125,49126,49127,49128,49129, 49130,49201,49202,49203,49204,49220,49221,49224,49227,49228,49229,49230,49232, 49233,49234,49235,49236,49237,49238,49239,49240,49241,49242,49245,49246,49247, 49248,49249,49250,49251,49252,49253,49254,49255,49256,49257,49258,49259,49261, 49262,49263,49264,49265,49266,49267,49268,49269,49270,49271,49272,49274,49276, 49277,49279,49281,49282,49283,49284,49285,49286,49287,49288,49289,49301,49302, 49303,49304,49305,49306,49307,49309,49310,49311,49312,49314,49315,49316,49317, 49318,49319,49320,49321,49322,49323,49325,49326,49327,49328,49329,49330,49331, 49332,49333,49335,49336,49337,49338,49339,49340,49341,49342,49343,49344,49345, 49346,49347,49348,49349,49351,49355,49356,49357,49401,49402,49403,49404,49405, 49406,49408,49409,49410,49411,49412,49413,49415,49416,49417,49418,49419,49420, 49421,49422,49423,49424,49425,49426,49427,49428,49429,49430,49431,49434,49435, 49436,49437,49440,49441,49442,49443,49444,49445,49446,49448,49449,49450,49451, 49452,49453,49454,49455,49456,49457,49458,49459,49460,49461,49463,49464,49468, 49501,49502,49503,49504,49505,49506,49507,49508,49509,49510,49512,49514,49515, 49516,49518,49519,49523,49525,49528,49530,49534,49544,49546,49548,49550,49555, 49560,49588,49599,49601,49610,49611,49612,49613,49614,49615,49616,49617,49618, 49619,49620,49621,49622,49623,49625,49626,49627,49628,49629,49630,49631,49632, 49633,49634,49635,49636,49637,49638,49639,49640,49642,49643,49644,49645,49646, 49648,49649,49650,49651,49653,49654,49655,49656,49657,49659,49660,49663,49664, 49665,49666,49667,49668,49670,49673,49674,49675,49676,49677,49679,49680,49682, 49683,49684,49685,49686,49688,49689,49690,49696,49701,49705,49706,49707,49709, 49710,49711,49712,49713,49715,49716,49717,49718,49719,49720,49721,49722,49723, 49724,49725,49726,49727,49728,49729,49730,49733,49734,49735,49736,49737,49738, 49739,49740,49743,49744,49745,49746,49747,49748,49749,49751,49752,49753,49755, 49756,49757,49759,49760,49761,49762,49764,49765,49766,49768,49769,49770,49774, 49775,49776,49777,49779,49780,49781,49782,49783,49784,49785,49786,49788,49790, 49791,49792,49793,49795,49796,49797,49799,49801,49802,49805,49806,49807,49808, 49812,49814,49815,49816,49817,49818,49819,49820,49821,49822,49825,49826,49827, 49829,49831,49833,49834,49835,49836,49837,49838,49839,49840,49841,49845,49847, 49848,49849,49852,49853,49854,49855,49858,49861,49862,49863,49864,49865,49866, 49868,49870,49871,49872,49873,49874,49876,49877,49878,49879,49880,49881,49883, 49884,49885,49886,49887,49891,49892,49893,49894,49895,49896,49901,49902,49903, 49905,49908,49910,49911,49912,49913,49915,49916,49917,49918,49919,49920,49921, 49922,49925,49927,49929,49930,49931,49934,49935,49938,49942,49945,49946,49947, 49948,49950,49952,49953,49955,49958,49959,49960,49961,49962,49963,49964,49965, 49967,49968,49969,49970,49971) AND c.number NOT IN ( SELECT zip_code FROM tbl_zip_code )
Originally Posted on February 23, 2015
Last Updated on October 26, 2015
Last Updated on October 26, 2015
All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.