How to return a value if an INNER JOIN isn't satisfied?

  • Hi,
    I have Historical T-SQL query as below:
    SELECT 'North Historical' AS Region
         , Grouping_Level
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
    FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
    WHERE Region IN ( 'TOP North Historical' )
    GROUP BY Grouping_Level

    that returns the below dataset:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAcAAAAAtCAYAAAA5iLubAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAABOASURBVHhe7Z17cBTHnce/IrEjExNDcXURheFAWglZiBwuIIjl/gAHhCRsl3iUEtvxAQFLBuRIEHCgcjF2DpdVwGFhg40U48BdJYdl3lgSIj5D3RWy7FCFY1YS0a5sqnxcSO6weQvz0F53T89Mz+zM7uxqV7vL9kf82OlfP2Z+v+7t18zupvkJSFA2/MsbKPyBm4ckTnC5XHij7l9RseQprpFIJBKJFQk/AEokEolEEgsSegCUSCQSiSRWpL2797AcACUSiUSSchhWgFeuXMGgQYN4SBILpI/7H+lzHekLa1LFL6lc/1a2D+CvEolEIpGkFIYBUN4OjD3Sx/2P9LmO9IU1qeKXVK5/K9vlClAikUgkKYmzAdC3BVPT0pBmkopmHi+JDs0VRh9P3QIfj0oKaDuJ1TXHomy1XRsasg9bpuq+b64Q6kMUmsfifaEX1YwKQ5jD8lSQWAti6b9QxNQXIfImMiHssopX0iRp/Rts4HEUWzuTuG4JzrdA3bXwkniahom3Fp71EVRWPCs5AbD1MR38SoAm0cf/1IHsWPkqherB1ucUtxtuT5fuB18jGlr5MaG4Tq2PJpSTP61+6oqVBIb3hRd569WOoxira93wdBk97GtsAGpXk9j4EB9fOMgbTSJo2/RabAliF8PcNxKpK07G+o/EzsSvWxV6TWYivwfoykF+awe8POgYVxVOnKiCiwdTDWsfk5n2eg9qvXXGN0ZxHfyx8lUK1UPwdl2GsvwGNKrvKG8HUF6OyL5/yIWqXfrE0DW7DGhoFN6sPtD+r2x2/LweL18kOs77Pud2JV/9iyRX/TnByvYBN76+BSp37vSS15u4ffsOCxvk5h309t7B16KuoxOfFozBSHa8GVO0pa8bmzrUNKKeyJTNaKc6+hoQ/wwOqjoSv2mJnm/JIV5ekoutjzsOYXfvPMwYIegM8dQnz2DJFO6nIH5jfhXDhrwW6fmrpb9pnHYeImL5VmLVTqgYyuHtg+nMbUWMM6W3KzuEOGnXM2bnYffBTqY7uPc05sweY3Gu2yCpcVPUWV3TiEw81OpR/DSiEHN638EBzUa9ng8K/qbCfK6WR+23qkv12OwbNV0IiasvNAnMa+kLZrNdmydi649ObHq6Gq2t1cgO1V65hN33iXZZxauShPUfkZ2aJF7dqqLabmYAHRXVkbG3txf0SNWJgrafIT/9XtynythOrDlWiUy/D68vfhdzPV/jeg8RzzzsXfwafEz/M+QdUPWbUEBKZ2WxV2P86Y0elC5v1s7V+Zier/0VWp7xepJRgvlY8QmXI8t0P09TbEebB2Peoj5ZiS47v4llCGE9r3V6a3/b118wMV+DUs8W7SOzEmsWf4i9TT6Wztf0LtoWr8HyTLv2ZFV2aKEEbde0zKxcYE8TOUczDrXPQ1GWzbloYSZdYDoXxhR40OWjx1komuc32Ih5xeQ940fhVm6fwed6eeZylbC9b8S0dkKJny8EMeW19YVNm9fbopU/mlD0Fokv2ITTrH/Sz2MnlJB+MeiMdgX0jenL0MLSJWH9G3RO7RSEnkQIx7tuVaFQ281oW6BkkNUSWkJPqhrS04ENBW/hUAvRdzdhX1sbns//FgbeRyR/Fdra9qC5heixCStmKdkDoPmE+Kzi+Sho96KbBsi5bPMlMSF9rDJrm9AYOAXzUZxFXoP5zQ41L4Gl33EYR5WggpW/TeeJGLv2QS64cMUmkN6BXHs3mvcCG1YU2aePcB/Gkc+zSjAXZ9Ddchhvj80Gd1WEeHGmLR85gr9FG+eqFdH9Oh6h9qk2KtrgBPGlE+LtC1vsfGHT5jWi1FYcvy81THYZ+kYq21DIo5Ku/g04t9OWONetip3t2gqQRrF4NWwQllYIZ2L5msV4+9ARHrcY+6/fwDVNjmM5d1pgGeZXu3izXg0nsTA7lP8M+sxi0unQyjXpLX0iHtulCaEvyCWzJ1WvvjrIZwhbiVUaqrFoH+QCNLvZZGk+iqjOLn1Ae3IoLI/yX2A8K5K8ZqJorgc1NR785LFZgt6cVgyrOlM6nxcdzL88bGnjEVTm78Hc09y+08pEhyTn5amvvAyD3saXWtogopZh1jOhZdOoGPqCCUslhEP5Qs0jlq2G6f9RaCssrfJfYDwryqgz2GURL0rS1b+gC8dOJiyFEE6AulWF5VHyikT+OcDCx/GTtw/haBap4IIdOKwuKbq34gcDlyt6rEKtYakhYIrvbt6Dtry+zjiTlSwyoRiL58cRv3EN0ILnxlnMDO38lpWNvLZOMs/kdHfqeQW9Yz+Hqj+n2LUPFsginS3wfOkqOjVWrilo+tjBZuptY/FYyCltMLqx7ZlVyFtTKfhXsXFfzR7dRsZYZKsBsa4odnXZT76JnS/sCOILil1bjEtbCccuSvLVv0K4dtqR2HWr3wMk/4LtD7MYgy4LY+gFtmRi2X8cAErT8e2BRMbtwZxPt2ImmUkuq98Ij6annblShlKWMf57++bh09cLWdnmcwWeOzmFmmHr45lbcXU/MIf6iskrGLN/I77Pbdd9YOe3QlRvaNfz17TreQva8QrXW/nZ2t/29adds4X0tq3C99RroFLptWkfSvrMonnkOgswpyiTl2HXngKv04nQLI7adeZyvH9Nvy6rc1HEMBWjvWNx5uc9eG2mMQ21sZe883UbTXVVuoOWzM6nnNeuLu19I57PTmgR8fYFFWPeUL6gaezaoo0/Ml1kAKHXsjzwHpWF0KKC+iWEXQFtnsj0rcp9PypJU/99tJMKRQ/Hv25VUW03k3bl6nUSBdyX/i3897n/QUbGd3Hr1m0WGVWOVmLQe4/jymt9mlYmNTH3sRWfkZnTEuDXH1Qik6siIknrLy4+T1DuGl9EuS2mShtJCjtj1M+otv/dyAe5RkF4ClQfKVVd36QFP71/IAapMrcDNVUzLdKljkTfx06E1TN6A/ShxK7+THpNKsOakfWXxMfniSnJ64vY9iXJ65fwJDHt7J9xQrPdRNqly1eZdiBZZn7xxTkMG5aBmzdvsUhJdJE+7n+kz3WkL6xJFb+kcv2rto8eNZJrFNLa29sDh0WJRCKRSO4y8vLy+JFC2q1bt7QBkC4R6afuJbFD+rj/kT7Xkb6wJlX8ksr1T22/5557eEgh7eLFi4YV4ODBg0F08jXGrxKJRCLpPx544AF+pJNGRkVtALx06RJu3In8o4GS0KR/o9eyIiSxg7Zr6XMF6QtrUsUvqVr/dnYHjHYZQ7/DjySxgK4AJRKJRBJ/AgbA8xcu8yNJLJDbn5JU45NPPuFHEkliIVeA/YxcAUruds6ePYsVK1Zg9OjR7IGLhx9+mL0OGTIETzzxBNraAr4QS5JkbN++HcOGDWP1qsr06dOxaNEi7N69Gzdu3OApE5vwVoCfv4FHh85A/ec8TKG6ojdwlgcd4zSfVbpIzxkLwrwWJytA35aphoZl+FVmO3xbMNVpHvrr82lTscXum9VpWSn5q/3NqEirIP8rmOtBET3enJ4i5pkqOLi5QijDpnLs0jjJG32c+4IOeDU1izAybSTuJ4McHfhqa2uZXoS2fdo5TpkyRS8jKXwhovhl/ZEj2sAerjw4NF07FtuISPztD17/S5cuxfnz53mswvHjx7Fz5042yaGTnYLswVr6RLUzghXgx3hhaYSDT7QHrmDlhXuufhpUQ60AaUPLbiiD169+i0ETUBJqECSNNbsBZV6ex1sLT4mxYxZpPlCP8vJ8NDSGGOJSZiD0YctU+mYrQT3XUFxVJ3gd6H51l5ei2CY99deC6nw08XrLr16gTDKIfr2nltepF7We9YGTD7s0TvJGFee+KFhcgv9c+ywb8Nau3YkvyN+1MLb4p02bjFc+eTGBfSEi+qULvywujnhr99yXX7PXhQtn6m1EJK72B6//np4eR7tYdAX4ke8SO87IGIJh1fMTzE6F8O8BTqrBr/LWYNv7PBxPRi/De0eWYRQP9ololhWE4CtAHxobgNpdVXBxDUh3W0caQl0xD1rh64LHXYbZaiZXFU7460hOK3zo8pSjtK4U+Q2NJBQEWs4J8VruVlyoOkHfbE0o5xorVj6zFd3vrySdQzZe7BiMV18t19LT1c7xj3z4MvcODq5di+nTN+C397aiOpt0Jtn8V6zZjDYb1a2Knm4hrSVpGd4OtObncF+7kJPfig5vEH3McOaL9T+tx+fH1pCVXx3XhM/x4x/h0F8/DrQnYXwhovtlIFkERIOdO3+PCWUWNsTV/uD1T1d94T7HcP78Vzg19I8JZqfCAPFn4ylO7gEWPleDzk0WqyW6iiL5aRkZQ6vwgaorqsLqIqKbuAYn/7AGBcJK6+hKNf13sDrcQVVYtZ3dPkMr59HtR1G/1HSuUNdG9dp1HcVqLa1+XR8I1yrqwyHo7MnXCLKO0wcyM2RmpG9zCis812ySqxoLnEyT6Dny6SomG3nkbNoiUCybdtiqLiW3QgPZ/s9lePW4D3/57AwL005gxYp6MkuuJwv0NLYKmv7jrThzZgfqamrYdtC1myypLbQzqSFp6X0TX5cH7rxsHgNk57nh6fLZ6uPJkbefxS+bOzRf9IUPyRvuo2OHeUghkX1x9uxfcJ38RYt/b5iGUtNMNZHtT09P50fh8dkFIHeI8d5vItip/yAuEYqjp0DJamllwCqwmw06D71zmZXR9nI7nlzJf73pD+3IfpPoT9ZgIllBtqkrLTJAeUuU9DTOclCl0IFMGHjYQMqjFI5i2y/G4ne8HPyiEa43xXM5vDamIGmL5gM87fkLe4AfKvc9H9ms6pT0ttcbhHBnTzo+bFlQjfwmpa68tR6UaPuidNbmRVlDNh8c7e/v+cgSM5+941yYXQa+DWos298UbO6fmryw6QA/ij70vknnuWs8lPgsWb2TH0WHa+c6+VHiQ7fzosmYHT+32alJLI7we5501yJShpzp+4Qp2kT8FOgjm8nAIA4An7fgEGqwfIYSHDVrLiZ2+JT4SXNROJqpjZABSk0fFDqQqQMPH3yUwUolC9mTfoMnybU/2jIL713Ygkd4DCOcazOlJetdbLzwPsppGnEVGTAIOyPip0DZ6rAWq/m7xUVGL7enS1idqVsXdADLR/UCq5Ub3WJtRX2JstLLrm5FK90GNZVNplxw80MJnfXvxP9eju2XByfLAHjx4h7tHla0uHfQ3/CjxCc9/V7S2/TtJ2JVRo0aDtevX4rZ/a1oQQe/4j7c81T5/V/5QQIR2QqQUYjlpftQ+fqfeDieZKH8iDI4rux6mAxQfIszqhzF6on78PhJu0HYGUFXgGwrU9iWjJTiUpS3diBg25xtfzZp9c1uMNPzxWh//W7hy91v8qPYkZOViVbhRoe3oxX5OS64cvIt9fHi/3a9im/e5/yjUgUFBVi3bh0eGv5trjGSORSYUvwkDynY2ZwovphC/nKJXX0hIyMD+/e/h9IfDQh4GC3R7KdPfEaDj/9oHAETwc4+fQ5w1LPP46Fdv1FWQqNn4XHo26JnW/bhZJ4r5g+VMNjKTN2mPIVfTWqHr5vHUcK5NlNa5X6g+tGPsXCpq8XuP8VgBahsSxpXb/Rx5DRUeJX7fBv5rifdylRvFLNHlMXHRJsPoN6dB30XXYHmgbC3rp6voSsnsGzlUEJq4r8OfxPjZpbycPQZNWoUin44U1jR0weV3EpV0dW4lT4uKA9pTS41DlgiQ0jHvnDhQrxZ+Q/AhBfwbx9+iBdf/DH+cfg1jJhQxFMp5OZmYt6Df49xY033lexsThBfDCR/m4ldx44dY4P7unXPoWjECBQ9R4/N8hQmkL+n1PBTE4jhi9HY2Ynx4+9nOzIBnXsC2U+f6jV/nCVSHvzu3/IjTiLYefVaj18Vsjrxk9WNvZys8U+cVONvE3RtL3/fD1VH40F/bpGIqNPy7PE/zeIX+X9nLsuibFu9qhPi2HXwc098+RRJJ5xLzcPjLa/NcB41ryJPv0N1xnNo5RjyhRbq41B4a92G85Q3aRF+t6p31/q9XE1pKtfTA+V+NYtOk78cbn+tmInCyiTpDWW7/W5aPtWZzpOMOPG5AvWRyXdN5X6QCvjqq6/848ePF3wcHSGdqX/rqVP8VEKcVun2+kiIhi96enr8RUVF+jVxGYqh/t/++c88sfV1nyK2krExQG8+X3/4QiQafrEmML21Df1jfyR2kkm7fs4+SHNzs6FcSn/Vs53daVeuXqcnYdy5fZOtUEhHzTWSaENX2MTvPJTg0KdAFwC7kvyjENH6AmDyJsJLL73EvgUj1Ddd0Kflpk2bhsmTJ3NNIGRAZWkivi8cAdH8MmT6jS70/hCFDIhsuzNZkV+GbQ/dAqVtvi/QXY5OsuqN9CnSvmJnd9rlK9e03rj3zi35axAxJml+DYJ+W0xJPVlMNsEf9EOIiU+0Ozf68QX6MYczpqfa6H2d3Nxc7TURSZWOPlzkAGgPnezNmTNHm+yEC30/kNUfm/DFC9sB8NLlq9oA6O+9LVeAMSapVoB3CbLT15G+sEYOgKERV/xOiccuhxW2A2B7e7vWGw8fPlyuAGOM/D3A/kd2+jrSF9bIAfDuxs7uATk5OVCFEs5ToJLwifdMSCKRSCQKafTpGH6sQTtpesNfvsbmVSKRSCT9h92qN40+CsqPJRKJRCJJGeQNP4lEIpGkJHIAlEgkEkkKAvw/EGaU5m4GzJoAAAAASUVORK5CYII=

    I have National T-SQL query as below:

    SELECT 'North National' AS Region
         , Grouping_Level
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
         , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
    FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
    WHERE Region IN ( 'TOP North National' )
    GROUP BY Grouping_Level

    that returns the below dataset:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAbYAAAB0CAYAAADpXhd7AAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACBMSURBVHhe7Z1djCRXdYDPECkYJwYkHmIeiGC3ew3jhieDlp6XhNjr7ZlgjddoHpDQKNjMBO+SGYzH2YdRHqJFrJiV1YNt0EyAaF4iaNZmvTDdXouQvExnFPyEe3thuzcgESJekPzDr3+2c8+te6tuVd1bfbu7urt+zgd3p+rW/TvnVNetc6tcZ6bHAGJkXn31VbjtttvEHjEtyA4epAs9edFLnu3/FvGXIAiCIDIBTWwxQY5vMiA7eJAu9ORFL3m2P01sBEEQRKbwT2zdbZibmYGZQFptiOPEcDRW/Tqd24auOJQK8LwY15jH2baKPLd9J3MXtuc8ezRWFRupCetofhteUw1Y9e0LeJ1VdlTDpOTWMVZd9KmbZPrIpTvulEmp/X0yiGOIUc702DbssZWr0GEuLLqxPHWq0Do3hAGmabgpYHT7cVKbB6irOt1sQ3FcusmZ3oMY7YCUy1BuXfd0092HWlNsMyo70kZ1WGH/c222U3EK+H4bHZg9Jy8IFdiolqF13a/17n4NoLrBjk6H6ejCom6cDHG+41iMRMjFCV4fWdqppNH+w8iZfNtK+i9FFo5BqdmGjti1prAGBwdrUBC7WQeNG4bdBZ9rQbWz4z+5KzvQG5ducqb3IHo7SJZgqVSDfflL6bQBVlagLHYHowBre95NX2FhCaC2r/wIu4DXtaWF6VliWrpIOtF6UbGXK332V0mX/Wx4yx/++Dq46bU34ebNN+GPal77Gvz4+B3wl3z7cfio636W4UJbllHzWfro43AV8/Bv6Phn4FmZx45feMir99Bl0V7K0ptv3mR/X4M33njTf6x9Gb518wG4+z1Knu846uAz8NBHhV4i9MT1qO776mrKi79a/eIxtx+W1PZ1SXdeYPK1I84Hnhc8N9RjgfKmtodIRjtgEv3cvTAL33r2Gs979ukX4f6FOzT9vwGsNLym5unG+Z4j8IFmy9Hde07A/Te/DZdcuT3bP6vYABO3g2wPdaKzr9wO6kuW65Omqgs3hetqdcFlNv0OWDLq4xpc+NQ6NJvrUOx3DotkoxejXLrjMqXQ/kPJ6abk2VYmlP0tOKurCQ6/AKVb/hTeJtOd1+Dsf5yBI70uPPHgd+BU64/wu9+z1HoAnn7wK9Dl+V+A2Usy/wIcB9EW/+s//uJWCxZPN9y+rn3cq3f1S9iefzxpSMjNmzeZtM5dkv+Ysv/cw55e/8qRFQ5bcMfXUQePwHWTntQ2lH2vrr68Xr9me0Wl4Bgcu2rOhyNn4OyD/wVP17u8XLf+HTh88CycPmI6f3RtD5cQkx2c4+zv0fcDXKyzfhtw+eoDcPKooX9sLJAXLleAO4634HoXt4/CyQd6PrnhgQr73fTgxJNCZp8dvPaC7Tr7Zn2pZU0JmZ4ulBSoa9SF4XfgnZ86fdTh5NfZ8eMX4EV+jfL6MSWkr158eX65QtfHWx6GK7xcCu3vy7OVU0nYibI/bdvKhISXIrEhObjft+HLx78Ol6+w/Bt1eObwEB4rvRVufRtLpUfh8PAiNK6wfLgAn7/XqR4C6ynHj1Y+AcevduAG7rC+jPVSBLvJcBUayb1PKQYVHP8EVI6yv1F6MiHrMnj5b3wPnnd2HXT6DfQzNKbzgQ34xOcvAPuFs7HfgMbTAF/+/Elz+RjXPqzscHQeTsFP4MaV78E37yyCUN+QdOAnhyU4pthAlfuUNM6NJ+BjKLOU28mNJkK/NkxbF0ZMujD8DlxiOn+sf6suAbl810dMT8EJcSh19vdhL6eRKdtWgrIHPDbngLd/BE6ffRC+efk5cexB+O7v/gC/ddN/wmmhiHAbwb+m48F8uZ+ixMft/OPLP1JhFw40UCBfqwN121SmT/7x97M7G5kv/1rU8+3rkq4M5mjOBzYAV25+0/MJOIl5pvKh82eExNtx/gkf592wv0fg5KkWnD/fgk9//F4lP1hW3Zd5gXLdDrS5zsW+Vu7n4EzpIpx6Ucj8onNTw4qL9uRf0YYv36Bft2xEkm0E83nCtvHQGHXBEy+l7PfThayjti338d8Yzh9e1vknfJw35c/zyaU5rqbU2V/JG0ROnngJZT8BtpWJ1en/8siJ++DT37wMzx9lRjv+DfiedAluPAl/c+tpJx8eharPVVAIHL/RuAiHs6PeHaaFo+zG4E547INMTyIH4Ap87oOauzaTno4WYfbwGrsHFNy45tVV8q312s9etpjOB75zlF0wAR5bfBRvW50xRZafLPzO+vBO+HjfW9AobsBTn3kUZs+eUXTuyP3M+Yue3Jw7oSh3VPshJvtOSF/j04WJCF0gpvNzKufPIHIh6bO/w6BymkiObUPP2PiqrC/vKNyBnV45Ag//+yWAxVvgz25l6YMX4f4fPwn3sLu+h3e3oOXm40XbacNpy3/8Q888AD9+4gRvO9hXuO90JBy2cT37nifhN98FuB91w9OX4I7vbsFHhKyezCY9nYD1L1/16p+/6tU9fhW+JPJ1etXr12wvd8yadPPwUfiQHAOmMx3D+eCUP3LyATbO43D/ySOiDdP5Ex7nsAmbiXqu4PZz5DT84LfeWHX9I+o+Jr8O7oSf/OPv4Sv3+Mug3DfZL9qTO2C/xW9gy7w/p1+Tfc36UvszJWxi2rrA5K/bTxdYxnR+GvRxpMAmBhzL6fAzIE3CpiL10keu0O+Apb9+0nmuhik19h9RTkyItz9928qETc68+pvfsT8x8vwZuO3798GrXxnpFjBVvO2Wt8L//vL/4Pbb/wJef/0NkTtm/ofd1TwE8C8/PANHRNZQZMheU7FDQsmMLmI+P/NyjqRCzjFde1D2kMc2eLoC//Dnt8JtMp1qw/m1ezTlspvwFoH/xf8Hjo0vOUa8Gcrvl0z2CuS76cxAd0vTTNOxQzJTenUx3utJevUyWEqmnJOZK1DomZdf+Q1uESNwK3Off/GLX8K73307vPba6yKXmDRkBw/ShZ686CXP9kfZZ65evUoTG0EQBJEZZl5//XWa2GIAXWD8r+aJ6UJ28CBd6MmLXvJsf5S699JLL8E73/lOoL+j/yUIgiCmy8yvfv0KeWwxcMuf3IR3vOMdYo+YFi+//DLZQUC60JMXveTV/ih3//9Am7ACPTaCIAhi+tDEFhO0DEkQBJEMaGKLCfLYCIIgkoF+YvvZV+Fv33U37P5M7COYd/Kr8HOxa41tvTj7jIMB+7bx2Lrbc/wtJZkSFk09l6ih7lV7+ELghwzVgFVTyH8FbGNuO/iJcru6kyJaTgdTGZu6aSUvesmqnBEe23/DP312yEll6AlpwD7VfqY5CTL6eWw4qRVrS0q49TrA/ICTW3f4UOmEhsYqzLdkCHy0h5hwmJ7PufkdqLbOgTM/dWF7Dn/I87CLu1FgG75CA9SdFEY5FUxlbOqmlbzoJcNymie2D5+Hf549C0/9QOxPglH6fN/D8P3nHob3it1JE+2xOWHiq3tr4AWKr8AOOyl2KmKXmDyVHegdSJsUYbaMgRbZZqcNzdIxkV+AY6UmtDvO9toB/pDrsMKPmWCT2HINSitlsY/Y1p0gRjkVTGVs6qaVvOglw3JGPmM78bnzcO2CxgtC7+hdb4fbeVqDH8q8k2uwcZLl3XUWXvjRWTiueFDPPyLLvx02IiYuU58/VOo7bdyA3c8q/QS9N7esOr6vwq5mHOG2nfxBiPTYuvtQgyVYcM6CMOzuZ0669Moylbp0ObfdYBdLESpdeG3+4wm6XUojaKNmCY4xG3Wvt6A8WxQH2JQ3W4YWn/EsaWzBemkTNmbFfkKxkdNUZmQdJZi86CXLcka/PMK8oEdCHpQzoXzg26/Ar379Chx+8Sp88hEROedHV6H4NZb/wnm4i3lfh9KDYpNPZ94pj8e0k6VE2yfAxx4X9d02rsCJrwX64USNTz+OcNsR4zMw/FuReHe/DqU63s33oFNtwTxfn2zA1noJ6ujqd6oA65fg2F4VymXm/nMvI3h8KzHPbdIGv0Eoog12mB89Kg1YnQeokytOEFMjemJjfOzxiwDqhf5nV+AynIfTdzu77733FNzV7jrHP3wKTryPZ/thk48sb0OoT0T1wtAjFNkhIsdnGIdt2xEM/VYk9+aqsCGug4WFJSi3rrPpDpfGdmEevbH9BTjoBS+6/Y4TOtwH3srDzcLaAX9OMHtu9Bd6utvnoFXdIFsQxBTpO7EBnIDTi8/AmSd+KvYnQbDP52Hjrmfgvhc8r+oucWR04mk70mMrLMASm772B/LU5TOZHmy2i+xiHHyTrt9xQkdlx9FZL+RRFWBhyVlOKRwrQVN5YNBpN6GEa5R9wWepTWiuoz1moLjubCd1mdhGTlOZ4XWUfPKilyzLaTGxMa/n7x+DD+z9q+PJvO9euA+8pcKfX3kGXpgtxP7Shq9Pzp1QkN7gjZ+avaqhxmfZdgTRHhteNAHWl9U3GvG1b+YhdHDSW4ctMSt192vOQ1n+3G2Ov2lU2elAFV9s8M4jdA3Cx5N5/UwsfAnSddGcSYn/OIuzwmt28q+3yqA8TojAu9lwlpXLUK524GAtmRc2KzlNZYbWUQrIi16yLCfzUnqh9ML53l0fPt87VPIOv/iRHsg8PA4Ywo4lNc+tc7H3KX7873r/FmxL07YpX+2Tb8s+3X4N/eC2r5ymfWVf27ZmPFGJeWzsWhYNu9D5+lmpuwd6ZZlfrvY6brZXnl0gWU69t8L3V9iW7jhhYweV+orQe0CHaj64hpKgHRwbOAT3HdA+Ybvoy44DG13o5fSP0aSLaB0ll7zoJa/2R7npI8gxgc/nmE7FHjEt6MO/HqQLPfQR5GxDH0GOEXaXILYIgiCIaUITW0zQtyIJgiCSAU1sMUEeG0EQRDKgiS0myGMjCIJIBjP4BglelNHjoL+j/SUIgiCmzwy+Him2iRGgN9CSAdnBg3ShJy96yav9UW5aiiQIgiAyBU1sBEEQRKagiY0gCILIFPqJTfkOoQvmDRO92bZenH3GwRj6VuOnYRr1S/ITg9vGG7fV+KdpuwEYJey9WkYtYspPIoPKOaiO0kpe9JJVOSM8tmbgo70DMPRFbcA+1X4SfiHFSa1YWxKh1DHVAeYHvPBNU0aMA+eOnaVOFVrnYhzLFGRDm8xDXciE4e3nHXuwsfQNe99YhXm3DNpSRFcw5ScRGzlNZWzqppW86CXDcponNnYhq5a8r85PhFH6LKzBgRvmP2ngl+MBqnvq+Cqww06K1MajLByDUrMNasCBtMHjsLkG8MLWgEXYex5BeGlBlKnARrUFl/C8rexAzz0PMWZegqMu2IT3N5WxqZtW8qKXDMsZ+YxtYcNwV85ma29pStyRYt7cKqzOsbziOjSb61BU7sD3Fbc1yksx9elze3kbTuRptx/ev6hnHN82bGvGEW7byY8NHkx0CRacs8CPOm5E2VeXLue2G355ZVl33Bo7yHrutujFV08s//rqWXgZ3evQKs+ySzdua9rjtGFL9K222deWWMjY5rjwwtbYhL3n8ahq+8JuTt1QaHy0e7MECQpT5cNGTlMZm7ppJS96ybKc0S+PMC9oM+RBORehUh1dUIw51YJ5ORM0WzC7x/I7VSjj0pW8c2UXrPaiU77vEpa2T7wRFvXdNvZhYS/QDydqfPpxhNuOGN/EaMDWegnqYkywfgmO+eS1swOsnwOQ2/xCjPXYNNtx6vU6S1CTy7+yni4aN046ymQ0U2zDpjsOU3u7AJtOvjq+/raMaHNMdLeXYV2JZN4X5pnV2Xnq6GQZ2qWyOODAb0rYDV6pTpHNCWLSRE9sjMpOHUC90HPvw7sAFBaWvIBzZYNXwi5Y1hcMRqhPRL2DR49QZIeIHJ9hHLZtTxRcxtqFeTamuf0FOAhONrZ2CNqEexFNWC8q8jZrsI/LCCb7ITjpyMkI19TZ2PjSW2R7gfHtXvK8yih9m9r0nRDD4XqLilvuPv/03SD1x5ugD2AxIAVf5mR6mj03hhUAgiAi6TuxsZ8vbCzVYHmrLfYnQbDPBqwW1Tt4dncvjozOONsWFDBK9qAXZi8a82a7yC7GFsuD1qw4nqCbDmDNW1WwgI1tcwV2+cyGWLbHly5t9a1pc5BZx4A7GYlna7pJbfCw9xhBGDRllOd2CcRGTlOZwXWUHvKilyzLaTGxMeHWNqG0u+vck/KLtLdU2N2vKQ8R48PXJ0d5VoEPLsVmiKHGZ9n20OAFDgJvfLILPPMcVjuBlzBk/9yrcZ4tVXbQQ2rBde88Gt4OWE96WwjvZ4hJs7IIK+iBRbWnyOUfXx99xzXGfjRWoYjLvUFPzSLsPV9qlK5YYwvWmyuwyOZKXz6rK5/bJRKb8P6mMjZ100pe9JJlOdnda5hOtVcuV3sdsYtgmHuQeXhchgRX89w6GFocj6/06sG2NG1z+vTJt2Wfbr+GfnDbV07kqe0r+9q2NeOJ4iWLMOzBfmQ0dV9+uawdV7mKOYq8TsVoOU3bbjuYyj3edJS82mOdXrUsZTC05+axpNTva0teStOmBTZ2kPhC24vk6DlwzCJkvhoZX82X7U0DG10MKqdXxpyfdPKil7zaH+WmjyDHBH1wNhmQHTxIF3ryope82h/ltlqKJAiCIIi0QBMbQRAEkSloYiMIgiAyBU1sBEEQRKaYwTdIxDZBEARBpB56KzIm6A20ZEB28CBd6MmLXvJqf5SbliIJgiCITEETG0EQBJEpaGIjCIIgMoV+YlO+U+iCeWrcMFts68XZZxzE3TeXT3xZXiQZH41/X1DJ9z41GK5DX4qPC/xWp//7kzah7lVbmeznpDF823IM2MhsKmNTN63kRS9ZlTPCY2sGPto7AENPCgP2qfYT90Q0DnyhX3pwsFbgF0X+dXk3vw4wr0xgah0euyzhMiaergi8Og+7IofDzh+bMPnLMkYes1NpfZmXcULUCBsJO5VXFpMfh81SZm0Zm7ppJS96ybCc5omNXVCrmoCfY2WUPgtrcDBgPK3pg19/B6juqeOuwA47WURUFT+FQCQAYghkOKA6rIgcjm2oexk1nNlpcUVfprFVg6VBAhBOCxuZTWVs9ZVG8qKXDMsZ+YxtYcPgIbDZ2lsiE0sumDe3Cqt4N8wDQyph/hn7itsa5bWa+vS5vbwNJ4K02w/vX9Qzjm8btjXjCLft5I8dHiw0IrhnkO51aLkXViJOrELd81BBMq5eAy7tOqFqfLDz7FxrAJtOERuZTWWs9JVS8qKXLMsZ/fII84I2Qx6UM6GU6njX6w/5D80WzO6xfFyKwSU06UGxyae96JTvu5ym7VMJEOm2sQ8Le4F+OFHj048j3HbE+EYBJ2F3ArV8BqPWwQCdPu+OmCzM29tjUxuP7H0OZjuBqOYMjDtX2iQbEcQ0iZ7YGJWdOoB6oedeRiDkvww4VzbcqbLJZ5CVmVCfiOqFoUcoskNEjs8wDtu2RwUnYTmB9sIXRS2+OvFEkSaGBM+TZYA9bos9gOXAy07sVmVrvRT24giCmCh9JzZ8lrCxVIPlrbbYnwTBPhuwit5KR1zg0SMUR0ZnnG33wbe0RUwTm1D36I3B0oLwxpyo6DXFeN3tc9CqbtjdsCQAG5lNZWzqppW86CXLclpMbEy4tU0o7e46ngy/GHtLhf6Q//Hh65NTAldv+OBSbIYYanyWbceOc3H0vwmKr6FP8Dkf4WAR6p7/mGv7bpn9mvpjdl4EWkrDwzWJTXh/UxmbumklL3rJspzMSwmDYf2VUP4ID+cv89Sw/2qeW0eG9V/p1YNtadrm9OmTb8s+3X4N/eC2r5zIU9tX9rVta8YTRd8w7BHtBft3o6wPOAbCLhy+gz/8PTJKmHx2IFHh8REbXYwis1EXCScvesmr/VFu+ghyTNAHZ5MB2cGDdKEnL3rJq/1RbqulSIIgCIJICzSxEQRBEJmCJjaCIAgiU9DERhAEQWSKGXyDRGwTBEEQROqhtyJjgt5ASwZkBw/ShZ686CWv9ke5aSmSIAiCyBQ0sREEQRCZgiY2giAIIlPoJzb+tfvAl8sxb5gI1bb14uwzDsbRN5dRRBHo9z3IacqeIwYNe6/mm+w3St1JYxqrio08prppJS96yaqcER5bM/Bx3gEY+qI8YJ9qP4mfCMJRBFrzljHZiLHQ3Z6Deag79uDh7eedCYedS9qw941VmHfz6wAa+xnbtKg7cUxyqpjK2NRNK3nRS4blNE9s5SpUNQE/x8oofRbW4MAXcDRh8OjXSrw6HK9tTDZiLBTWDqC3Iy2AkRZEFGBD2HseNdgNW1OBjWoLLgXOVVObNnUnjk14f1MZm7ppJS96ybCckc/YFjYM0aTZbO0tqYk7T8ybW4XVOZaHwTox8rPiQe0rbmuU12rq0+f28jacSNluP7x/Uc84vm3Y1owj3LaTHysinM6y5ramb/8+ebzlWvQOZJ25JN0upRIvDI0p7L0ubE10OHyvzcHrjh+b8P6mMjZ100pe9JJlOaNfHmFexWbIg3ImlFIdXdAedNid57y8EjdbMLvH8jFYJ0Z+lh4Um3zai055vgSnmywl2j4xqrao77axDwt7gX44UePTjyPcdsT4hqYAawcdWKoVQxNUdP8oj7qEuQQ1vlzrRGuuizqwvjX9pa0U091eZrcdfSK9V3agzs7NIrffMrRL0SFpfW0OWJcgiOGJntgYlZ06gHqh7e5DTbkAFBaWvIBz6lKbCpt8Ii8YAUJ9IqrXgh6hyA4ROT7DOGzbHhmc3MQEVS95zxOj+kd5mk1YLyrHmxh1uwiz5V2YZ3lz+wu0rGmJ6x0rbjF6vsXaUuAGSY93E3IAixFniq5N27oEQYxG34mN/RxhY6kGy1ttsT8Jgn2GX7yI7353nG1HUFmElWYbOlb9rziemZsOYK3gTZKbbfQC6UUUG9zJRTwH001AdmHvMWowaMPh958ozXUniY2cpjJ2OkonedFLluW0mNiYcGubUNrdde4xxbMiuVTY3a8pDxHjw9cnpwSu3vDBpdgMMdT4LNseAf48TH141rgEu+VZcFapI/pHeZhn5r5owL07Nonxv85yZmWnA9VyCxK6lJ9cGqtQxOXc4ARkCHvvs2FjC9abK7AYdJMNbVrVnTQ24f1NZWzqppW86CXLcrK71zCdaq9crvY6YhfpVMs9kHl4XIYEV/PcOhhaHI+v9OrBtjRtc/r0ybdln26/hn5w21dO5KntK/vatjXjiWLgMOxK6PX+/Us5MZV7VTEotV5ZZuYcGztI/Pbw69EmHL6X7YXSt21zEpH0Bz4n3UF58vA9C12o+UknL3rJq/1RbvoIckzQB2eTAdnBg3ShJy96yav9UW6rpUiCIAiCSAs0sREEQRCZgiY2giAIIlPQxEYQBEFkihl8g0RsEwRBEETqobciY4LeQEsGZAcP0oWevOglr/ZHuWkpkiAIgsgUNLERBEEQmYImNoIgCCJT6Cc25TuELpg3TIRq23px9hkH4+ibyyi+Ls+S+unIkYljvIHxWY1zmjYaEjX+nUk2Uxn+zUeRn4UYeL5YgAZlmMrY1E0redFLVuWM8NiaXliVQRn6Yjdgn2o/ib/Ahr/i35ofwxf5R9UDxrfD8cmE44wzPt207dRYhXk3pH0dQGcDUxk29mUZA4/ll9aX/TdiaYPJ0ze8v6mMTd20khe9ZFhO88TGLnBVTcDPsTJKn4U1ODCGCUkA3evQUuPV4XjHEUMtbj0UjkGJh9fJBjzy79KC0E8FNqotL3KCILKMG5GhAosrCQ77b4NNeH9TGZu6aSUvesmwnJHP2BY2DHfrbLb2lqy8u9m5uVVYnWN5PBjmOhSVO/N9xW2N8lpNffrcXt6GEynb7Yf3L+oZx7cN25pxhNt28mNFhNNZDt7WqONG5D7/K/TJx6XRs5ovUdvDbVcusczrqx+oq4NPyOJirmuP04YtzXj62gwLGdscDzyOVG1f6LsL+7VmKKS9sQy3IQZ5xfwGXNpNQOiZEbAJ728qY1M3reRFL1mWM/rlEXb3vxnyoJyLU6mOLmgPOuxudl7OBM0WzO6xfAyWiUta0nNgF7L2olO+79KWtk8lQKTbxj4s7AX64USNTz+OcNsR4xsaDAzagaUaBgW1vIhLfYbkMOT7QD2oS5/skiyXed36Go8RJx13omGp2IZNrt+o9nYBNp18dTz9bRbR5rio7ECdnV+OjMvQLmnCyhrLMBvusTHyaObnYLZDUcsJIolET2yMyk4dQL3Qd/fZPWsVNsQvurCw5AWcU5faVNiFTJa3IdQnot7Zo0coskNEjs8wDtu2R8aLet2rl/o/T1T0yeXYveR4Q6Z8FdRDswnr/CIs5GoybwOXC0x2QnDSkZMRrp3LIKeR7QX0LcfTT6+mNiOVMjrehHsAiwZra8ugPMsAezx/D2B5/B4mQRCD03diYz9x2FiqwfJWW+xPgmCf4RcvNPfZQzLOtiOoLMLKoM+u3Oc7AUz5sCJedJDpANb0BQ2wiXhzBXbdB0yW7fHx2OpV06Zp0o0djPwLfULae2UwGju4z94KwOZwqI17Fh4jNuH9TWVs6qaVvOgly3JaTGxMuLVNKO3uOvet4lmRXCrEH7v3EDE+fH1ySuDqDR9cis0QQ43Psu0R4K+Jq0uGjUuwixNA8OUMtX8l3yeHKV8F9SC9LYR7TxbP1ILgBIweWFR7xvH00WtcYxwAnx0aW7DeDD8nM5XhP+bAs7ekXrSssAnvbypjUzet5EUvWZaT3SWH6VR75XK11xG7SKda7oHMw+MyJLia59bB0OJ4fKVXD7alaZvTp0++Lft0+zX0g9u+ciJPbV/Z17atGU8UA4dhV0Kv+/ovl51+ef9s280XY4nMD45b6gdTuVd1hFWOB9Ae6/SqZeg5kd8N7bl5LCn1+9qMl9K0OQKD2sGLaI/j0IfDV6PeJzUcvo6Bz0lXHrMuVJnTpAuVvOglr/ZHuekjyDER+wdH5fOc4Kv7pnyCQx/+9SBd6MmLXvJqf5TbaimSIAiCINICeWwxQXfHyYDs4EG60JMXveTV/ig3eWwEQRBEpqCJjSAIgsgUM/gGidgmCIIgiFSDy6/0jI0gCILIFLQUSRAEQWQIgP8HKj48Ym+S9yUAAAAASUVORK5CYII=

    However, I have wrapped both queries into an INNER JOIN to be able to do a subtraction between two values, one from each table. Below is my full query:

    SELECT
         'North Central' AS Region
         , nh.Grouping_Level AS Grouping_Level
         , ( nh.MTDValue - nn.MTDValue ) AS MTDValue
         , ( nh.MTDTarget - nn.MTDTarget ) AS MTDTarget
         , ( nh.DEValue - nn.DEValue ) AS DEValue
         , ( nh.DETarget - nn.DETarget ) AS DETarget
    FROM 
       (
        SELECT 'North Historical' AS Region
             , Grouping_Level
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
        FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
        WHERE Region IN ( 'TOP North Historical' )
        GROUP BY Grouping_Level ) AS nh
        INNER JOIN
      (
        
        SELECT 'North National' AS Region
             , Grouping_Level
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
             , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
        FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
        WHERE Region IN ( 'TOP North National' )
        GROUP BY Grouping_Level ) AS nn  
      ON nh.Grouping_Level = nn.Grouping_Level

    The returned dataset is:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAaYAAABJCAYAAAB2FILgAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAo9SURBVHhe7d3bbxTXHQfwry3RAC3PJVKh7a6JU9vksVrMSy/B2EFEYCPe+hJzkbBboCGUhz4igcCNzK0SLSD1LQVzM8XGqLeXuPsMaxvhdV/S9B+gCQHb655zZmbnsnN2Zu1d7xn2+4l+Xs+ZmTPz+53x/tYglKYlAYY597vf298REVGjMbIxERFR42q6dfsBGxMRERlD/cb04sULbNiwwR5KrjcljyRhzV2sRbhGqUsjr3+1c2+2X4mIiIygGtOb8tdM/Ouy1ceau1iLcI1Sl0Ze/2rnzt+YiIjIKOUbU/4Ctjc1oSkQh8ft/Ukyftifx/YLyNu7EkGuRa3uuRZzO8+O72HJ48J2t/bjhz3r4Q15Tsiz5041jsO+bZs657DYG6KW9YtS01pEnGuyiLzC9lvHJHT9fTnY+yRtngle2xWK/qO8zmHMiv3yGBWzw8idXsYCr8KDoc1DNqUPgDFvHr+dxpZa3U89fwhWmbbmUmcnOnPP3TrkH+LmpP290HPVWY8xHBL/Fdfnao91gO/Zm0XbaecHugefDHci99xf4fzDm8DwJ2JvfdSnFjHOraZlPNvyXrTK5KUE339EXO1J4vovJ0/z19Yh76maKv87ppZ30DE5jVl7M7aWo/j886NosTdrITwP8cn0dA7Ds1f9D2zPVSzV6n5WIVdTlH929mN/x008dJ702Wng0CF02puVacHRP7kfilp27QduPvT8EOUh35f276pf1etVC9PFf3+Jn1fy1t8rWesXR/zc42leXCzgm1evsbCwKF7n/fF6EYXCIl55x6Zn8CTTis3q+0+xrfjrZSeGpp1jvOMitn2KKTkmX0v2H8R9Z0zsHzrgnndg1J4vRmjzmB7FZ4U+vL/JM+bbL697EAe22fdS5t7UvXu3feeGHG+/huYk9xWvI8I7f1iErYUM3zz2Gqix4Hp49wWO180dEXGenfd3teGz+zNq7P7tp9i7qzXkWgsQR+O1dyzsnjal8KPJnFWnTV3YW/gz7hVzdNf5vqfeMlTNnflk/mFr6XwfrI1zXETUtRbFKD03tBYqZ90zL0JbjxkM/eIYJiePYUvU82pHxe8v3rzC9juRwPVfVp7FMG9tnXByryb1G1OhUIDsd7LrBQPZj9Gx9ltY50T7DE79YxCppTwu9d9Cb+4Vvn4pIteH2/0XkVfjH6PtnjM+hIyYXc2lXv37n57PYc/AePFaM7vd86bOyPn896OLcnlY17Xj0RE3l59Y8yObQ+s1ed1f47nu3rxzeLbdc8OPD89JX6NyEbwHq5Yha5AaxKn+f+H2WF4dlx+7hWz/KQykdGsWNnd0SGWfHTln+l1gZExcYxyjU33oTmuuJScLjJUe14LWTA7P8/L7NLr7lnw5oq9HPJdL6Lps5+eruTtfcF5rW18b77G6kOpXC08EztXWQvPMu89iWD3G0H1N7M8M4al6D3Cvowspsi6+MX9eJe8/a49gQh2XwPX3jcXN0xPyIp7teq+tE5LMvZqaRcMsTh5K3qiT/MtpnMtcw+iEGJ8bw51sFic73sL6dSI6TiCbHcH4hBjHEI7vtE4vIc/z7E/37ENmahZzckNcS3tehMg8HDuveBbJltmHnrR4LXdvOs65gjr++gM8tjYtYTkFrrNsujUQN9x1fAjip1bc+xzGbwPnjnfrj1/mnyfEqnn6A/TiGeYmHuBG+xbYpVqmWTzLduAdT729OfY6CzF3CT+T+Tk5WqPllallHPWuhZauFppnvqhKz0rsn8uiQF6+9x8ZV9Bl70rc+vvEz1OrzmvrqDz3aM1yOjWn+CIn94c6xrOdwsCpftwYfWTv68fdr7/BV8X4JwbsQpfOEXzV7Q+OO9sRoY61vvjGUz3izUAWPTAeel3v97pjIsYz74pPG8648xrjPN92WIQdI0dC1kDcQDFv9UFhH7rlmO74kjWLGeoc60vpfjWleE2huzeHs2dz+Gj3Ts948FjvtjMWOC4/i2lVX3s7NMdHGOwYQe9TO7+n1gcQcbg9n/Nqz+Eb19SyeGyZcOYIjquQc8tdNayFCnWUZzuqFs453rmdbfm1Cs+KOtb6UrpfTeUf8+UVst8biVt/z1gleapQR3i2DVhbJ9Q51rnVUvm/Y+r6EB/dGMXjtHgoMtfxwPn1YO4yfr5+wBrHCQz7fm3wCOyfGx9Btm2lnx7LSYtm2o6TW8W92SPABH65NeSTlO7e0lvQlp0Rn8tsczPuuZ7x2LlE1Sgu3RqojbR4EwRO7jkhP0pa91T2+NpRn2yz7dgd+RGwnDlcOXgCbacGPfW1crxzdsTNUWnHFmfDu1aSbi1XqTa1q4VOmVpIumexLs9KJXlJyVt/S6V56iRpbSvTLNtduT8bVXt8Y2m0yqQmUjjyt3vAnrX49noRW0ew98ll7BCfCo/84TxyxXHZAKw5rLn8+9+704cnl7rU3MFrlV5bH/JQbR47LuN/d4G98n5UnEHr3fP4sT2/ex3dvXXh2Lkp9/yzU+65mSmcscfDcgnPSV+j4j2HRCF7Au859yBjcFazBtbxqe4+cZ8Z7O1O2XPo1qz0PuOEPCXWs5MawF+/cu8r7FqSd1uGP992PPvNS1zc4T9G5lgQP5FujoG12nNdzqyuZ11Xt5b62nivpws5Rb1rIcN/blQt5DG6Z1FTj1SLeGOX9zJQ+ncgISGnKluXiLxKnnkRP71s/b2SjMSs/wrzlCG52/VfWyec3KupaWFhcek/X/4XGzd+F/PzC/ZwFT0exIa/fIgXF1f0ETHSurVvoaZ5hPm3+KRxAPjj3weRsoeWZZVqVG11qbmh3phaVPlZbJRnJBF51uh9xsn9+5u/Z4+sXLNsd07XC3bC5cUEfvWd9djgRO80zh7dEXJcdaP6ecQJq4iFkvGo0NUoMF6MwYo+waxW1KfmZkZya1Hbn9fk1qWyMDPP1XkvLuZeRU3zCwtLX3zxJd5+eyNev563h5NnvfiV9E3II0lYcxdrEa5R6tLI6+/k/sMfbLZHVq5pamqquq2OiIgaTltbm/3dyjXNz8+L38KWIP8FcNK9KXkkCWvuYi3CNUpdGnn9Ze5r1qyxt1ZO/R9s7e+JiIjqrvJ/x0RERFRDbExERGQUNiYiIjIKGxMRERmFjYmIiIzCxkREREZhYyIiIqOwMRERkVHYmIiIyChsTEREZBQ2JiIiMgobExERGYWNiYiIjMLGRERERmFjIiIio7AxERGRUdiYiIjIKGxMRERkFDYmIiIyChsTEREZhY2JiIiMwsZERERGYWMiIiKjsDEREZFR2JiIiMgobExERGQUNiYiIjIKGxMRERmFjYmIiIzCxkREREZhYyIiIqOwMRERkVHYmIiIyChsTEREZBQ2JiIiMgobExERGYWNiYiIjMLGRERERmFjIiIio7AxERGRUdiYiIjIKGxMRERkFDYmIiIyChsTEREZhY2JiIiMwsZERERGYWMiIiKjsDEREZFR2JiIiMgobExERGQUNiYiIjII8H8U9izn/Y+EdQAAAABJRU5ErkJggg==

    How do I make it so the subtraction is performed when the INNER JOIN is satisfied, otherwise do the following:
    1) The Historical value is returned if this side of the join has a value
    2) The National value is returned if this side of the join has a value BUT this value needs to be a negative number as the calculation would be, in affect, 0/empty - the National value (a double negative must remain a negative).
    3) If the value doesn't exist in either table a 0 (zero) is returned.

    Please help (amend my query to meet the requirements)?
    Thanks in advance.

  • I don't really know, but here's a guess:
    SELECT
    'North Central' AS Region
    , nh.Grouping_Level AS Grouping_Level
    , (COALESCE(nh.MTDValue,0) - COALESCE(nn.MTDValue,0) ) AS MTDValue
    , (COALESCE(nh.MTDTarget,0) - COALESCE(nn.MTDTarget,0) ) AS MTDTarget
    , (COALESCE(nh.DEValue,0) - COALESCE(nn.DEValue,0) ) AS DEValue
    , (COALESCE(nh.DETarget,0) - COALESCE(nn.DETarget,0) ) AS DETarget
    FROM
    (
    SELECT 'North Historical' AS Region
    , Grouping_Level
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
    FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
    WHERE Region IN ( '########' )
    GROUP BY Grouping_Level ) AS nh
    FULL OUTER JOIN
    (
    SELECT 'North National' AS Region
    , Grouping_Level
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
    , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
    FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
    WHERE Region IN ( '########' )
    GROUP BY Grouping_Level ) AS nn
    ON nh.Grouping_Level = nn.Grouping_Level

    If that's not what you're looking for, please provide table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and expected results.

    John

  • Hi John, having tried using COALESCE I find it doesn't achieve what I require. The full query continues to return nothing due to the join not being satisifed and no 0 (zero) is being substituted. Thanks.

  • Have you tried FULL JOIN instead of INNER?

  • Hi Jonathan,
    Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAa8AAACNCAYAAAAAePWIAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACOqSURBVHhe7Z1tjBxHmcef5cQRcpcLEh8uSAf4ZcaBzcCXJMi0v1y4xPHsHtHGQXvSSWhFEmaP2LldQjbnkyws0CIsNopmSQI3e5DLfolgcYLjZGfjiIPLB8+tIP5CxmviGZ+JOE58QUoIr3nxXD3V1d3VPV0z3b07L739/0F5p6urqrue6pmnn+5K/Ue+9+QzLQIAAABSxEhLoD6DiLz++ut01VVXqS3QD2BzD9ginKzYJcvjr/f9HfJfAAAAIEXAeSUAwWr/gc09YItwsmKXLI+/3nc4LwAAAKmj3Xk1F2nfyAiNBNL0mtoPOrM27bfdvkVqql2pgMe/V+fci7ad69V3gTZpcZ9n+7VpbTz0xHVCrnevqTWa9m0rZJ1psTeEXtqvGz21RZe6w0yXfoXtt8ukdPx9fVD7GGM/0zm24ZGXVaaGCM84RJOpUab6fIIBGeRA9hBj2M6Oa4yoqtvu6Able2WDbWrfMIw2ZyyLrPoFzw7NVVqpqc+CYsUZjyqVxP/c8akU7QK+671Bo/POl75Ic2WL6hf8Fm6urhCV58TewTAYW0Sou5UkuLb5XIx06Jck+JsnUqWYxvFP0s/hH1sHPieHaI8Nc3uoUNughtqMTG6GzpyZoZza3C7oBvQQd7jzdSo3Kv6LulihVq9ssE3tG0a4zR0mabKwQqvOt6GxQVQqkaU245GjmWXvZi03Pkm0sqp90ZrEv12T44Oz+qBsMex0totO9H6lb/x10jV+UdD7/o4//ulN8qU33qbLl9+mP+l5G+fpp3uvpQ/Izw/Rx92w0qIHN5wyer5IH3+IznEe/23b/1l62skT+x+826t39ynV3pCmt9++LP6+QW+99bZ/38Yp+s7lO+jm92t5vv3c18/S3R9X/e9gD2kvfdtXN6S8+htqR97nHkckvf2wFDb+nHztqHGXecFrQN8XKG9qu0sy2pyTavPm8VH6ztPnZd7TT75Et49fG3Kst0iUpjf0vLBzev8u+nCtbtvp/fvp9svfpZNuH71xflqzNydpc6c97n/YWDqfg7ZxynVJA7WFm9rrhtpC9tl0zYtktMd5evDTs1SrzVK+2/WqUhS7GPsVtt9JKRz/RP100/CNrZOcvju8gz1ZMNH6F6hwxZ/Tu5103Xk68qPDtKvVpIfv+h4drP+Jfv8Hkep30JN3fZ2aMv8LNHrSyX+Q9pJqS/71739poU4Th9bcY53/pFfv3Fe5Pf/5DFNiLl++LHpl3wX492nbz93j2e9v7T7Rep2u/Rb39T66YLKH3oa27dUNLx9uR/O4dErBc7DHL2Tcdx2mI3f9Nz1Zbcpyzer3aP2uI3Rol+k6CWu7e2JMNrf3i7+7P0R0oiqOsUanzt1BB3YbjsWNBfLay+Xo2r11utDkz7vpwB0tXx/pjqL4LrRo/yOqfz6be+0F27W3zbbRy5oSMzhbaClQ12gLwzXvXYth9qjSgW+J/XsfpJfk7453HFNiutrFl+fvV9tv3hX30GlZLoXj78uL2k8t8UG07UGPrZMY7rtD+GNDbtg52T9s0Nf2fotOnRb5F6v01Po6PVB4F135bpEK99P6+glaOy3y6UH6/K129Ta4nrZ/d/FTtPdcgy7yhjiWsd4QIm4gXEN25NZHtYFU7P0UFXeLv53sYcKpK5Dlv/0MPW9v2oTZMXCcxJjGXZzw/s8/SOKbLc79Iq09SfS1zx8wl0/47CKSzXeP0UH6GV08/Qw9dl2elKkS0qCfrRdoj2ZvvY8HnYG4+DB9gvvn9NHO7UwHW0Zh0LYwYrKF4Zp32aJrJfL30iXQL99vHqdHab/albrx9xG9n0YGPLYOwb6HRF72Dm97Fx06chc9duo5te8u+v7v/0i/c9N/0SFlmPY2gn9N+4P5zvYQJnl+9j++/F1F8YPBAxPID+2r/tlUpkv+3g+JuxYn3/kboZ5vOyyFleGckHEXJ+D2W97AfIoOcJ6pfNt1EjHJOvY/7ftlk+LvLjpwsE7Hj9fpzk/equUHy+rbTl6gXLNBG9K+aju0j8/R4cIJOviS6t9L9k2KKK7ac/6qNnz5Blu6ZTskp41gvkzcNu/qoS1kkqW07W62cOrobTvb/O8WXCuyrP1P+37ZlD/P16+Q/XpK3fhreXH6KZMsoW0Pwdg6Sdax6zLRJmzsv43ufOwUPb9bDOLeb9Mzzi3/xUfo7648ZOfT/VT2hQIagf0X107Q+uhm7wiHjd3CyV9HD3xE2EPlEJ2mez8SckdmssfuPI2unxf3d4qL5726Wn5k+3Ubl6iYxl1u7BY/lEQPTNzPt6T2OXUs3zvkHfL6dfTJrreSnbhIj372fho9clizr93Hp46f8PoouY7yzoY+VoxpLPtkm97ZwkQHWzCma3Eg10qcfjHpG3+buP00MZxjG/rOSz5R9eXtpmv5JE7vonv+8yTRxBX0F1eK9JETdPtPH6FbxJ3ePUsLVHfz+QfbbsNuy7//o0/dQT99eL9sO3is9mMPV+LTMz5zvuUR+u33iW5nG8j0Vbr2+wv0MdUnr28me+yn2a+d8+ofP+fV3XuOvqryw+wXbkfzuLjnHJIur99PH3XOgdPhhmHc7fK7DtwhznMv3X5gl2rDdJ20n2eUxFU6Ped329x1iH7wO++8wo7F6Nuc/P29jn72L3+gr9/iL8N9vCy+tV4fA2M18W1uWR7PPq5pLM220Y9nStzEoG3ByV+3my24jOlaNNhjV078+PO5HGp/JxOSuKmOdunSr7ZrXqSbHrHfc3FKzfhvsp+cGG978GPrJKfvDiOv//b3ImuLef4wXfXsbfT61zd12zeUvPuKd9H//vL/6Jpr/prefPMtldtj/kfcsdxN9O8/PEy7VFYiUjouA7H5kLJtbLHF12JWrpFU9LNHvzNO3z/4gb+R26GRV/x0mv75L6+kq5x0cIOOz9wSUi79id2//Mv/D+zrXZJjRZfb8rsl07gE8t10ONadUL/SYGw+nCm9tujtb0R67RIvDWc/+/P77/ZdMfLab37rbYGuXCnC31/84pf0vvddQ2+88abKBb0ENveALcLJil2yPP5O33fu+IDcHjl37hycFwAAgFQwOjoq/468+eabcF4x4dCV/4tx0D9gcw/YIpys2CXL4899f+c73yk/j7z66qut97znPST+Ev7G+wsAAKB/XH311eqTcF6/+vVvEHnF5Io/u+wzIug9r732GmyugC3CyYpdsjr+wX5H+4+UgQ+OvAAAAAwOOK8E4JEhAAAMFjivBCDyAgCAwWJ2Xpe+QX//3ptp6ZLaZjjvwDfo52ozMjHq/fzfbqZr3vtXbpr7gdrRS2L2K0rk1VzcJ2cEOSmSmnbTL9PdsQ6rNo/so0XTCs3cVkZUlv2wdLsn0R4cBzvpEu7+8oxeZ59mYJ9UumFwTGWi1N16YItwemcXncH3f3v3s0vk9WP64ucSOCsmgaNjx7X35EFa//Vv6FcynSD6hwQOLKmTjUi3yIsHPL8yqcltV4nGujkwceHkV2iyoeo0ylQf819IOmsnl6hUKtDKahf3lBkn1qTFffxFGaMllcPkZs6oMfDsapUmqGgoz/aami0oGfQqFWan7BsEkT9fdyTUG1Suz7ffOJjKRKm7pcAW4fTYLjoD7X82+tnZed14nL48eoQe7Uf0Qxfp+ZNEX/7mPbRD5RDtpwXhxBZuVptDQufIy5YJLy/r8vxFqohBrBTVZhjNC1S3JslVF2eJ/1ZF1AyjSRfqJZqoTFDBJ1EeArdzRj+X7UqOZs7YX7KSygljbUHcIMyxVTuUt0YpLz8UaaJUo42G+NjYoFphj7JjjvYUVL6OqUyUulsKbBFOj+2iM9D+Z6OfXd957b/3OJ1/MCSK4ejGfbw3Qz908g7M0NwBkXfDEXrxJ0dorxYBPX9fh8eBl07TKTpI+3eq7SC+46nHmSrCWvK1e5GWPqcdWz8ncZ5zWlm7vN18HDpGXs1VEpeE54SCiDsS79GgFlnlxkWtWZqKcnvCxyjwHVOeRsXR3OBLbzs/SzUnL5OPD0OQd4MdxoaR4+DYdI1OLombBPH9bl6oi++x/TVm8qMW1S/4rWoqE6Vu34EtwtmEXXSGvv/boJ/dJ2zsvIfua4u+bAfx4e/aj/fWv3KO/vE+pdLyk3OU/6bIf/E43SAit/XnVCQlnEljTD0OFPtCHaIRPt5TdNuLTv2DdMp5nNnW7mna/83gsdU5/XqRFh5SZROdh03y2YYiPJ+apUKV73Ja1CjXacx9lsh3Pw2aXMkrx2Z+n9UUoV1BXkU5Gp8k9ejQ33ar2umeK5tIux3tFoWKcVgWX9k8j8E8jTZM0W+6gS3CyYpdtkM/uzsvwSceOkGk/8jLKOk4HVKP83bcepBu2Gja+280RE/CmTjlY8PH+8mP6Ys3qIhJRnVP0fOs9BalXf2c9AiO21HZcUg821BGZWWSkbogJzyPVb+gRUXswBznU6DZqbCIiR9L1mhpjC+oEcrP1qjGjw4DbYtbHbLUR8Cs0cJsoe3OsQ2OVKeIluWz+mWiqQ6TYlILbBFOVuyyPfoZyXnxu6dDE0/R4YdfVts9YOetdBsJh6TPbvTxGXrCiZhk+gGVYsuDPk9zN+gRnIjQ1J44dIy8fKH2JihOUKm2QW2PieUjw6rt4GRqCJcljrflz823F83FeaqX57reOfIdKU2OqztSL7LN7SlQTXto39ioUWGP/77VVCZK3X4CW4SzWbvoDHP/t0s/IzovEV390wP04eX/sCMV6Wi8R4k/P/0UvTia0yZaJGE37Z+gwOxG4Wz4vdRFcbwb/4PWnEeXMnpS79licx3lnCjs4ss9iLzsQfZHTTwFdYSmG+zYZmlBPSnki8N5qSmnpOrTEddO0pL7stRDXlDas2T3orqwp71t+yOQ0Sp/D7t/eeSXzp0EY0e58kvHkawbJfOEGcs/DIypTJS6fQO2CGcL7KIztP3fRv0UEUgrNL14vHXDjcdb61re+lc+1iInj/cTS6KJpOe5dU60Pi33f6b1RLCtkLZ9x3DaFenT33X2Oe1x+ljryy+KPGO75mMH25fn3uF8wpKIvETQ05lG2fIdp1R1d7QsJ98qtxoqm6mWvPJEpZZTxaPaKpHVKuuVGNmmKO9r22pZ3D7nBY6TRqLY3IZtFLBdtdQidwCCtJf3jYNWLzzfXz9e3WTAFuEM3i796X9W+hkk2G8szJsAfl8mbKe2hhznuXXKp8tjMVoP2CIcLMy7vcHCvFuAuANQn4YcXoWDp8y7/70FAABsD+C8EpCatQ2LFXtSR8f/OhoAANIHnFcCUhN5AQDANgXOKwGpibwAAGCbMiISz+KQP8j4G/0vAACA/qJP2BjhKYfqM4iIPevlNbUF+sFrr12dyRlWYWC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM6LV2YIynJwXhJtqBj1glLVPVCPbidpv0w0HxO2Oxiwncjb95g6xo9oeuRfyd81Lc9XVsOUDyS67LhvmciOcuTt0udhcBvtMujR6g6CKBLspjJR6qaVrNglC/3sEnnVDLIcEUjgEJLJ54ew1c4oEWeF7eBo+sbaNI25suN83SinIq6FcDlyg/R5GNyGXx89et1BYOyzhqlMlLppJSt2yUg/Ozsvq0zlgrdSeW+xVzuOLZ8/rFjHhO2+1CfbAbmaiLt+Y55GrTpJ4VajHLmjndZZKp2vy8WpFSqUdHW0qHUHRBQJ9gHKtw+MrNglI/3s+s5rfK5M9fmQKEZ46DY5e87bN03TfFfKa+rVZimvRUCrWjjaFk3Fks9XjzPl8RZp0deurSjsHls/J3Ge03pILMvbzfeC8bljwnaIvvoOX0u1ArF6w6blyNcWaLZwlOZG1XYKiNLnQcq3D4qs2CUr/ew+YSM3Q0fboi+/5LxPzr5Wp9Flkd8okyUit4ZzNyycycaEXZ73hTpEI/bd72TDqT9JK87jzLZ2V2l8OXhsdU6tClUqqmyi84hJ7k5hO0Rf/US+MxU3ToXqVkiWr9H0GFEVa0MCMHR0d16CYqVKpP/Id5KztwzRk3AmrkR9XOSddI1m8ypiklGdUg+O0q5+TnoEx+2o7F5RrDwubIfoq1/kZs6IG5MGjc5vPqqOqjgLAOg/kZwXv3uam1yhqYUNtd0Dusrnl6jqREwynaEZL7qNiLiTzusRnIjQ1J7ecZOw3bPCdvqD4500ar1sv5NxaF6iupVvU04GSWB1aftxR3I5cls5tjablzc6+Vn7c/uMw+EjSp9NZZLba/jJil2y0s+Izkt0duYoFZaW7EhFOppwOfvkdJHPt5bopHMnLaOnpFOU7XchEn45qT72ktzMvcJ2T2jH2kF7Cmf9L0IbDWHDnZu0YXaRjwvdUEuTLE8sR+5MyrBTo2yJIL9BZ2ZSMEJR+jxI+fZBkRW7ZKSfkZ2XjL7EF9hGfLGXy1Qfsx+/yentwfcCuT1U4EkTMZwMP/JpiAgv7zzWG+EXDjzbkH9I7Gnz9uM+jp46vNMwHpv7UBfNqHbG+jXRWURf5evVZ5ti5QUanf+gOA+V5vPChjepvYLal8T5a/unf9Q5P+PwtVOlMWETHts8rUw27Fmq6p2tfU3l5eSLzv5neP/brcgY+6z1zVQmtr1SRFbskpF+YmHeBGBh3v6DhXk9sDBvOFiYd3uDhXkBAACkHjgvAAAAqQPOCwAAQOqA8wIAAJA6Rl599VVM2AAAADD06BM2MNswAZht2H8w29ADsw3DwWzD7Q1mGwIAAEg9cF4AAABSB5wXAACA1GF2XnL9QKWb5aD0s/SsSMSoJ9eok0uT2KmXelsuSftlguX6Rw4GbKdL+GuS/y5anknu35QPJJuRNdfL6EVM+cNO3D7HtVdayYpdstDPLpFXLbBQbgwSOAR2XHKdRLUYqi3nnuBHY6udUSLOCtvB0fQLvnbGqKquG5YvH7OvG3EtdJU1X5umMbcMX3Nq/TdT/rATpc+mMlHqppWs2CUj/ezsvKwylQtBIcpewSuBE5WXHSl3pkgVYcRUagFax4TtIETZL6SOl3uheJIoUjmgi6y5VI+dHFdl7MWbpYJBsUItR9CU8jRq1f0yNsNKRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnVHSnnOZL7UjBy1pbilxWIVrVwtC2akgKXBiFLxnc89ThTHm+RFn3t2irP7rH1cxLnOa2HxLK83XwvGJ87JmyH6Kv/eJIoUWTNpYbRyqoaJ7tum/S5FETV5HSGmKzIwMclK3bJSj+7T9hQS+T7IwjbQRSqHFqy1lGdxhwv4Ejus9CjT4o/KNcf57EeH08XkZykFedxZlu7qzS+HDy2OqdWhSoVVTbRecQkd6ewHaKvftNcnKJZTem7KyLCqroyEFO0UfBLlMr3sOJmrFDtIMMDAOgr3Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu34S8663RbF5FTDKqW6FVDmejtKufkx7BcTsqu1cUK48L2yH66hfue1P3cV80iu5NzRmaCFwV8pFkq0Gj8+matAHAdiaS8xJfbZqbXKGphQ213QOkOrNwSMZf+RJVnYhJ/cjMeNFtRNZoWgpZqjY4OlR7esdNwnbPCtvpD4530qj1sv/9SfMS1a08xe4ScAlzXPFlzVk9lkLKaO/RhpysyMDHJSt2yUo/Izov0dmZo1RYWrLvSaWj8R4lNldXtJd8SeEfBwrMbmTlT3G32xDHs5bsl+iMjJ6SzvzS3lvwy0n1sZfkZu4VtntCO9YO2lM4638R2mgIG+7cpA0zzNo05WcLVA1GXBFkzeVjQSekWlug2VqJJkQ078sXdZ33aENPRmTgY5MVu2SlnyICCadRbllWudVQm0yjbLXIyeP9ojo34ctz61RbJbm/1KoG2wpp20Eew2lXpJIIt2yc9jhZrTJXNrZrPnawfXnuHc4nDF7MuNV6xZwax0R7x0R7Xl6jfL04lp73Qqts6eeh7eP6+jlyKj1uznfb3L7JtrmZailgF5EseZEE9rkXFF8j4vpwtrQy3jXnz3faGzTdbMHE7bNXxpw/7GTFLlkd/2C/sTBvArAwb//BwrweWJg3HCzMu73BwrwAAABSD5wXAACA1AHnBQAAIHXAeQEAAEgdIzyDQ30GAAAAhhZ9wgZmGyYAsw37D2YbemC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM5Lrh+odLMclH6WnhWJGPXkenLOqu+8rmGyBQzjkbRfJliuf+RgwHa6hL8m+e+i5Znk/k35wACvjelfAzOKxLl+De5Tgxi8Lu00vMrKWZCBT0JW7JKFfnaJvGqBhXJjkMAh8A+EXBXcXTmepdcTOLCtdkaJOCtsB0czGJq0KMVHx2hJ5UjEdRFFHn2KF/hV119hdkqWsWVRnOtSJFYkKE0Mp75XxH6mXQY+NlmxS0b62dl5WWUqtwlR9gpetZuovKyvCl6kijCiq+6eJqxjwnYQohwMOZo5w1++KpVUjiSqxLk1qqRpijRRCi+ztrBCk4kF6npMRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnV4xPOcyT3pWCkkuKXFYhWtXC0LZqSApcGIUvGdzz1OFMeb5EWfe3aKs/usfVzEuc5rYfEsrzdfC8YnzsmbIfoa1iIJHHu05Vbo5NLtjyKD3FNzdc7XKsDJisy8HHJil2y0s/uEzZyM3S0LfqyHUShyne3LWqU6zTmeAFHcp8fq/ik+INy/XEe6/HxdBFJ8fPiPM5sa3eVxpeDx1bn1KpQxVXMdcrHOY+Y5O4UtkP0lS5E1LYsri+p2j1Po4126X/WryscDeiGAQD6SnfnJShWqkT6j7yMkjz5/dz4pCdgpkvu60SR6zfBx6vVaFb+oIgkozpxd8zhbJR29XPSIzhuR2X3imLlcWE7RF+9xn3JvNlQmq+PKaJleYOzTDQVmLQkorGF2UJ7NAYA6CuRnBc/+5+bXKGphQ213QN8j2vCKKmX6E46QzNedBuRNZrO6xGciNDUnt5xk7Dds8J2+oPjnTRqvUy+aLx5iepWXr1rAXEpOhF1hxekUSTOOaqiyXEVVdnq3ivaRdlcnKd6eW44J2oosiIDH5es2CUr/YzovERnZ45SYWnJjlSko/EeJfIX3nvJlxT7h8I/u5GnOo/QdEMcz1qik85NtYyekk5TLpA7FvxyUn3sJbmZe4XtntCOtYP2FM76X4Q2GsKGO/EoqpdEkDiXX96VVbeMX/rfnlQ0OawvuxyyIgMfl6zYJSv9FHer4YRI40sJfSeP9ztS0XqeW8csxR/WtkNQpt9ToXba42S1pCK7sV3zsYPty3PvcD5h2HLU4XL1MrFcv0/y/xVx3Ov9Uv+tF1plSz8PbZ9J7t+U77a5fVMU6fN2/LLnzGbk0cWOoZBFz6oMfDeyYpesjn+w31iYNwFYmLf/YGFeDyzMGw4W5t3eYGFeAAAAqQfOCwAAQOqA8wIAAJA64LwAAACkjhGewaE+AwAAAEOLPmEDsw0TgNmG/QezDT0w2zAczDbc3mC2IQAAgNQD5wUAACB1wHkBAABIHWbnJdcPDKyozXlJFIpj1AvKrfdSb8slab9MsFz/yMGA7XQJf03y30XLM8n9m/KBJK6suZ5vus42U3eQmM5bJ0rfTHXTSlbskoV+dom8aoGFcmOQwCGw48qvTCoJak5VorEEPw5b7YwScVbYDo6mX/C1M0ZVdd2wfPmYfd2IayFU1nxtmsbcfL7O2hd6NrYZoe5AMfVZx1QmSt20khW7ZKSfnZ2XVaZymxBlr7BX7C4v6yJ/RaoII3ZQuRherGPCdhCi7Be5mTOaHAorFCgFWIOsuVSMdaVPijRXrnuqBQpTm1HqDpSMyMDHJit2yUg/u77zGp8zqA0LD+3J8qs7T85zJPelYKSS4pcViFa1cLQtmpIClx2k1X3HU48z5fEWadHXrq3y7B5bPydxntN6SCzL2833gvG5Y8J2iL76jydlYpI1D5M+6Sx37rUZv25/iSLlbioTpW5ayYpdstLP7hM2cjN0tC36sh1EocqhZYsa4s5zzPECjuQ+Cz36pPiDcv1xHuvx8XQRyUlacR5ntrW7SuPLwWOrc2pVqOKIFiY6j5jk7hS2Q/TVb5qLUzSrKX2HUqxQVVzXeXkTM0Ubhc6ypL42Y9YFAGw93Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu3wQfr1aj2byKmGRUt0KrHM5GaVc/Jz2C43ZUdq8oVh4XtkP01S/c96bOjUsHXPXl1hma6HAlhLUZtS4AoDdEcl7iq0pzkys0tbChtnuAVGcWDsn4K1+iqhMxqR+NGS+6jcgaTef1CE5EaGpP77hJ2O5ZYTv9wfFOGrVeJl803rxEdStPsbsEXMKcTDRZc1aMpVC58+7O0Fx3UETps6lMNHulk6zYJSv9jOi8RGdnjlJhacm+x5SOxnuU2Fxd0V7yJYVfiFNgdqNwNiJCmm6I41lL3ktxGT0lneFVIHcs+OWk+thLcjP3Cts9oR1rB+0pnPW/CG00hA13btKGGWZtmvKzBaoGnYxB1pydkjsNeG2BZmslmghG8IY2I9UdJFmRgY9LVuySlX6KCCScEGl8KaHv5PF+Rypaz3PrsOQ07y+1SfGHte0QlOn3VKid9jhZrTJXNrZrPnawfXnuHc4nDFuOOlyuXiaW6/dJ/r8ijnu9X+q/9UKrbOnnoe0zyf2b8t02t2/qJn3uky5XyZIXSWCfQe7cf53ZUulR2+y3UnpWZeC7kRW7ZHX8g/3GwrwJwMK8/QcL83pgYd5wsDDv9gYL8wIAAEg9cF4AAABSB5wXAACA1AHnBQAAIHWM8AwO9RkAAAAYWvQJG5htmADMNuw/mG3ogdmG4WC24fYGsw0BAACkHjgvAAAAqQPOCwAAQOowOy+5fqDSzXJQ+ll6ViRi1JPrxjmrvvO6hskWMIxH0n6ZYLn+kYMB2+kS/prkv4uWZ5L7N+WDNnQpc9M1ZCqjX4P7hlVGtgtZkIFPQlbskoV+dom8aoGFcmOQwCG4K3i7K8ezxHoCB7bVzigRZ4Xt4GgGQhSZflMZce1M8WK8Kr8wOzW0MuhGRB+yIAMfm6zYJSP97Oy8rDKV24QoewUr0hKVl/UVvItUEUZ0ldjThHVM2A5ClINAqsF2kenvWMYaVdI0RZooDbHcu4mMyMDHJit2yUg/u77zGp8zqA0LD+3J8nt3ra7kvhSMVFL8sgLRqhaOtkVTUuDSIGTJ+I6nHmfK4y3Soq9dW+XZPbZ+TuI8p/WQWJa3m+8F43PHhO0QffUbqUnURabfWManK7dGJ5eGTO4kAlmRgY9LVuySlX52n7CRm6GjbdGX7SAKVQ4tW9QQd61jjhdwJPdZ6NEnxR+U64/zWI+Pp4tIip8X53FmW7urNL4cPLY6p1aFKq4CrlM+znnEJHensB2ir74TRabfWCZHM8vi+pKq3fM02qiI+AsAMGx0d16CYqVKpP/IyyjJk9/PjU96Ama65L5OFLl+E3y8Wo1m5Q+KSDKqE3fHHM5GaVc/Jz2C43ZUdq8oVh4XtkP01Wvcl8zqJiqKTH9oGb4+poiWZf4y0VRg0hIAYCiI5LzE15zmJldoamFDbfcA3+OaMErqJbr3gzPjRbcRWaPpvB7BiQhN7ekdNwnbPStspz843kmj1svki8abl6hu5dW7FhAX1xG1vSBlNdhuMv1eGVYFJ/ddmK3uvWK+KIeSrMjAxyUrdslKPyM6L9HZmaNUWFqy70+lo/EeJfIX3nvJlxT7h8I/u1E4G3E3Pd0Qx7OWvBfqMnoKmUEWiQK5Y8EvJ9XHXpKbuVfY7gntWDtoT+Gs/0VooyFsuHOTNgRMFJl+Uxn55Q28CxvWL6+RrMjAxyUrdslKP8Xdajgh0vhSQt/J4/2OVLSe59YxS/GHte0QlOn3VKid9jhZLanGbmzXfOxg+/LcO5xPGLYcdbhcvUws1++T/H9FHPd6v9R/64VW2dLPQ9tnkvs35bttbt8URfpcR5cy919D4TLoutr5sMqgO2RVBr4bWbFLVsc/2G8szJsALMzbf7AwrwcW5g0HC/Nub7AwLwAAgNQD5wUAACB1wHkBAABIHXBeAAAAUscIz+BQnwEAAIChJDhJBbMNAQAApA48NgQAAJA64LwAAACkDjgvAAAAqQPOCwAAQOqA8wIAAJA64LwAAACkDKL/BxbIGfAinJIEAAAAAElFTkSuQmCC

    So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

  • You're joining on grouping level, but the grouping levels don't match. Is there something else that should match? Or are you trying to ensure the single row from the national level is applied to every other one?

  • chocthree - Friday, March 15, 2019 10:18 AM

    Hi Jonathan,
    Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAa8AAACNCAYAAAAAePWIAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACOqSURBVHhe7Z1tjBxHmcef5cQRcpcLEh8uSAf4ZcaBzcCXJMi0v1y4xPHsHtHGQXvSSWhFEmaP2LldQjbnkyws0CIsNopmSQI3e5DLfolgcYLjZGfjiIPLB8+tIP5CxmviGZ+JOE58QUoIr3nxXD3V1d3VPV0z3b07L739/0F5p6urqrue6pmnn+5K/Ue+9+QzLQIAAABSxEhLoD6DiLz++ut01VVXqS3QD2BzD9ginKzYJcvjr/f9HfJfAAAAIEXAeSUAwWr/gc09YItwsmKXLI+/3nc4LwAAAKmj3Xk1F2nfyAiNBNL0mtoPOrM27bfdvkVqql2pgMe/V+fci7ad69V3gTZpcZ9n+7VpbTz0xHVCrnevqTWa9m0rZJ1psTeEXtqvGz21RZe6w0yXfoXtt8ukdPx9fVD7GGM/0zm24ZGXVaaGCM84RJOpUab6fIIBGeRA9hBj2M6Oa4yoqtvu6Able2WDbWrfMIw2ZyyLrPoFzw7NVVqpqc+CYsUZjyqVxP/c8akU7QK+671Bo/POl75Ic2WL6hf8Fm6urhCV58TewTAYW0Sou5UkuLb5XIx06Jck+JsnUqWYxvFP0s/hH1sHPieHaI8Nc3uoUNughtqMTG6GzpyZoZza3C7oBvQQd7jzdSo3Kv6LulihVq9ssE3tG0a4zR0mabKwQqvOt6GxQVQqkaU245GjmWXvZi03Pkm0sqp90ZrEv12T44Oz+qBsMex0totO9H6lb/x10jV+UdD7/o4//ulN8qU33qbLl9+mP+l5G+fpp3uvpQ/Izw/Rx92w0qIHN5wyer5IH3+IznEe/23b/1l62skT+x+826t39ynV3pCmt9++LP6+QW+99bZ/38Yp+s7lO+jm92t5vv3c18/S3R9X/e9gD2kvfdtXN6S8+htqR97nHkckvf2wFDb+nHztqHGXecFrQN8XKG9qu0sy2pyTavPm8VH6ztPnZd7TT75Et49fG3Kst0iUpjf0vLBzev8u+nCtbtvp/fvp9svfpZNuH71xflqzNydpc6c97n/YWDqfg7ZxynVJA7WFm9rrhtpC9tl0zYtktMd5evDTs1SrzVK+2/WqUhS7GPsVtt9JKRz/RP100/CNrZOcvju8gz1ZMNH6F6hwxZ/Tu5103Xk68qPDtKvVpIfv+h4drP+Jfv8Hkep30JN3fZ2aMv8LNHrSyX+Q9pJqS/71739poU4Th9bcY53/pFfv3Fe5Pf/5DFNiLl++LHpl3wX492nbz93j2e9v7T7Rep2u/Rb39T66YLKH3oa27dUNLx9uR/O4dErBc7DHL2Tcdx2mI3f9Nz1Zbcpyzer3aP2uI3Rol+k6CWu7e2JMNrf3i7+7P0R0oiqOsUanzt1BB3YbjsWNBfLay+Xo2r11utDkz7vpwB0tXx/pjqL4LrRo/yOqfz6be+0F27W3zbbRy5oSMzhbaClQ12gLwzXvXYth9qjSgW+J/XsfpJfk7453HFNiutrFl+fvV9tv3hX30GlZLoXj78uL2k8t8UG07UGPrZMY7rtD+GNDbtg52T9s0Nf2fotOnRb5F6v01Po6PVB4F135bpEK99P6+glaOy3y6UH6/K129Ta4nrZ/d/FTtPdcgy7yhjiWsd4QIm4gXEN25NZHtYFU7P0UFXeLv53sYcKpK5Dlv/0MPW9v2oTZMXCcxJjGXZzw/s8/SOKbLc79Iq09SfS1zx8wl0/47CKSzXeP0UH6GV08/Qw9dl2elKkS0qCfrRdoj2ZvvY8HnYG4+DB9gvvn9NHO7UwHW0Zh0LYwYrKF4Zp32aJrJfL30iXQL99vHqdHab/albrx9xG9n0YGPLYOwb6HRF72Dm97Fx06chc9duo5te8u+v7v/0i/c9N/0SFlmPY2gn9N+4P5zvYQJnl+9j++/F1F8YPBAxPID+2r/tlUpkv+3g+JuxYn3/kboZ5vOyyFleGckHEXJ+D2W97AfIoOcJ6pfNt1EjHJOvY/7ftlk+LvLjpwsE7Hj9fpzk/equUHy+rbTl6gXLNBG9K+aju0j8/R4cIJOviS6t9L9k2KKK7ac/6qNnz5Blu6ZTskp41gvkzcNu/qoS1kkqW07W62cOrobTvb/O8WXCuyrP1P+37ZlD/P16+Q/XpK3fhreXH6KZMsoW0Pwdg6Sdax6zLRJmzsv43ufOwUPb9bDOLeb9Mzzi3/xUfo7648ZOfT/VT2hQIagf0X107Q+uhm7wiHjd3CyV9HD3xE2EPlEJ2mez8SckdmssfuPI2unxf3d4qL5726Wn5k+3Ubl6iYxl1u7BY/lEQPTNzPt6T2OXUs3zvkHfL6dfTJrreSnbhIj372fho9clizr93Hp46f8PoouY7yzoY+VoxpLPtkm97ZwkQHWzCma3Eg10qcfjHpG3+buP00MZxjG/rOSz5R9eXtpmv5JE7vonv+8yTRxBX0F1eK9JETdPtPH6FbxJ3ePUsLVHfz+QfbbsNuy7//o0/dQT99eL9sO3is9mMPV+LTMz5zvuUR+u33iW5nG8j0Vbr2+wv0MdUnr28me+yn2a+d8+ofP+fV3XuOvqryw+wXbkfzuLjnHJIur99PH3XOgdPhhmHc7fK7DtwhznMv3X5gl2rDdJ20n2eUxFU6Ped329x1iH7wO++8wo7F6Nuc/P29jn72L3+gr9/iL8N9vCy+tV4fA2M18W1uWR7PPq5pLM220Y9nStzEoG3ByV+3my24jOlaNNhjV078+PO5HGp/JxOSuKmOdunSr7ZrXqSbHrHfc3FKzfhvsp+cGG978GPrJKfvDiOv//b3ImuLef4wXfXsbfT61zd12zeUvPuKd9H//vL/6Jpr/prefPMtldtj/kfcsdxN9O8/PEy7VFYiUjouA7H5kLJtbLHF12JWrpFU9LNHvzNO3z/4gb+R26GRV/x0mv75L6+kq5x0cIOOz9wSUi79id2//Mv/D+zrXZJjRZfb8rsl07gE8t10ONadUL/SYGw+nCm9tujtb0R67RIvDWc/+/P77/ZdMfLab37rbYGuXCnC31/84pf0vvddQ2+88abKBb0ENveALcLJil2yPP5O33fu+IDcHjl37hycFwAAgFQwOjoq/468+eabcF4x4dCV/4tx0D9gcw/YIpys2CXL4899f+c73yk/j7z66qut97znPST+Ev7G+wsAAKB/XH311eqTcF6/+vVvEHnF5Io/u+wzIug9r732GmyugC3CyYpdsjr+wX5H+4+UgQ+OvAAAAAwOOK8E4JEhAAAMFjivBCDyAgCAwWJ2Xpe+QX//3ptp6ZLaZjjvwDfo52ozMjHq/fzfbqZr3vtXbpr7gdrRS2L2K0rk1VzcJ2cEOSmSmnbTL9PdsQ6rNo/so0XTCs3cVkZUlv2wdLsn0R4cBzvpEu7+8oxeZ59mYJ9UumFwTGWi1N16YItwemcXncH3f3v3s0vk9WP64ucSOCsmgaNjx7X35EFa//Vv6FcynSD6hwQOLKmTjUi3yIsHPL8yqcltV4nGujkwceHkV2iyoeo0ylQf819IOmsnl6hUKtDKahf3lBkn1qTFffxFGaMllcPkZs6oMfDsapUmqGgoz/aami0oGfQqFWan7BsEkT9fdyTUG1Suz7ffOJjKRKm7pcAW4fTYLjoD7X82+tnZed14nL48eoQe7Uf0Qxfp+ZNEX/7mPbRD5RDtpwXhxBZuVptDQufIy5YJLy/r8vxFqohBrBTVZhjNC1S3JslVF2eJ/1ZF1AyjSRfqJZqoTFDBJ1EeArdzRj+X7UqOZs7YX7KSygljbUHcIMyxVTuUt0YpLz8UaaJUo42G+NjYoFphj7JjjvYUVL6OqUyUulsKbBFOj+2iM9D+Z6OfXd957b/3OJ1/MCSK4ejGfbw3Qz908g7M0NwBkXfDEXrxJ0dorxYBPX9fh8eBl07TKTpI+3eq7SC+46nHmSrCWvK1e5GWPqcdWz8ncZ5zWlm7vN18HDpGXs1VEpeE54SCiDsS79GgFlnlxkWtWZqKcnvCxyjwHVOeRsXR3OBLbzs/SzUnL5OPD0OQd4MdxoaR4+DYdI1OLombBPH9bl6oi++x/TVm8qMW1S/4rWoqE6Vu34EtwtmEXXSGvv/boJ/dJ2zsvIfua4u+bAfx4e/aj/fWv3KO/vE+pdLyk3OU/6bIf/E43SAit/XnVCQlnEljTD0OFPtCHaIRPt5TdNuLTv2DdMp5nNnW7mna/83gsdU5/XqRFh5SZROdh03y2YYiPJ+apUKV73Ja1CjXacx9lsh3Pw2aXMkrx2Z+n9UUoV1BXkU5Gp8k9ejQ33ar2umeK5tIux3tFoWKcVgWX9k8j8E8jTZM0W+6gS3CyYpdtkM/uzsvwSceOkGk/8jLKOk4HVKP83bcepBu2Gja+280RE/CmTjlY8PH+8mP6Ys3qIhJRnVP0fOs9BalXf2c9AiO21HZcUg821BGZWWSkbogJzyPVb+gRUXswBznU6DZqbCIiR9L1mhpjC+oEcrP1qjGjw4DbYtbHbLUR8Cs0cJsoe3OsQ2OVKeIluWz+mWiqQ6TYlILbBFOVuyyPfoZyXnxu6dDE0/R4YdfVts9YOetdBsJh6TPbvTxGXrCiZhk+gGVYsuDPk9zN+gRnIjQ1J44dIy8fKH2JihOUKm2QW2PieUjw6rt4GRqCJcljrflz823F83FeaqX57reOfIdKU2OqztSL7LN7SlQTXto39ioUWGP/77VVCZK3X4CW4SzWbvoDHP/t0s/IzovEV390wP04eX/sCMV6Wi8R4k/P/0UvTia0yZaJGE37Z+gwOxG4Wz4vdRFcbwb/4PWnEeXMnpS79licx3lnCjs4ss9iLzsQfZHTTwFdYSmG+zYZmlBPSnki8N5qSmnpOrTEddO0pL7stRDXlDas2T3orqwp71t+yOQ0Sp/D7t/eeSXzp0EY0e58kvHkawbJfOEGcs/DIypTJS6fQO2CGcL7KIztP3fRv0UEUgrNL14vHXDjcdb61re+lc+1iInj/cTS6KJpOe5dU60Pi33f6b1RLCtkLZ9x3DaFenT33X2Oe1x+ljryy+KPGO75mMH25fn3uF8wpKIvETQ05lG2fIdp1R1d7QsJ98qtxoqm6mWvPJEpZZTxaPaKpHVKuuVGNmmKO9r22pZ3D7nBY6TRqLY3IZtFLBdtdQidwCCtJf3jYNWLzzfXz9e3WTAFuEM3i796X9W+hkk2G8szJsAfl8mbKe2hhznuXXKp8tjMVoP2CIcLMy7vcHCvFuAuANQn4YcXoWDp8y7/70FAABsD+C8EpCatQ2LFXtSR8f/OhoAANIHnFcCUhN5AQDANgXOKwGpibwAAGCbMiISz+KQP8j4G/0vAACA/qJP2BjhKYfqM4iIPevlNbUF+sFrr12dyRlWYWC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM6LV2YIynJwXhJtqBj1glLVPVCPbidpv0w0HxO2Oxiwncjb95g6xo9oeuRfyd81Lc9XVsOUDyS67LhvmciOcuTt0udhcBvtMujR6g6CKBLspjJR6qaVrNglC/3sEnnVDLIcEUjgEJLJ54ew1c4oEWeF7eBo+sbaNI25suN83SinIq6FcDlyg/R5GNyGXx89et1BYOyzhqlMlLppJSt2yUg/Ozsvq0zlgrdSeW+xVzuOLZ8/rFjHhO2+1CfbAbmaiLt+Y55GrTpJ4VajHLmjndZZKp2vy8WpFSqUdHW0qHUHRBQJ9gHKtw+MrNglI/3s+s5rfK5M9fmQKEZ46DY5e87bN03TfFfKa+rVZimvRUCrWjjaFk3Fks9XjzPl8RZp0deurSjsHls/J3Ge03pILMvbzfeC8bljwnaIvvoOX0u1ArF6w6blyNcWaLZwlOZG1XYKiNLnQcq3D4qs2CUr/ew+YSM3Q0fboi+/5LxPzr5Wp9Flkd8okyUit4ZzNyycycaEXZ73hTpEI/bd72TDqT9JK87jzLZ2V2l8OXhsdU6tClUqqmyi84hJ7k5hO0Rf/US+MxU3ToXqVkiWr9H0GFEVa0MCMHR0d16CYqVKpP/Id5KztwzRk3AmrkR9XOSddI1m8ypiklGdUg+O0q5+TnoEx+2o7F5RrDwubIfoq1/kZs6IG5MGjc5vPqqOqjgLAOg/kZwXv3uam1yhqYUNtd0Dusrnl6jqREwynaEZL7qNiLiTzusRnIjQ1J7ecZOw3bPCdvqD4500ar1sv5NxaF6iupVvU04GSWB1aftxR3I5cls5tjablzc6+Vn7c/uMw+EjSp9NZZLba/jJil2y0s+Izkt0duYoFZaW7EhFOppwOfvkdJHPt5bopHMnLaOnpFOU7XchEn45qT72ktzMvcJ2T2jH2kF7Cmf9L0IbDWHDnZu0YXaRjwvdUEuTLE8sR+5MyrBTo2yJIL9BZ2ZSMEJR+jxI+fZBkRW7ZKSfkZ2XjL7EF9hGfLGXy1Qfsx+/yentwfcCuT1U4EkTMZwMP/JpiAgv7zzWG+EXDjzbkH9I7Gnz9uM+jp46vNMwHpv7UBfNqHbG+jXRWURf5evVZ5ti5QUanf+gOA+V5vPChjepvYLal8T5a/unf9Q5P+PwtVOlMWETHts8rUw27Fmq6p2tfU3l5eSLzv5neP/brcgY+6z1zVQmtr1SRFbskpF+YmHeBGBh3v6DhXk9sDBvOFiYd3uDhXkBAACkHjgvAAAAqQPOCwAAQOqA8wIAAJA6Rl599VVM2AAAADD06BM2MNswAZht2H8w29ADsw3DwWzD7Q1mGwIAAEg9cF4AAABSB5wXAACA1GF2XnL9QKWb5aD0s/SsSMSoJ9eok0uT2KmXelsuSftlguX6Rw4GbKdL+GuS/y5anknu35QPJJuRNdfL6EVM+cNO3D7HtVdayYpdstDPLpFXLbBQbgwSOAR2XHKdRLUYqi3nnuBHY6udUSLOCtvB0fQLvnbGqKquG5YvH7OvG3EtdJU1X5umMbcMX3Nq/TdT/rATpc+mMlHqppWs2CUj/ezsvKwylQtBIcpewSuBE5WXHSl3pkgVYcRUagFax4TtIETZL6SOl3uheJIoUjmgi6y5VI+dHFdl7MWbpYJBsUItR9CU8jRq1f0yNsNKRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnVHSnnOZL7UjBy1pbilxWIVrVwtC2akgKXBiFLxnc89ThTHm+RFn3t2irP7rH1cxLnOa2HxLK83XwvGJ87JmyH6Kv/eJIoUWTNpYbRyqoaJ7tum/S5FETV5HSGmKzIwMclK3bJSj+7T9hQS+T7IwjbQRSqHFqy1lGdxhwv4Ejus9CjT4o/KNcf57EeH08XkZykFedxZlu7qzS+HDy2OqdWhSoVVTbRecQkd6ewHaKvftNcnKJZTem7KyLCqroyEFO0UfBLlMr3sOJmrFDtIMMDAOgr3Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu34S8663RbF5FTDKqW6FVDmejtKufkx7BcTsqu1cUK48L2yH66hfue1P3cV80iu5NzRmaCFwV8pFkq0Gj8+matAHAdiaS8xJfbZqbXKGphQ213QOkOrNwSMZf+RJVnYhJ/cjMeNFtRNZoWgpZqjY4OlR7esdNwnbPCtvpD4530qj1sv/9SfMS1a08xe4ScAlzXPFlzVk9lkLKaO/RhpysyMDHJSt2yUo/Izov0dmZo1RYWrLvSaWj8R4lNldXtJd8SeEfBwrMbmTlT3G32xDHs5bsl+iMjJ6SzvzS3lvwy0n1sZfkZu4VtntCO9YO2lM4638R2mgIG+7cpA0zzNo05WcLVA1GXBFkzeVjQSekWlug2VqJJkQ078sXdZ33aENPRmTgY5MVu2SlnyICCadRbllWudVQm0yjbLXIyeP9ojo34ctz61RbJbm/1KoG2wpp20Eew2lXpJIIt2yc9jhZrTJXNrZrPnawfXnuHc4nDF7MuNV6xZwax0R7x0R7Xl6jfL04lp73Qqts6eeh7eP6+jlyKj1uznfb3L7JtrmZailgF5EseZEE9rkXFF8j4vpwtrQy3jXnz3faGzTdbMHE7bNXxpw/7GTFLlkd/2C/sTBvArAwb//BwrweWJg3HCzMu73BwrwAAABSD5wXAACA1AHnBQAAIHXAeQEAAEgdIzyDQ30GAAAAhhZ9wgZmGyYAsw37D2YbemC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM5Lrh+odLMclH6WnhWJGPXkenLOqu+8rmGyBQzjkbRfJliuf+RgwHa6hL8m+e+i5Znk/k35wACvjelfAzOKxLl+De5Tgxi8Lu00vMrKWZCBT0JW7JKFfnaJvGqBhXJjkMAh8A+EXBXcXTmepdcTOLCtdkaJOCtsB0czGJq0KMVHx2hJ5UjEdRFFHn2KF/hV119hdkqWsWVRnOtSJFYkKE0Mp75XxH6mXQY+NlmxS0b62dl5WWUqtwlR9gpetZuovKyvCl6kijCiq+6eJqxjwnYQohwMOZo5w1++KpVUjiSqxLk1qqRpijRRCi+ztrBCk4kF6npMRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnV4xPOcyT3pWCkkuKXFYhWtXC0LZqSApcGIUvGdzz1OFMeb5EWfe3aKs/usfVzEuc5rYfEsrzdfC8YnzsmbIfoa1iIJHHu05Vbo5NLtjyKD3FNzdc7XKsDJisy8HHJil2y0s/uEzZyM3S0LfqyHUShyne3LWqU6zTmeAFHcp8fq/ik+INy/XEe6/HxdBFJ8fPiPM5sa3eVxpeDx1bn1KpQxVXMdcrHOY+Y5O4UtkP0lS5E1LYsri+p2j1Po4126X/WryscDeiGAQD6SnfnJShWqkT6j7yMkjz5/dz4pCdgpkvu60SR6zfBx6vVaFb+oIgkozpxd8zhbJR29XPSIzhuR2X3imLlcWE7RF+9xn3JvNlQmq+PKaJleYOzTDQVmLQkorGF2UJ7NAYA6CuRnBc/+5+bXKGphQ213QN8j2vCKKmX6E46QzNedBuRNZrO6xGciNDUnt5xk7Dds8J2+oPjnTRqvUy+aLx5iepWXr1rAXEpOhF1hxekUSTOOaqiyXEVVdnq3ivaRdlcnKd6eW44J2oosiIDH5es2CUr/YzovERnZ45SYWnJjlSko/EeJfIX3nvJlxT7h8I/u5GnOo/QdEMcz1qik85NtYyekk5TLpA7FvxyUn3sJbmZe4XtntCOtYP2FM76X4Q2GsKGO/EoqpdEkDiXX96VVbeMX/rfnlQ0OawvuxyyIgMfl6zYJSv9FHer4YRI40sJfSeP9ztS0XqeW8csxR/WtkNQpt9ToXba42S1pCK7sV3zsYPty3PvcD5h2HLU4XL1MrFcv0/y/xVx3Ov9Uv+tF1plSz8PbZ9J7t+U77a5fVMU6fN2/LLnzGbk0cWOoZBFz6oMfDeyYpesjn+w31iYNwFYmLf/YGFeDyzMGw4W5t3eYGFeAAAAqQfOCwAAQOqA8wIAAJA64LwAAACkjhGewaE+AwAAAEOLPmEDsw0TgNmG/QezDT0w2zAczDbc3mC2IQAAgNQD5wUAACB1wHkBAABIHWbnJdcPDKyozXlJFIpj1AvKrfdSb8slab9MsFz/yMGA7XQJf03y30XLM8n9m/KBJK6suZ5vus42U3eQmM5bJ0rfTHXTSlbskoV+dom8aoGFcmOQwCGw48qvTCoJak5VorEEPw5b7YwScVbYDo6mX/C1M0ZVdd2wfPmYfd2IayFU1nxtmsbcfL7O2hd6NrYZoe5AMfVZx1QmSt20khW7ZKSfnZ2XVaZymxBlr7BX7C4v6yJ/RaoII3ZQuRherGPCdhCi7Be5mTOaHAorFCgFWIOsuVSMdaVPijRXrnuqBQpTm1HqDpSMyMDHJit2yUg/u77zGp8zqA0LD+3J8qs7T85zJPelYKSS4pcViFa1cLQtmpIClx2k1X3HU48z5fEWadHXrq3y7B5bPydxntN6SCzL2833gvG5Y8J2iL76jydlYpI1D5M+6Sx37rUZv25/iSLlbioTpW5ayYpdstLP7hM2cjN0tC36sh1EocqhZYsa4s5zzPECjuQ+Cz36pPiDcv1xHuvx8XQRyUlacR5ntrW7SuPLwWOrc2pVqOKIFiY6j5jk7hS2Q/TVb5qLUzSrKX2HUqxQVVzXeXkTM0Ubhc6ypL42Y9YFAGw93Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu3wQfr1aj2byKmGRUt0KrHM5GaVc/Jz2C43ZUdq8oVh4XtkP01S/c96bOjUsHXPXl1hma6HAlhLUZtS4AoDdEcl7iq0pzkys0tbChtnuAVGcWDsn4K1+iqhMxqR+NGS+6jcgaTef1CE5EaGpP77hJ2O5ZYTv9wfFOGrVeJl803rxEdStPsbsEXMKcTDRZc1aMpVC58+7O0Fx3UETps6lMNHulk6zYJSv9jOi8RGdnjlJhacm+x5SOxnuU2Fxd0V7yJYVfiFNgdqNwNiJCmm6I41lL3ktxGT0lneFVIHcs+OWk+thLcjP3Cts9oR1rB+0pnPW/CG00hA13btKGGWZtmvKzBaoGnYxB1pydkjsNeG2BZmslmghG8IY2I9UdJFmRgY9LVuySlX6KCCScEGl8KaHv5PF+Rypaz3PrsOQ07y+1SfGHte0QlOn3VKid9jhZrTJXNrZrPnawfXnuHc4nDFuOOlyuXiaW6/dJ/r8ijnu9X+q/9UKrbOnnoe0zyf2b8t02t2/qJn3uky5XyZIXSWCfQe7cf53ZUulR2+y3UnpWZeC7kRW7ZHX8g/3GwrwJwMK8/QcL83pgYd5wsDDv9gYL8wIAAEg9cF4AAABSB5wXAACA1AHnBQAAIHWM8AwO9RkAAAAYWvQJG5htmADMNuw/mG3ogdmG4WC24fYGsw0BAACkHjgvAAAAqQPOCwAAQOowOy+5fqDSzXJQ+ll6ViRi1JPrxjmrvvO6hskWMIxH0n6ZYLn+kYMB2+kS/prkv4uWZ5L7N+WDNnQpc9M1ZCqjX4P7hlVGtgtZkIFPQlbskoV+dom8aoGFcmOQwCG4K3i7K8ezxHoCB7bVzigRZ4Xt4GgGQhSZflMZce1M8WK8Kr8wOzW0MuhGRB+yIAMfm6zYJSP97Oy8rDKV24QoewUr0hKVl/UVvItUEUZ0ldjThHVM2A5ClINAqsF2kenvWMYaVdI0RZooDbHcu4mMyMDHJit2yUg/u77zGp8zqA0LD+3J8nt3ra7kvhSMVFL8sgLRqhaOtkVTUuDSIGTJ+I6nHmfK4y3Soq9dW+XZPbZ+TuI8p/WQWJa3m+8F43PHhO0QffUbqUnURabfWManK7dGJ5eGTO4kAlmRgY9LVuySlX52n7CRm6GjbdGX7SAKVQ4tW9QQd61jjhdwJPdZ6NEnxR+U64/zWI+Pp4tIip8X53FmW7urNL4cPLY6p1aFKq4CrlM+znnEJHensB2ir74TRabfWCZHM8vi+pKq3fM02qiI+AsAMGx0d16CYqVKpP/IyyjJk9/PjU96Ama65L5OFLl+E3y8Wo1m5Q+KSDKqE3fHHM5GaVc/Jz2C43ZUdq8oVh4XtkP01Wvcl8zqJiqKTH9oGb4+poiWZf4y0VRg0hIAYCiI5LzE15zmJldoamFDbfcA3+OaMErqJbr3gzPjRbcRWaPpvB7BiQhN7ekdNwnbPStspz843kmj1svki8abl6hu5dW7FhAX1xG1vSBlNdhuMv1eGVYFJ/ddmK3uvWK+KIeSrMjAxyUrdslKPyM6L9HZmaNUWFqy70+lo/EeJfIX3nvJlxT7h8I/u1E4G3E3Pd0Qx7OWvBfqMnoKmUEWiQK5Y8EvJ9XHXpKbuVfY7gntWDtoT+Gs/0VooyFsuHOTNgRMFJl+Uxn55Q28CxvWL6+RrMjAxyUrdslKP8Xdajgh0vhSQt/J4/2OVLSe59YxS/GHte0QlOn3VKid9jhZLanGbmzXfOxg+/LcO5xPGLYcdbhcvUws1++T/H9FHPd6v9R/64VW2dLPQ9tnkvs35bttbt8URfpcR5cy919D4TLoutr5sMqgO2RVBr4bWbFLVsc/2G8szJsALMzbf7AwrwcW5g0HC/Nub7AwLwAAgNQD5wUAACB1wHkBAABIHXBeAAAAUscIz+BQnwEAAIChJDhJBbMNAQAApA48NgQAAJA64LwAAACkDjgvAAAAqQPOCwAAQOqA8wIAAJA64LwAAACkDKL/BxbIGfAinJIEAAAAAElFTkSuQmCC

    So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

    With an INNER JOIN, it doesn't matter whether you're pulling nh.Grouping_Level or nn.Grouping_Level, because they will be the same based on the join conditions.  That's not true with a FULL OUTER JOIN.  You're only looking at one, when you need to look at both.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • chocthree - Friday, March 15, 2019 10:18 AM

    Hi Jonathan,
    Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAa8AAACNCAYAAAAAePWIAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACOqSURBVHhe7Z1tjBxHmcef5cQRcpcLEh8uSAf4ZcaBzcCXJMi0v1y4xPHsHtHGQXvSSWhFEmaP2LldQjbnkyws0CIsNopmSQI3e5DLfolgcYLjZGfjiIPLB8+tIP5CxmviGZ+JOE58QUoIr3nxXD3V1d3VPV0z3b07L739/0F5p6urqrue6pmnn+5K/Ue+9+QzLQIAAABSxEhLoD6DiLz++ut01VVXqS3QD2BzD9ginKzYJcvjr/f9HfJfAAAAIEXAeSUAwWr/gc09YItwsmKXLI+/3nc4LwAAAKmj3Xk1F2nfyAiNBNL0mtoPOrM27bfdvkVqql2pgMe/V+fci7ad69V3gTZpcZ9n+7VpbTz0xHVCrnevqTWa9m0rZJ1psTeEXtqvGz21RZe6w0yXfoXtt8ukdPx9fVD7GGM/0zm24ZGXVaaGCM84RJOpUab6fIIBGeRA9hBj2M6Oa4yoqtvu6Able2WDbWrfMIw2ZyyLrPoFzw7NVVqpqc+CYsUZjyqVxP/c8akU7QK+671Bo/POl75Ic2WL6hf8Fm6urhCV58TewTAYW0Sou5UkuLb5XIx06Jck+JsnUqWYxvFP0s/hH1sHPieHaI8Nc3uoUNughtqMTG6GzpyZoZza3C7oBvQQd7jzdSo3Kv6LulihVq9ssE3tG0a4zR0mabKwQqvOt6GxQVQqkaU245GjmWXvZi03Pkm0sqp90ZrEv12T44Oz+qBsMex0totO9H6lb/x10jV+UdD7/o4//ulN8qU33qbLl9+mP+l5G+fpp3uvpQ/Izw/Rx92w0qIHN5wyer5IH3+IznEe/23b/1l62skT+x+826t39ynV3pCmt9++LP6+QW+99bZ/38Yp+s7lO+jm92t5vv3c18/S3R9X/e9gD2kvfdtXN6S8+htqR97nHkckvf2wFDb+nHztqHGXecFrQN8XKG9qu0sy2pyTavPm8VH6ztPnZd7TT75Et49fG3Kst0iUpjf0vLBzev8u+nCtbtvp/fvp9svfpZNuH71xflqzNydpc6c97n/YWDqfg7ZxynVJA7WFm9rrhtpC9tl0zYtktMd5evDTs1SrzVK+2/WqUhS7GPsVtt9JKRz/RP100/CNrZOcvju8gz1ZMNH6F6hwxZ/Tu5103Xk68qPDtKvVpIfv+h4drP+Jfv8Hkep30JN3fZ2aMv8LNHrSyX+Q9pJqS/71739poU4Th9bcY53/pFfv3Fe5Pf/5DFNiLl++LHpl3wX492nbz93j2e9v7T7Rep2u/Rb39T66YLKH3oa27dUNLx9uR/O4dErBc7DHL2Tcdx2mI3f9Nz1Zbcpyzer3aP2uI3Rol+k6CWu7e2JMNrf3i7+7P0R0oiqOsUanzt1BB3YbjsWNBfLay+Xo2r11utDkz7vpwB0tXx/pjqL4LrRo/yOqfz6be+0F27W3zbbRy5oSMzhbaClQ12gLwzXvXYth9qjSgW+J/XsfpJfk7453HFNiutrFl+fvV9tv3hX30GlZLoXj78uL2k8t8UG07UGPrZMY7rtD+GNDbtg52T9s0Nf2fotOnRb5F6v01Po6PVB4F135bpEK99P6+glaOy3y6UH6/K129Ta4nrZ/d/FTtPdcgy7yhjiWsd4QIm4gXEN25NZHtYFU7P0UFXeLv53sYcKpK5Dlv/0MPW9v2oTZMXCcxJjGXZzw/s8/SOKbLc79Iq09SfS1zx8wl0/47CKSzXeP0UH6GV08/Qw9dl2elKkS0qCfrRdoj2ZvvY8HnYG4+DB9gvvn9NHO7UwHW0Zh0LYwYrKF4Zp32aJrJfL30iXQL99vHqdHab/albrx9xG9n0YGPLYOwb6HRF72Dm97Fx06chc9duo5te8u+v7v/0i/c9N/0SFlmPY2gn9N+4P5zvYQJnl+9j++/F1F8YPBAxPID+2r/tlUpkv+3g+JuxYn3/kboZ5vOyyFleGckHEXJ+D2W97AfIoOcJ6pfNt1EjHJOvY/7ftlk+LvLjpwsE7Hj9fpzk/equUHy+rbTl6gXLNBG9K+aju0j8/R4cIJOviS6t9L9k2KKK7ac/6qNnz5Blu6ZTskp41gvkzcNu/qoS1kkqW07W62cOrobTvb/O8WXCuyrP1P+37ZlD/P16+Q/XpK3fhreXH6KZMsoW0Pwdg6Sdax6zLRJmzsv43ufOwUPb9bDOLeb9Mzzi3/xUfo7648ZOfT/VT2hQIagf0X107Q+uhm7wiHjd3CyV9HD3xE2EPlEJ2mez8SckdmssfuPI2unxf3d4qL5726Wn5k+3Ubl6iYxl1u7BY/lEQPTNzPt6T2OXUs3zvkHfL6dfTJrreSnbhIj372fho9clizr93Hp46f8PoouY7yzoY+VoxpLPtkm97ZwkQHWzCma3Eg10qcfjHpG3+buP00MZxjG/rOSz5R9eXtpmv5JE7vonv+8yTRxBX0F1eK9JETdPtPH6FbxJ3ePUsLVHfz+QfbbsNuy7//o0/dQT99eL9sO3is9mMPV+LTMz5zvuUR+u33iW5nG8j0Vbr2+wv0MdUnr28me+yn2a+d8+ofP+fV3XuOvqryw+wXbkfzuLjnHJIur99PH3XOgdPhhmHc7fK7DtwhznMv3X5gl2rDdJ20n2eUxFU6Ped329x1iH7wO++8wo7F6Nuc/P29jn72L3+gr9/iL8N9vCy+tV4fA2M18W1uWR7PPq5pLM220Y9nStzEoG3ByV+3my24jOlaNNhjV078+PO5HGp/JxOSuKmOdunSr7ZrXqSbHrHfc3FKzfhvsp+cGG978GPrJKfvDiOv//b3ImuLef4wXfXsbfT61zd12zeUvPuKd9H//vL/6Jpr/prefPMtldtj/kfcsdxN9O8/PEy7VFYiUjouA7H5kLJtbLHF12JWrpFU9LNHvzNO3z/4gb+R26GRV/x0mv75L6+kq5x0cIOOz9wSUi79id2//Mv/D+zrXZJjRZfb8rsl07gE8t10ONadUL/SYGw+nCm9tujtb0R67RIvDWc/+/P77/ZdMfLab37rbYGuXCnC31/84pf0vvddQ2+88abKBb0ENveALcLJil2yPP5O33fu+IDcHjl37hycFwAAgFQwOjoq/468+eabcF4x4dCV/4tx0D9gcw/YIpys2CXL4899f+c73yk/j7z66qut97znPST+Ev7G+wsAAKB/XH311eqTcF6/+vVvEHnF5Io/u+wzIug9r732GmyugC3CyYpdsjr+wX5H+4+UgQ+OvAAAAAwOOK8E4JEhAAAMFjivBCDyAgCAwWJ2Xpe+QX//3ptp6ZLaZjjvwDfo52ozMjHq/fzfbqZr3vtXbpr7gdrRS2L2K0rk1VzcJ2cEOSmSmnbTL9PdsQ6rNo/so0XTCs3cVkZUlv2wdLsn0R4cBzvpEu7+8oxeZ59mYJ9UumFwTGWi1N16YItwemcXncH3f3v3s0vk9WP64ucSOCsmgaNjx7X35EFa//Vv6FcynSD6hwQOLKmTjUi3yIsHPL8yqcltV4nGujkwceHkV2iyoeo0ylQf819IOmsnl6hUKtDKahf3lBkn1qTFffxFGaMllcPkZs6oMfDsapUmqGgoz/aami0oGfQqFWan7BsEkT9fdyTUG1Suz7ffOJjKRKm7pcAW4fTYLjoD7X82+tnZed14nL48eoQe7Uf0Qxfp+ZNEX/7mPbRD5RDtpwXhxBZuVptDQufIy5YJLy/r8vxFqohBrBTVZhjNC1S3JslVF2eJ/1ZF1AyjSRfqJZqoTFDBJ1EeArdzRj+X7UqOZs7YX7KSygljbUHcIMyxVTuUt0YpLz8UaaJUo42G+NjYoFphj7JjjvYUVL6OqUyUulsKbBFOj+2iM9D+Z6OfXd957b/3OJ1/MCSK4ejGfbw3Qz908g7M0NwBkXfDEXrxJ0dorxYBPX9fh8eBl07TKTpI+3eq7SC+46nHmSrCWvK1e5GWPqcdWz8ncZ5zWlm7vN18HDpGXs1VEpeE54SCiDsS79GgFlnlxkWtWZqKcnvCxyjwHVOeRsXR3OBLbzs/SzUnL5OPD0OQd4MdxoaR4+DYdI1OLombBPH9bl6oi++x/TVm8qMW1S/4rWoqE6Vu34EtwtmEXXSGvv/boJ/dJ2zsvIfua4u+bAfx4e/aj/fWv3KO/vE+pdLyk3OU/6bIf/E43SAit/XnVCQlnEljTD0OFPtCHaIRPt5TdNuLTv2DdMp5nNnW7mna/83gsdU5/XqRFh5SZROdh03y2YYiPJ+apUKV73Ja1CjXacx9lsh3Pw2aXMkrx2Z+n9UUoV1BXkU5Gp8k9ejQ33ar2umeK5tIux3tFoWKcVgWX9k8j8E8jTZM0W+6gS3CyYpdtkM/uzsvwSceOkGk/8jLKOk4HVKP83bcepBu2Gja+280RE/CmTjlY8PH+8mP6Ys3qIhJRnVP0fOs9BalXf2c9AiO21HZcUg821BGZWWSkbogJzyPVb+gRUXswBznU6DZqbCIiR9L1mhpjC+oEcrP1qjGjw4DbYtbHbLUR8Cs0cJsoe3OsQ2OVKeIluWz+mWiqQ6TYlILbBFOVuyyPfoZyXnxu6dDE0/R4YdfVts9YOetdBsJh6TPbvTxGXrCiZhk+gGVYsuDPk9zN+gRnIjQ1J44dIy8fKH2JihOUKm2QW2PieUjw6rt4GRqCJcljrflz823F83FeaqX57reOfIdKU2OqztSL7LN7SlQTXto39ioUWGP/77VVCZK3X4CW4SzWbvoDHP/t0s/IzovEV390wP04eX/sCMV6Wi8R4k/P/0UvTia0yZaJGE37Z+gwOxG4Wz4vdRFcbwb/4PWnEeXMnpS79licx3lnCjs4ss9iLzsQfZHTTwFdYSmG+zYZmlBPSnki8N5qSmnpOrTEddO0pL7stRDXlDas2T3orqwp71t+yOQ0Sp/D7t/eeSXzp0EY0e58kvHkawbJfOEGcs/DIypTJS6fQO2CGcL7KIztP3fRv0UEUgrNL14vHXDjcdb61re+lc+1iInj/cTS6KJpOe5dU60Pi33f6b1RLCtkLZ9x3DaFenT33X2Oe1x+ljryy+KPGO75mMH25fn3uF8wpKIvETQ05lG2fIdp1R1d7QsJ98qtxoqm6mWvPJEpZZTxaPaKpHVKuuVGNmmKO9r22pZ3D7nBY6TRqLY3IZtFLBdtdQidwCCtJf3jYNWLzzfXz9e3WTAFuEM3i796X9W+hkk2G8szJsAfl8mbKe2hhznuXXKp8tjMVoP2CIcLMy7vcHCvFuAuANQn4YcXoWDp8y7/70FAABsD+C8EpCatQ2LFXtSR8f/OhoAANIHnFcCUhN5AQDANgXOKwGpibwAAGCbMiISz+KQP8j4G/0vAACA/qJP2BjhKYfqM4iIPevlNbUF+sFrr12dyRlWYWC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM6LV2YIynJwXhJtqBj1glLVPVCPbidpv0w0HxO2Oxiwncjb95g6xo9oeuRfyd81Lc9XVsOUDyS67LhvmciOcuTt0udhcBvtMujR6g6CKBLspjJR6qaVrNglC/3sEnnVDLIcEUjgEJLJ54ew1c4oEWeF7eBo+sbaNI25suN83SinIq6FcDlyg/R5GNyGXx89et1BYOyzhqlMlLppJSt2yUg/Ozsvq0zlgrdSeW+xVzuOLZ8/rFjHhO2+1CfbAbmaiLt+Y55GrTpJ4VajHLmjndZZKp2vy8WpFSqUdHW0qHUHRBQJ9gHKtw+MrNglI/3s+s5rfK5M9fmQKEZ46DY5e87bN03TfFfKa+rVZimvRUCrWjjaFk3Fks9XjzPl8RZp0deurSjsHls/J3Ge03pILMvbzfeC8bljwnaIvvoOX0u1ArF6w6blyNcWaLZwlOZG1XYKiNLnQcq3D4qs2CUr/ew+YSM3Q0fboi+/5LxPzr5Wp9Flkd8okyUit4ZzNyycycaEXZ73hTpEI/bd72TDqT9JK87jzLZ2V2l8OXhsdU6tClUqqmyi84hJ7k5hO0Rf/US+MxU3ToXqVkiWr9H0GFEVa0MCMHR0d16CYqVKpP/Id5KztwzRk3AmrkR9XOSddI1m8ypiklGdUg+O0q5+TnoEx+2o7F5RrDwubIfoq1/kZs6IG5MGjc5vPqqOqjgLAOg/kZwXv3uam1yhqYUNtd0Dusrnl6jqREwynaEZL7qNiLiTzusRnIjQ1J7ecZOw3bPCdvqD4500ar1sv5NxaF6iupVvU04GSWB1aftxR3I5cls5tjablzc6+Vn7c/uMw+EjSp9NZZLba/jJil2y0s+Izkt0duYoFZaW7EhFOppwOfvkdJHPt5bopHMnLaOnpFOU7XchEn45qT72ktzMvcJ2T2jH2kF7Cmf9L0IbDWHDnZu0YXaRjwvdUEuTLE8sR+5MyrBTo2yJIL9BZ2ZSMEJR+jxI+fZBkRW7ZKSfkZ2XjL7EF9hGfLGXy1Qfsx+/yentwfcCuT1U4EkTMZwMP/JpiAgv7zzWG+EXDjzbkH9I7Gnz9uM+jp46vNMwHpv7UBfNqHbG+jXRWURf5evVZ5ti5QUanf+gOA+V5vPChjepvYLal8T5a/unf9Q5P+PwtVOlMWETHts8rUw27Fmq6p2tfU3l5eSLzv5neP/brcgY+6z1zVQmtr1SRFbskpF+YmHeBGBh3v6DhXk9sDBvOFiYd3uDhXkBAACkHjgvAAAAqQPOCwAAQOqA8wIAAJA6Rl599VVM2AAAADD06BM2MNswAZht2H8w29ADsw3DwWzD7Q1mGwIAAEg9cF4AAABSB5wXAACA1GF2XnL9QKWb5aD0s/SsSMSoJ9eok0uT2KmXelsuSftlguX6Rw4GbKdL+GuS/y5anknu35QPJJuRNdfL6EVM+cNO3D7HtVdayYpdstDPLpFXLbBQbgwSOAR2XHKdRLUYqi3nnuBHY6udUSLOCtvB0fQLvnbGqKquG5YvH7OvG3EtdJU1X5umMbcMX3Nq/TdT/rATpc+mMlHqppWs2CUj/ezsvKwylQtBIcpewSuBE5WXHSl3pkgVYcRUagFax4TtIETZL6SOl3uheJIoUjmgi6y5VI+dHFdl7MWbpYJBsUItR9CU8jRq1f0yNsNKRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnVHSnnOZL7UjBy1pbilxWIVrVwtC2akgKXBiFLxnc89ThTHm+RFn3t2irP7rH1cxLnOa2HxLK83XwvGJ87JmyH6Kv/eJIoUWTNpYbRyqoaJ7tum/S5FETV5HSGmKzIwMclK3bJSj+7T9hQS+T7IwjbQRSqHFqy1lGdxhwv4Ejus9CjT4o/KNcf57EeH08XkZykFedxZlu7qzS+HDy2OqdWhSoVVTbRecQkd6ewHaKvftNcnKJZTem7KyLCqroyEFO0UfBLlMr3sOJmrFDtIMMDAOgr3Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu34S8663RbF5FTDKqW6FVDmejtKufkx7BcTsqu1cUK48L2yH66hfue1P3cV80iu5NzRmaCFwV8pFkq0Gj8+matAHAdiaS8xJfbZqbXKGphQ213QOkOrNwSMZf+RJVnYhJ/cjMeNFtRNZoWgpZqjY4OlR7esdNwnbPCtvpD4530qj1sv/9SfMS1a08xe4ScAlzXPFlzVk9lkLKaO/RhpysyMDHJSt2yUo/Izov0dmZo1RYWrLvSaWj8R4lNldXtJd8SeEfBwrMbmTlT3G32xDHs5bsl+iMjJ6SzvzS3lvwy0n1sZfkZu4VtntCO9YO2lM4638R2mgIG+7cpA0zzNo05WcLVA1GXBFkzeVjQSekWlug2VqJJkQ078sXdZ33aENPRmTgY5MVu2SlnyICCadRbllWudVQm0yjbLXIyeP9ojo34ctz61RbJbm/1KoG2wpp20Eew2lXpJIIt2yc9jhZrTJXNrZrPnawfXnuHc4nDF7MuNV6xZwax0R7x0R7Xl6jfL04lp73Qqts6eeh7eP6+jlyKj1uznfb3L7JtrmZailgF5EseZEE9rkXFF8j4vpwtrQy3jXnz3faGzTdbMHE7bNXxpw/7GTFLlkd/2C/sTBvArAwb//BwrweWJg3HCzMu73BwrwAAABSD5wXAACA1AHnBQAAIHXAeQEAAEgdIzyDQ30GAAAAhhZ9wgZmGyYAsw37D2YbemC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM5Lrh+odLMclH6WnhWJGPXkenLOqu+8rmGyBQzjkbRfJliuf+RgwHa6hL8m+e+i5Znk/k35wACvjelfAzOKxLl+De5Tgxi8Lu00vMrKWZCBT0JW7JKFfnaJvGqBhXJjkMAh8A+EXBXcXTmepdcTOLCtdkaJOCtsB0czGJq0KMVHx2hJ5UjEdRFFHn2KF/hV119hdkqWsWVRnOtSJFYkKE0Mp75XxH6mXQY+NlmxS0b62dl5WWUqtwlR9gpetZuovKyvCl6kijCiq+6eJqxjwnYQohwMOZo5w1++KpVUjiSqxLk1qqRpijRRCi+ztrBCk4kF6npMRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnV4xPOcyT3pWCkkuKXFYhWtXC0LZqSApcGIUvGdzz1OFMeb5EWfe3aKs/usfVzEuc5rYfEsrzdfC8YnzsmbIfoa1iIJHHu05Vbo5NLtjyKD3FNzdc7XKsDJisy8HHJil2y0s/uEzZyM3S0LfqyHUShyne3LWqU6zTmeAFHcp8fq/ik+INy/XEe6/HxdBFJ8fPiPM5sa3eVxpeDx1bn1KpQxVXMdcrHOY+Y5O4UtkP0lS5E1LYsri+p2j1Po4126X/WryscDeiGAQD6SnfnJShWqkT6j7yMkjz5/dz4pCdgpkvu60SR6zfBx6vVaFb+oIgkozpxd8zhbJR29XPSIzhuR2X3imLlcWE7RF+9xn3JvNlQmq+PKaJleYOzTDQVmLQkorGF2UJ7NAYA6CuRnBc/+5+bXKGphQ213QN8j2vCKKmX6E46QzNedBuRNZrO6xGciNDUnt5xk7Dds8J2+oPjnTRqvUy+aLx5iepWXr1rAXEpOhF1hxekUSTOOaqiyXEVVdnq3ivaRdlcnKd6eW44J2oosiIDH5es2CUr/YzovERnZ45SYWnJjlSko/EeJfIX3nvJlxT7h8I/u5GnOo/QdEMcz1qik85NtYyekk5TLpA7FvxyUn3sJbmZe4XtntCOtYP2FM76X4Q2GsKGO/EoqpdEkDiXX96VVbeMX/rfnlQ0OawvuxyyIgMfl6zYJSv9FHer4YRI40sJfSeP9ztS0XqeW8csxR/WtkNQpt9ToXba42S1pCK7sV3zsYPty3PvcD5h2HLU4XL1MrFcv0/y/xVx3Ov9Uv+tF1plSz8PbZ9J7t+U77a5fVMU6fN2/LLnzGbk0cWOoZBFz6oMfDeyYpesjn+w31iYNwFYmLf/YGFeDyzMGw4W5t3eYGFeAAAAqQfOCwAAQOqA8wIAAJA64LwAAACkjhGewaE+AwAAAEOLPmEDsw0TgNmG/QezDT0w2zAczDbc3mC2IQAAgNQD5wUAACB1wHkBAABIHWbnJdcPDKyozXlJFIpj1AvKrfdSb8slab9MsFz/yMGA7XQJf03y30XLM8n9m/KBJK6suZ5vus42U3eQmM5bJ0rfTHXTSlbskoV+dom8aoGFcmOQwCGw48qvTCoJak5VorEEPw5b7YwScVbYDo6mX/C1M0ZVdd2wfPmYfd2IayFU1nxtmsbcfL7O2hd6NrYZoe5AMfVZx1QmSt20khW7ZKSfnZ2XVaZymxBlr7BX7C4v6yJ/RaoII3ZQuRherGPCdhCi7Be5mTOaHAorFCgFWIOsuVSMdaVPijRXrnuqBQpTm1HqDpSMyMDHJit2yUg/u77zGp8zqA0LD+3J8qs7T85zJPelYKSS4pcViFa1cLQtmpIClx2k1X3HU48z5fEWadHXrq3y7B5bPydxntN6SCzL2833gvG5Y8J2iL76jydlYpI1D5M+6Sx37rUZv25/iSLlbioTpW5ayYpdstLP7hM2cjN0tC36sh1EocqhZYsa4s5zzPECjuQ+Cz36pPiDcv1xHuvx8XQRyUlacR5ntrW7SuPLwWOrc2pVqOKIFiY6j5jk7hS2Q/TVb5qLUzSrKX2HUqxQVVzXeXkTM0Ubhc6ypL42Y9YFAGw93Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu3wQfr1aj2byKmGRUt0KrHM5GaVc/Jz2C43ZUdq8oVh4XtkP01S/c96bOjUsHXPXl1hma6HAlhLUZtS4AoDdEcl7iq0pzkys0tbChtnuAVGcWDsn4K1+iqhMxqR+NGS+6jcgaTef1CE5EaGpP77hJ2O5ZYTv9wfFOGrVeJl803rxEdStPsbsEXMKcTDRZc1aMpVC58+7O0Fx3UETps6lMNHulk6zYJSv9jOi8RGdnjlJhacm+x5SOxnuU2Fxd0V7yJYVfiFNgdqNwNiJCmm6I41lL3ktxGT0lneFVIHcs+OWk+thLcjP3Cts9oR1rB+0pnPW/CG00hA13btKGGWZtmvKzBaoGnYxB1pydkjsNeG2BZmslmghG8IY2I9UdJFmRgY9LVuySlX6KCCScEGl8KaHv5PF+Rypaz3PrsOQ07y+1SfGHte0QlOn3VKid9jhZrTJXNrZrPnawfXnuHc4nDFuOOlyuXiaW6/dJ/r8ijnu9X+q/9UKrbOnnoe0zyf2b8t02t2/qJn3uky5XyZIXSWCfQe7cf53ZUulR2+y3UnpWZeC7kRW7ZHX8g/3GwrwJwMK8/QcL83pgYd5wsDDv9gYL8wIAAEg9cF4AAABSB5wXAACA1AHnBQAAIHWM8AwO9RkAAAAYWvQJG5htmADMNuw/mG3ogdmG4WC24fYGsw0BAACkHjgvAAAAqQPOCwAAQOowOy+5fqDSzXJQ+ll6ViRi1JPrxjmrvvO6hskWMIxH0n6ZYLn+kYMB2+kS/prkv4uWZ5L7N+WDNnQpc9M1ZCqjX4P7hlVGtgtZkIFPQlbskoV+dom8aoGFcmOQwCG4K3i7K8ezxHoCB7bVzigRZ4Xt4GgGQhSZflMZce1M8WK8Kr8wOzW0MuhGRB+yIAMfm6zYJSP97Oy8rDKV24QoewUr0hKVl/UVvItUEUZ0ldjThHVM2A5ClINAqsF2kenvWMYaVdI0RZooDbHcu4mMyMDHJit2yUg/u77zGp8zqA0LD+3J8nt3ra7kvhSMVFL8sgLRqhaOtkVTUuDSIGTJ+I6nHmfK4y3Soq9dW+XZPbZ+TuI8p/WQWJa3m+8F43PHhO0QffUbqUnURabfWManK7dGJ5eGTO4kAlmRgY9LVuySlX52n7CRm6GjbdGX7SAKVQ4tW9QQd61jjhdwJPdZ6NEnxR+U64/zWI+Pp4tIip8X53FmW7urNL4cPLY6p1aFKq4CrlM+znnEJHensB2ir74TRabfWCZHM8vi+pKq3fM02qiI+AsAMGx0d16CYqVKpP/IyyjJk9/PjU96Ama65L5OFLl+E3y8Wo1m5Q+KSDKqE3fHHM5GaVc/Jz2C43ZUdq8oVh4XtkP01Wvcl8zqJiqKTH9oGb4+poiWZf4y0VRg0hIAYCiI5LzE15zmJldoamFDbfcA3+OaMErqJbr3gzPjRbcRWaPpvB7BiQhN7ekdNwnbPStspz843kmj1svki8abl6hu5dW7FhAX1xG1vSBlNdhuMv1eGVYFJ/ddmK3uvWK+KIeSrMjAxyUrdslKPyM6L9HZmaNUWFqy70+lo/EeJfIX3nvJlxT7h8I/u1E4G3E3Pd0Qx7OWvBfqMnoKmUEWiQK5Y8EvJ9XHXpKbuVfY7gntWDtoT+Gs/0VooyFsuHOTNgRMFJl+Uxn55Q28CxvWL6+RrMjAxyUrdslKP8Xdajgh0vhSQt/J4/2OVLSe59YxS/GHte0QlOn3VKid9jhZLanGbmzXfOxg+/LcO5xPGLYcdbhcvUws1++T/H9FHPd6v9R/64VW2dLPQ9tnkvs35bttbt8URfpcR5cy919D4TLoutr5sMqgO2RVBr4bWbFLVsc/2G8szJsALMzbf7AwrwcW5g0HC/Nub7AwLwAAgNQD5wUAACB1wHkBAABIHXBeAAAAUscIz+BQnwEAAIChJDhJBbMNAQAApA48NgQAAJA64LwAAACkDjgvAAAAqQPOCwAAQOqA8wIAAJA64LwAAACkDKL/BxbIGfAinJIEAAAAAElFTkSuQmCC

    So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

    It would be easier for us to understand if you gave us the data in the tables and the output you expect from the query.

  • Jonathan AC Roberts - Friday, March 15, 2019 12:21 PM

    chocthree - Friday, March 15, 2019 10:18 AM

    Hi Jonathan,
    Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAa8AAACNCAYAAAAAePWIAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAACOqSURBVHhe7Z1tjBxHmcef5cQRcpcLEh8uSAf4ZcaBzcCXJMi0v1y4xPHsHtHGQXvSSWhFEmaP2LldQjbnkyws0CIsNopmSQI3e5DLfolgcYLjZGfjiIPLB8+tIP5CxmviGZ+JOE58QUoIr3nxXD3V1d3VPV0z3b07L739/0F5p6urqrue6pmnn+5K/Ue+9+QzLQIAAABSxEhLoD6DiLz++ut01VVXqS3QD2BzD9ginKzYJcvjr/f9HfJfAAAAIEXAeSUAwWr/gc09YItwsmKXLI+/3nc4LwAAAKmj3Xk1F2nfyAiNBNL0mtoPOrM27bfdvkVqql2pgMe/V+fci7ad69V3gTZpcZ9n+7VpbTz0xHVCrnevqTWa9m0rZJ1psTeEXtqvGz21RZe6w0yXfoXtt8ukdPx9fVD7GGM/0zm24ZGXVaaGCM84RJOpUab6fIIBGeRA9hBj2M6Oa4yoqtvu6Able2WDbWrfMIw2ZyyLrPoFzw7NVVqpqc+CYsUZjyqVxP/c8akU7QK+671Bo/POl75Ic2WL6hf8Fm6urhCV58TewTAYW0Sou5UkuLb5XIx06Jck+JsnUqWYxvFP0s/hH1sHPieHaI8Nc3uoUNughtqMTG6GzpyZoZza3C7oBvQQd7jzdSo3Kv6LulihVq9ssE3tG0a4zR0mabKwQqvOt6GxQVQqkaU245GjmWXvZi03Pkm0sqp90ZrEv12T44Oz+qBsMex0totO9H6lb/x10jV+UdD7/o4//ulN8qU33qbLl9+mP+l5G+fpp3uvpQ/Izw/Rx92w0qIHN5wyer5IH3+IznEe/23b/1l62skT+x+826t39ynV3pCmt9++LP6+QW+99bZ/38Yp+s7lO+jm92t5vv3c18/S3R9X/e9gD2kvfdtXN6S8+htqR97nHkckvf2wFDb+nHztqHGXecFrQN8XKG9qu0sy2pyTavPm8VH6ztPnZd7TT75Et49fG3Kst0iUpjf0vLBzev8u+nCtbtvp/fvp9svfpZNuH71xflqzNydpc6c97n/YWDqfg7ZxynVJA7WFm9rrhtpC9tl0zYtktMd5evDTs1SrzVK+2/WqUhS7GPsVtt9JKRz/RP100/CNrZOcvju8gz1ZMNH6F6hwxZ/Tu5103Xk68qPDtKvVpIfv+h4drP+Jfv8Hkep30JN3fZ2aMv8LNHrSyX+Q9pJqS/71739poU4Th9bcY53/pFfv3Fe5Pf/5DFNiLl++LHpl3wX492nbz93j2e9v7T7Rep2u/Rb39T66YLKH3oa27dUNLx9uR/O4dErBc7DHL2Tcdx2mI3f9Nz1Zbcpyzer3aP2uI3Rol+k6CWu7e2JMNrf3i7+7P0R0oiqOsUanzt1BB3YbjsWNBfLay+Xo2r11utDkz7vpwB0tXx/pjqL4LrRo/yOqfz6be+0F27W3zbbRy5oSMzhbaClQ12gLwzXvXYth9qjSgW+J/XsfpJfk7453HFNiutrFl+fvV9tv3hX30GlZLoXj78uL2k8t8UG07UGPrZMY7rtD+GNDbtg52T9s0Nf2fotOnRb5F6v01Po6PVB4F135bpEK99P6+glaOy3y6UH6/K129Ta4nrZ/d/FTtPdcgy7yhjiWsd4QIm4gXEN25NZHtYFU7P0UFXeLv53sYcKpK5Dlv/0MPW9v2oTZMXCcxJjGXZzw/s8/SOKbLc79Iq09SfS1zx8wl0/47CKSzXeP0UH6GV08/Qw9dl2elKkS0qCfrRdoj2ZvvY8HnYG4+DB9gvvn9NHO7UwHW0Zh0LYwYrKF4Zp32aJrJfL30iXQL99vHqdHab/albrx9xG9n0YGPLYOwb6HRF72Dm97Fx06chc9duo5te8u+v7v/0i/c9N/0SFlmPY2gn9N+4P5zvYQJnl+9j++/F1F8YPBAxPID+2r/tlUpkv+3g+JuxYn3/kboZ5vOyyFleGckHEXJ+D2W97AfIoOcJ6pfNt1EjHJOvY/7ftlk+LvLjpwsE7Hj9fpzk/equUHy+rbTl6gXLNBG9K+aju0j8/R4cIJOviS6t9L9k2KKK7ac/6qNnz5Blu6ZTskp41gvkzcNu/qoS1kkqW07W62cOrobTvb/O8WXCuyrP1P+37ZlD/P16+Q/XpK3fhreXH6KZMsoW0Pwdg6Sdax6zLRJmzsv43ufOwUPb9bDOLeb9Mzzi3/xUfo7648ZOfT/VT2hQIagf0X107Q+uhm7wiHjd3CyV9HD3xE2EPlEJ2mez8SckdmssfuPI2unxf3d4qL5726Wn5k+3Ubl6iYxl1u7BY/lEQPTNzPt6T2OXUs3zvkHfL6dfTJrreSnbhIj372fho9clizr93Hp46f8PoouY7yzoY+VoxpLPtkm97ZwkQHWzCma3Eg10qcfjHpG3+buP00MZxjG/rOSz5R9eXtpmv5JE7vonv+8yTRxBX0F1eK9JETdPtPH6FbxJ3ePUsLVHfz+QfbbsNuy7//o0/dQT99eL9sO3is9mMPV+LTMz5zvuUR+u33iW5nG8j0Vbr2+wv0MdUnr28me+yn2a+d8+ofP+fV3XuOvqryw+wXbkfzuLjnHJIur99PH3XOgdPhhmHc7fK7DtwhznMv3X5gl2rDdJ20n2eUxFU6Ped329x1iH7wO++8wo7F6Nuc/P29jn72L3+gr9/iL8N9vCy+tV4fA2M18W1uWR7PPq5pLM220Y9nStzEoG3ByV+3my24jOlaNNhjV078+PO5HGp/JxOSuKmOdunSr7ZrXqSbHrHfc3FKzfhvsp+cGG978GPrJKfvDiOv//b3ImuLef4wXfXsbfT61zd12zeUvPuKd9H//vL/6Jpr/prefPMtldtj/kfcsdxN9O8/PEy7VFYiUjouA7H5kLJtbLHF12JWrpFU9LNHvzNO3z/4gb+R26GRV/x0mv75L6+kq5x0cIOOz9wSUi79id2//Mv/D+zrXZJjRZfb8rsl07gE8t10ONadUL/SYGw+nCm9tujtb0R67RIvDWc/+/P77/ZdMfLab37rbYGuXCnC31/84pf0vvddQ2+88abKBb0ENveALcLJil2yPP5O33fu+IDcHjl37hycFwAAgFQwOjoq/468+eabcF4x4dCV/4tx0D9gcw/YIpys2CXL4899f+c73yk/j7z66qut97znPST+Ev7G+wsAAKB/XH311eqTcF6/+vVvEHnF5Io/u+wzIug9r732GmyugC3CyYpdsjr+wX5H+4+UgQ+OvAAAAAwOOK8E4JEhAAAMFjivBCDyAgCAwWJ2Xpe+QX//3ptp6ZLaZjjvwDfo52ozMjHq/fzfbqZr3vtXbpr7gdrRS2L2K0rk1VzcJ2cEOSmSmnbTL9PdsQ6rNo/so0XTCs3cVkZUlv2wdLsn0R4cBzvpEu7+8oxeZ59mYJ9UumFwTGWi1N16YItwemcXncH3f3v3s0vk9WP64ucSOCsmgaNjx7X35EFa//Vv6FcynSD6hwQOLKmTjUi3yIsHPL8yqcltV4nGujkwceHkV2iyoeo0ylQf819IOmsnl6hUKtDKahf3lBkn1qTFffxFGaMllcPkZs6oMfDsapUmqGgoz/aami0oGfQqFWan7BsEkT9fdyTUG1Suz7ffOJjKRKm7pcAW4fTYLjoD7X82+tnZed14nL48eoQe7Uf0Qxfp+ZNEX/7mPbRD5RDtpwXhxBZuVptDQufIy5YJLy/r8vxFqohBrBTVZhjNC1S3JslVF2eJ/1ZF1AyjSRfqJZqoTFDBJ1EeArdzRj+X7UqOZs7YX7KSygljbUHcIMyxVTuUt0YpLz8UaaJUo42G+NjYoFphj7JjjvYUVL6OqUyUulsKbBFOj+2iM9D+Z6OfXd957b/3OJ1/MCSK4ejGfbw3Qz908g7M0NwBkXfDEXrxJ0dorxYBPX9fh8eBl07TKTpI+3eq7SC+46nHmSrCWvK1e5GWPqcdWz8ncZ5zWlm7vN18HDpGXs1VEpeE54SCiDsS79GgFlnlxkWtWZqKcnvCxyjwHVOeRsXR3OBLbzs/SzUnL5OPD0OQd4MdxoaR4+DYdI1OLombBPH9bl6oi++x/TVm8qMW1S/4rWoqE6Vu34EtwtmEXXSGvv/boJ/dJ2zsvIfua4u+bAfx4e/aj/fWv3KO/vE+pdLyk3OU/6bIf/E43SAit/XnVCQlnEljTD0OFPtCHaIRPt5TdNuLTv2DdMp5nNnW7mna/83gsdU5/XqRFh5SZROdh03y2YYiPJ+apUKV73Ja1CjXacx9lsh3Pw2aXMkrx2Z+n9UUoV1BXkU5Gp8k9ejQ33ar2umeK5tIux3tFoWKcVgWX9k8j8E8jTZM0W+6gS3CyYpdtkM/uzsvwSceOkGk/8jLKOk4HVKP83bcepBu2Gja+280RE/CmTjlY8PH+8mP6Ys3qIhJRnVP0fOs9BalXf2c9AiO21HZcUg821BGZWWSkbogJzyPVb+gRUXswBznU6DZqbCIiR9L1mhpjC+oEcrP1qjGjw4DbYtbHbLUR8Cs0cJsoe3OsQ2OVKeIluWz+mWiqQ6TYlILbBFOVuyyPfoZyXnxu6dDE0/R4YdfVts9YOetdBsJh6TPbvTxGXrCiZhk+gGVYsuDPk9zN+gRnIjQ1J44dIy8fKH2JihOUKm2QW2PieUjw6rt4GRqCJcljrflz823F83FeaqX57reOfIdKU2OqztSL7LN7SlQTXto39ioUWGP/77VVCZK3X4CW4SzWbvoDHP/t0s/IzovEV390wP04eX/sCMV6Wi8R4k/P/0UvTia0yZaJGE37Z+gwOxG4Wz4vdRFcbwb/4PWnEeXMnpS79licx3lnCjs4ss9iLzsQfZHTTwFdYSmG+zYZmlBPSnki8N5qSmnpOrTEddO0pL7stRDXlDas2T3orqwp71t+yOQ0Sp/D7t/eeSXzp0EY0e58kvHkawbJfOEGcs/DIypTJS6fQO2CGcL7KIztP3fRv0UEUgrNL14vHXDjcdb61re+lc+1iInj/cTS6KJpOe5dU60Pi33f6b1RLCtkLZ9x3DaFenT33X2Oe1x+ljryy+KPGO75mMH25fn3uF8wpKIvETQ05lG2fIdp1R1d7QsJ98qtxoqm6mWvPJEpZZTxaPaKpHVKuuVGNmmKO9r22pZ3D7nBY6TRqLY3IZtFLBdtdQidwCCtJf3jYNWLzzfXz9e3WTAFuEM3i796X9W+hkk2G8szJsAfl8mbKe2hhznuXXKp8tjMVoP2CIcLMy7vcHCvFuAuANQn4YcXoWDp8y7/70FAABsD+C8EpCatQ2LFXtSR8f/OhoAANIHnFcCUhN5AQDANgXOKwGpibwAAGCbMiISz+KQP8j4G/0vAACA/qJP2BjhKYfqM4iIPevlNbUF+sFrr12dyRlWYWC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM6LV2YIynJwXhJtqBj1glLVPVCPbidpv0w0HxO2Oxiwncjb95g6xo9oeuRfyd81Lc9XVsOUDyS67LhvmciOcuTt0udhcBvtMujR6g6CKBLspjJR6qaVrNglC/3sEnnVDLIcEUjgEJLJ54ew1c4oEWeF7eBo+sbaNI25suN83SinIq6FcDlyg/R5GNyGXx89et1BYOyzhqlMlLppJSt2yUg/Ozsvq0zlgrdSeW+xVzuOLZ8/rFjHhO2+1CfbAbmaiLt+Y55GrTpJ4VajHLmjndZZKp2vy8WpFSqUdHW0qHUHRBQJ9gHKtw+MrNglI/3s+s5rfK5M9fmQKEZ46DY5e87bN03TfFfKa+rVZimvRUCrWjjaFk3Fks9XjzPl8RZp0deurSjsHls/J3Ge03pILMvbzfeC8bljwnaIvvoOX0u1ArF6w6blyNcWaLZwlOZG1XYKiNLnQcq3D4qs2CUr/ew+YSM3Q0fboi+/5LxPzr5Wp9Flkd8okyUit4ZzNyycycaEXZ73hTpEI/bd72TDqT9JK87jzLZ2V2l8OXhsdU6tClUqqmyi84hJ7k5hO0Rf/US+MxU3ToXqVkiWr9H0GFEVa0MCMHR0d16CYqVKpP/Id5KztwzRk3AmrkR9XOSddI1m8ypiklGdUg+O0q5+TnoEx+2o7F5RrDwubIfoq1/kZs6IG5MGjc5vPqqOqjgLAOg/kZwXv3uam1yhqYUNtd0Dusrnl6jqREwynaEZL7qNiLiTzusRnIjQ1J7ecZOw3bPCdvqD4500ar1sv5NxaF6iupVvU04GSWB1aftxR3I5cls5tjablzc6+Vn7c/uMw+EjSp9NZZLba/jJil2y0s+Izkt0duYoFZaW7EhFOppwOfvkdJHPt5bopHMnLaOnpFOU7XchEn45qT72ktzMvcJ2T2jH2kF7Cmf9L0IbDWHDnZu0YXaRjwvdUEuTLE8sR+5MyrBTo2yJIL9BZ2ZSMEJR+jxI+fZBkRW7ZKSfkZ2XjL7EF9hGfLGXy1Qfsx+/yentwfcCuT1U4EkTMZwMP/JpiAgv7zzWG+EXDjzbkH9I7Gnz9uM+jp46vNMwHpv7UBfNqHbG+jXRWURf5evVZ5ti5QUanf+gOA+V5vPChjepvYLal8T5a/unf9Q5P+PwtVOlMWETHts8rUw27Fmq6p2tfU3l5eSLzv5neP/brcgY+6z1zVQmtr1SRFbskpF+YmHeBGBh3v6DhXk9sDBvOFiYd3uDhXkBAACkHjgvAAAAqQPOCwAAQOqA8wIAAJA6Rl599VVM2AAAADD06BM2MNswAZht2H8w29ADsw3DwWzD7Q1mGwIAAEg9cF4AAABSB5wXAACA1GF2XnL9QKWb5aD0s/SsSMSoJ9eok0uT2KmXelsuSftlguX6Rw4GbKdL+GuS/y5anknu35QPJJuRNdfL6EVM+cNO3D7HtVdayYpdstDPLpFXLbBQbgwSOAR2XHKdRLUYqi3nnuBHY6udUSLOCtvB0fQLvnbGqKquG5YvH7OvG3EtdJU1X5umMbcMX3Nq/TdT/rATpc+mMlHqppWs2CUj/ezsvKwylQtBIcpewSuBE5WXHSl3pkgVYcRUagFax4TtIETZL6SOl3uheJIoUjmgi6y5VI+dHFdl7MWbpYJBsUItR9CU8jRq1f0yNsNKRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnVHSnnOZL7UjBy1pbilxWIVrVwtC2akgKXBiFLxnc89ThTHm+RFn3t2irP7rH1cxLnOa2HxLK83XwvGJ87JmyH6Kv/eJIoUWTNpYbRyqoaJ7tum/S5FETV5HSGmKzIwMclK3bJSj+7T9hQS+T7IwjbQRSqHFqy1lGdxhwv4Ejus9CjT4o/KNcf57EeH08XkZykFedxZlu7qzS+HDy2OqdWhSoVVTbRecQkd6ewHaKvftNcnKJZTem7KyLCqroyEFO0UfBLlMr3sOJmrFDtIMMDAOgr3Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu34S8663RbF5FTDKqW6FVDmejtKufkx7BcTsqu1cUK48L2yH66hfue1P3cV80iu5NzRmaCFwV8pFkq0Gj8+matAHAdiaS8xJfbZqbXKGphQ213QOkOrNwSMZf+RJVnYhJ/cjMeNFtRNZoWgpZqjY4OlR7esdNwnbPCtvpD4530qj1sv/9SfMS1a08xe4ScAlzXPFlzVk9lkLKaO/RhpysyMDHJSt2yUo/Izov0dmZo1RYWrLvSaWj8R4lNldXtJd8SeEfBwrMbmTlT3G32xDHs5bsl+iMjJ6SzvzS3lvwy0n1sZfkZu4VtntCO9YO2lM4638R2mgIG+7cpA0zzNo05WcLVA1GXBFkzeVjQSekWlug2VqJJkQ078sXdZ33aENPRmTgY5MVu2SlnyICCadRbllWudVQm0yjbLXIyeP9ojo34ctz61RbJbm/1KoG2wpp20Eew2lXpJIIt2yc9jhZrTJXNrZrPnawfXnuHc4nDF7MuNV6xZwax0R7x0R7Xl6jfL04lp73Qqts6eeh7eP6+jlyKj1uznfb3L7JtrmZailgF5EseZEE9rkXFF8j4vpwtrQy3jXnz3faGzTdbMHE7bNXxpw/7GTFLlkd/2C/sTBvArAwb//BwrweWJg3HCzMu73BwrwAAABSD5wXAACA1AHnBQAAIHXAeQEAAEgdIzyDQ30GAAAAhhZ9wgZmGyYAsw37D2YbemC2YTiYbbi9wWxDAAAAqQfOCwAAQOqA8wIAAJA6zM5Lrh+odLMclH6WnhWJGPXkenLOqu+8rmGyBQzjkbRfJliuf+RgwHa6hL8m+e+i5Znk/k35wACvjelfAzOKxLl+De5Tgxi8Lu00vMrKWZCBT0JW7JKFfnaJvGqBhXJjkMAh8A+EXBXcXTmepdcTOLCtdkaJOCtsB0czGJq0KMVHx2hJ5UjEdRFFHn2KF/hV119hdkqWsWVRnOtSJFYkKE0Mp75XxH6mXQY+NlmxS0b62dl5WWUqtwlR9gpetZuovKyvCl6kijCiq+6eJqxjwnYQohwMOZo5w1++KpVUjiSqxLk1qqRpijRRCi+ztrBCk4kF6npMRmTgY5MVu2Skn13feY3PGdSGhYf2ZPnV4xPOcyT3pWCkkuKXFYhWtXC0LZqSApcGIUvGdzz1OFMeb5EWfe3aKs/usfVzEuc5rYfEsrzdfC8YnzsmbIfoa1iIJHHu05Vbo5NLtjyKD3FNzdc7XKsDJisy8HHJil2y0s/uEzZyM3S0LfqyHUShyne3LWqU6zTmeAFHcp8fq/ik+INy/XEe6/HxdBFJ8fPiPM5sa3eVxpeDx1bn1KpQxVXMdcrHOY+Y5O4UtkP0lS5E1LYsri+p2j1Po4126X/WryscDeiGAQD6SnfnJShWqkT6j7yMkjz5/dz4pCdgpkvu60SR6zfBx6vVaFb+oIgkozpxd8zhbJR29XPSIzhuR2X3imLlcWE7RF+9xn3JvNlQmq+PKaJleYOzTDQVmLQkorGF2UJ7NAYA6CuRnBc/+5+bXKGphQ213QN8j2vCKKmX6E46QzNedBuRNZrO6xGciNDUnt5xk7Dds8J2+oPjnTRqvUy+aLx5iepWXr1rAXEpOhF1hxekUSTOOaqiyXEVVdnq3ivaRdlcnKd6eW44J2oosiIDH5es2CUr/YzovERnZ45SYWnJjlSko/EeJfIX3nvJlxT7h8I/u5GnOo/QdEMcz1qik85NtYyekk5TLpA7FvxyUn3sJbmZe4XtntCOtYP2FM76X4Q2GsKGO/EoqpdEkDiXX96VVbeMX/rfnlQ0OawvuxyyIgMfl6zYJSv9FHer4YRI40sJfSeP9ztS0XqeW8csxR/WtkNQpt9ToXba42S1pCK7sV3zsYPty3PvcD5h2HLU4XL1MrFcv0/y/xVx3Ov9Uv+tF1plSz8PbZ9J7t+U77a5fVMU6fN2/LLnzGbk0cWOoZBFz6oMfDeyYpesjn+w31iYNwFYmLf/YGFeDyzMGw4W5t3eYGFeAAAAqQfOCwAAQOqA8wIAAJA64LwAAACkjhGewaE+AwAAAEOLPmEDsw0TgNmG/QezDT0w2zAczDbc3mC2IQAAgNQD5wUAACB1wHkBAABIHWbnJdcPDKyozXlJFIpj1AvKrfdSb8slab9MsFz/yMGA7XQJf03y30XLM8n9m/KBJK6suZ5vus42U3eQmM5bJ0rfTHXTSlbskoV+dom8aoGFcmOQwCGw48qvTCoJak5VorEEPw5b7YwScVbYDo6mX/C1M0ZVdd2wfPmYfd2IayFU1nxtmsbcfL7O2hd6NrYZoe5AMfVZx1QmSt20khW7ZKSfnZ2XVaZymxBlr7BX7C4v6yJ/RaoII3ZQuRherGPCdhCi7Be5mTOaHAorFCgFWIOsuVSMdaVPijRXrnuqBQpTm1HqDpSMyMDHJit2yUg/u77zGp8zqA0LD+3J8qs7T85zJPelYKSS4pcViFa1cLQtmpIClx2k1X3HU48z5fEWadHXrq3y7B5bPydxntN6SCzL2833gvG5Y8J2iL76jydlYpI1D5M+6Sx37rUZv25/iSLlbioTpW5ayYpdstLP7hM2cjN0tC36sh1EocqhZYsa4s5zzPECjuQ+Cz36pPiDcv1xHuvx8XQRyUlacR5ntrW7SuPLwWOrc2pVqOKIFiY6j5jk7hS2Q/TVb5qLUzSrKX2HUqxQVVzXeXkTM0Ubhc6ypL42Y9YFAGw93Z2XoFipEuk/8jJK8n4ccuOTnoCZLrmvE0Wu3wQfr1aj2byKmGRUt0KrHM5GaVc/Jz2C43ZUdq8oVh4XtkP01S/c96bOjUsHXPXl1hma6HAlhLUZtS4AoDdEcl7iq0pzkys0tbChtnuAVGcWDsn4K1+iqhMxqR+NGS+6jcgaTef1CE5EaGpP77hJ2O5ZYTv9wfFOGrVeJl803rxEdStPsbsEXMKcTDRZc1aMpVC58+7O0Fx3UETps6lMNHulk6zYJSv9jOi8RGdnjlJhacm+x5SOxnuU2Fxd0V7yJYVfiFNgdqNwNiJCmm6I41lL3ktxGT0lneFVIHcs+OWk+thLcjP3Cts9oR1rB+0pnPW/CG00hA13btKGGWZtmvKzBaoGnYxB1pydkjsNeG2BZmslmghG8IY2I9UdJFmRgY9LVuySlX6KCCScEGl8KaHv5PF+Rypaz3PrsOQ07y+1SfGHte0QlOn3VKid9jhZrTJXNrZrPnawfXnuHc4nDFuOOlyuXiaW6/dJ/r8ijnu9X+q/9UKrbOnnoe0zyf2b8t02t2/qJn3uky5XyZIXSWCfQe7cf53ZUulR2+y3UnpWZeC7kRW7ZHX8g/3GwrwJwMK8/QcL83pgYd5wsDDv9gYL8wIAAEg9cF4AAABSB5wXAACA1AHnBQAAIHWM8AwO9RkAAAAYWvQJG5htmADMNuw/mG3ogdmG4WC24fYGsw0BAACkHjgvAAAAqQPOCwAAQOowOy+5fqDSzXJQ+ll6ViRi1JPrxjmrvvO6hskWMIxH0n6ZYLn+kYMB2+kS/prkv4uWZ5L7N+WDNnQpc9M1ZCqjX4P7hlVGtgtZkIFPQlbskoV+dom8aoGFcmOQwCG4K3i7K8ezxHoCB7bVzigRZ4Xt4GgGQhSZflMZce1M8WK8Kr8wOzW0MuhGRB+yIAMfm6zYJSP97Oy8rDKV24QoewUr0hKVl/UVvItUEUZ0ldjThHVM2A5ClINAqsF2kenvWMYaVdI0RZooDbHcu4mMyMDHJit2yUg/u77zGp8zqA0LD+3J8nt3ra7kvhSMVFL8sgLRqhaOtkVTUuDSIGTJ+I6nHmfK4y3Soq9dW+XZPbZ+TuI8p/WQWJa3m+8F43PHhO0QffUbqUnURabfWManK7dGJ5eGTO4kAlmRgY9LVuySlX52n7CRm6GjbdGX7SAKVQ4tW9QQd61jjhdwJPdZ6NEnxR+U64/zWI+Pp4tIip8X53FmW7urNL4cPLY6p1aFKq4CrlM+znnEJHensB2ir74TRabfWCZHM8vi+pKq3fM02qiI+AsAMGx0d16CYqVKpP/IyyjJk9/PjU96Ama65L5OFLl+E3y8Wo1m5Q+KSDKqE3fHHM5GaVc/Jz2C43ZUdq8oVh4XtkP01Wvcl8zqJiqKTH9oGb4+poiWZf4y0VRg0hIAYCiI5LzE15zmJldoamFDbfcA3+OaMErqJbr3gzPjRbcRWaPpvB7BiQhN7ekdNwnbPStspz843kmj1svki8abl6hu5dW7FhAX1xG1vSBlNdhuMv1eGVYFJ/ddmK3uvWK+KIeSrMjAxyUrdslKPyM6L9HZmaNUWFqy70+lo/EeJfIX3nvJlxT7h8I/u1E4G3E3Pd0Qx7OWvBfqMnoKmUEWiQK5Y8EvJ9XHXpKbuVfY7gntWDtoT+Gs/0VooyFsuHOTNgRMFJl+Uxn55Q28CxvWL6+RrMjAxyUrdslKP8Xdajgh0vhSQt/J4/2OVLSe59YxS/GHte0QlOn3VKid9jhZLanGbmzXfOxg+/LcO5xPGLYcdbhcvUws1++T/H9FHPd6v9R/64VW2dLPQ9tnkvs35bttbt8URfpcR5cy919D4TLoutr5sMqgO2RVBr4bWbFLVsc/2G8szJsALMzbf7AwrwcW5g0HC/Nub7AwLwAAgNQD5wUAACB1wHkBAABIHXBeAAAAUscIz+BQnwEAAIChJDhJBbMNAQAApA48NgQAAJA64LwAAACkDjgvAAAAqQPOCwAAQOqA8wIAAJA64LwAAACkDKL/BxbIGfAinJIEAAAAAElFTkSuQmCC

    So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

    It would be easier for us to understand if you gave us the data in the tables and the output you expect from the query.

    Yes, DDL and test data + expected outcome would be very welcome.

    Lacking that, how about something like this:
    SELECT
     'North Central' AS Region
     , COALESCE(nh.Grouping_Level, nn.Grouping_Level) AS Grouping_Level
     , (COALESCE(nh.MTDValue,0) - CASE WHEN nn.MTDValue IS NOT NULL AND nn.MTDValue < 0 THEN nn.MTDValue ELSE 0 END) AS MTDValue
     , (COALESCE(nh.MTDTarget,0) - CASE WHEN nn.MTDTarget IS NOT NULL AND nn.MTDTarget < 0 THEN nn.MTDTarget ELSE 0 END) AS MTDTarget
     , (COALESCE(nh.DEValue,0) - CASE WHEN nn.DEValue IS NOT NULL AND nn.DEValue < 0 THEN nn.DEValue ELSE 0 END) AS DEValue
     , (COALESCE(nh.DETarget,0) - CASE WHEN nn.DETarget IS NOT NULL AND nn.DETarget < 0 THEN nn.DETarget ELSE 0 END) AS DETarget
    FROM
     (
     SELECT
      'North Historical' AS Region
      , Grouping_Level
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
     FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
     WHERE Region IN ( '########' )
     GROUP BY Grouping_Level
     ) AS nh
    FULL OUTER JOIN
     (
     SELECT
      'North National' AS Region
      , Grouping_Level
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
     FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
     WHERE Region IN ( '########' )
     GROUP BY Grouping_Level
     ) AS nn
    ON nh.Grouping_Level = nn.Grouping_Level

  • chocthree - Friday, March 15, 2019 9:30 AM

    How do I make it so the subtraction is performed when the INNER JOIN is satisfied, otherwise do the following:
    1) The Historical value is returned if this side of the join has a value
    2) The National value is returned if this side of the join has a value BUT this value needs to be a negative number as the calculation would be, in affect, 0/empty - the National value (a double negative must remain a negative).
    3) If the value doesn't exist in either table a 0 (zero) is returned.

    Please help (amend my query to meet the requirements)?
    Thanks in advance.

    The problem - I believe - is that you want a list of all possible grouping_levels but you have not included anything that will contain that list.  Based on your queries - you will have a value in either the historical or national query and therefore you will never encounter item 3 above.

    For Item 2 - I am not sure what you mean by a double negative must remain a negative.  For example, if the historical value does not exist and the national value is negative - what value should be returned?  If the historical value exists - but is less than or equal to 0.00 - and the national value is negative - what value should be returned?

    With allGroupingLevels (Region, Grouping_Level)
      As (
    Select Distinct
       'North Central'
      , dd.Grouping_Level
     From MISReporting.dbo.tbl_DailyE2E_Div   dd
    Where dd.Region In ('TOP North Historical', 'TOP North National')
       )
      , historicalTotals (Grouping_Level, MTDValue, MTDTarget, DEValue, DETarget)
      As (
    Select Grouping_Level
      , sum(round(convert(decimal(36, 2), dd.MTDValue), 2))
      , sum(round(convert(decimal(36, 2), dd.MTDTarget), 2))
      , sum(round(convert(decimal(36, 2), dd.DEValue), 2))
      , sum(round(convert(decimal(36, 2), dd.DETarget), 2))
     From MISReporting.dbo.tbl_DailyE2E_Div   dd
    Where dd.Region = 'TOP North Historical'
    Group By
       dd.Grouping_Level
       )
      , nationalTotals (Grouping_Level, MTDValue, MTDTarget, DEValue, DETarget)
      As (
    Select Grouping_Level
      , sum(round(convert(decimal(36, 2), dd.MTDValue), 2))
      , sum(round(convert(decimal(36, 2), dd.MTDTarget), 2))
      , sum(round(convert(decimal(36, 2), dd.DEValue), 2))
      , sum(round(convert(decimal(36, 2), dd.DETarget), 2))
     From MISReporting.dbo.tbl_DailyE2E_Div   dd
    Where dd.Region = 'TOP North National'
    Group By
       dd.Grouping_Level
       )
    Select agl.Region
      , agl.Grouping_Level
      , MTDValue = coalesce(nh.MTDValue, 0.00) - coalesce(iif(coalesce(nh.MTDValue, 0.00) <= 0.00, (nn.MTDValue * -1), nn.MTDValue), 0.00)
      , MTDTarget = coalesce(nh.MTDTarget, 0.00) - coalesce(iif(coalesce(nh.MTDTarget, 0.00) <= 0.00, (nn.MTDTarget * -1), nn.MTDTarget), 0.00)
      , DEValue = coalesce(nh.DEValue, 0.00) - coalesce(iif(coalesce(nh.DEValue, 0.00) <= 0.00, (nn.DEValue * -1), nn.DEValue), 0.00)
      , DETarget = coalesce(nh.DETarget, 0.00) - coalesce(iif(coalesce(nh.DETarget, 0.00) <= 0.00, (nn.DETarget * -1), nn.DEValue), 0.00)
     From allGroupingLevels     agl
     Left Join historicalTotals    nh On nh.Grouping_Level = agl.Grouping_Level
     Left Join nationalTotals     nn On nn.Grouping_Level = agl.Grouping_Level;

    Here is what I think you are looking for - but this will not satisfy item 3.  In order to satisfy item 3 - you would need a complete list of all available grouping levels.  If you can identify the query that produces that list - or a table that contains that list - you can put it in place of the query used in the allGroupingLevels CTE.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank you all for your responses.
    There is a winner and that is Jeffrey Williams' post. I had to tweak it slightly to handle the negative values. Jeffrey, I know you required clarification on the negative comments but yoru post helped me to just put the cherry on the cake.
    Thank you all again. I've learnt another trick if I have the same scenario in the future.

  • chocthree - Wednesday, March 20, 2019 4:41 AM

    Thank you all for your responses.
    There is a winner and that is Jeffrey Williams' post. I had to tweak it slightly to handle the negative values. Jeffrey, I know you required clarification on the negative comments but yoru post helped me to just put the cherry on the cake.
    Thank you all again. I've learnt another trick if I have the same scenario in the future.

    Glad I could help - so what tweaks did you make for the negative values?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You'd almost certainly get better performance by combining both of those aggregates into one, like this:
    SELECT
     'North Central' AS Region,
     Grouping_Level,
     (nh_MTDValue - nn_MTDValue) AS MTDValue,
     (nh_MTDTarget - nn_MTDTarget) AS MTDTarget,
     (nh_DEValue - nn_DEValue) AS DEValue,
     (nh_DETarget - nn_DETarget) AS DETarget
    FROM (
     SELECT
      'North Historical' AS Region,
      Grouping_Level,
      SUM(CASE WHEN Region = 'TOP North Historical' THEN MTDValue ELSE 0 END) AS nh_MTDValue,
      SUM(CASE WHEN Region = 'TOP North Historical' THEN MTDTarget ELSE 0 END) AS nh_MTDTarget,
      SUM(CASE WHEN Region = 'TOP North Historical' THEN DEValue ELSE 0 END) AS nh_DEValue,
      SUM(CASE WHEN Region = 'TOP North Historical' THEN DETarget ELSE 0 END) AS nh_DETarget,
      SUM(CASE WHEN Region = 'TOP North National' THEN MTDValue ELSE 0 END) AS nn_MTDValue,
      SUM(CASE WHEN Region = 'TOP North National' THEN MTDTarget ELSE 0 END) AS nn_MTDTarget,
      SUM(CASE WHEN Region = 'TOP North National' THEN DEValue ELSE 0 END) AS nn_DEValue,
      SUM(CASE WHEN Region = 'TOP North National' THEN DETarget ELSE 0 END) AS nn_DETarget
     FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
     WHERE Region IN ('TOP North Historical', 'TOP North National')
     GROUP BY Grouping_Level
    ) d

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply