Moving Annual Total cross tab by month with Difference in one column

  • Hi

    I'm trying to create the below without relying on Excel, but am struggling to get the difference value and % difference showing vertically in one column.

    Account NoCustomer NameYearAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMAT Total
    A123Fidget spinner LTD2016 - 2017£91,441£84,629£93,823£92,732£96,511£100,846£89,192£94,777£101,450£85,837£86,788£1,018,027
    A123Fidget spinner LTD2017 - 2018£84,608£96,389£110,082£137,461£107,662£107,299£135,743£117,290£124,586£124,031£52,770£1,197,921
      Difference-£6,833£11,760£16,259£44,728£11,151£6,452£46,551£22,513£23,136£38,194-£34,019£179,894
      %-7%14%17%48%12%6%52%24%23%44%-39%18%
  • Thanks.  I managed to solve this linking to a time table and using Lag Over (partition AccountNo, [Timetable].Month  Order by [Year].  The data was then returned to SSRS and grouped appropriately.

  • Trawler - Friday, February 23, 2018 4:34 AM

    Thanks.  I managed to solve this linking to a time table and using Lag Over (partition AccountNo, [Timetable].Month  Order by [Year].  The data was then returned to SSRS and grouped appropriately.

    Okay, fine

  • Trawler - Friday, February 23, 2018 4:34 AM

    Thanks.  I managed to solve this linking to a time table and using Lag Over (partition AccountNo, [Timetable].Month  Order by [Year].  The data was then returned to SSRS and grouped appropriately.

    So share... post your code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I have cut all the table joins down to help with making this ledgible.  As mentioned this then goes into SSRS and is grouped up

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAA6kAAACLCAYAAACDU98TAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAC5kSURBVHhe7d1faCTXnS/w39xc8H24wX4JmHjSUQeEMMR7xaKgLEN6PUpozLWSQDvswxWXNsMNIgJhEETQweA2mDTkgmARSAiWgWaRHhbT2OsmGIE1pEPYTNIsM9k8WAhG43Ym+C0jMrBBxHfu+VWdU32quv6ruqqr6vuBnqnqVldXnXOq6pw6vzp17ejo6CkJl5eX/B8AAAAAAADAVL3++utyimh7e1tOmWyN1GazabyZhI2NDdrb25NzkFecj8vLy4mWDQCAPOl2uzgGZgDpnn+oQ0BYvL/fvXtXzkHR/eQnP6GPPvooWiP14uLC+OCqWq2W0UhNanmQDc5HdYJBXgJAGb333ns4BmYA6Z5/qENAWLy/cyP1S1/6knwHiuyHP/xhYCP1v8j/AQAAAAAAADKHRioAAAAAAADMDDRSAQAAAAAAYGagkQoAAAAAAAAzA41UAAAAAAAAmBlopAIAAAAAAMDMwCNowBeGjweAssOjULKBdM8/vzoEfwbl1Ol05NQYHkFTLmEeQYNGKvhCIxUAyg6NpWwg3fPPqw7B77s1VKAc3PIfjdRywXNSAQAAAGBmoIEKnP9cDgD8oJEKAAAAAKlAAxUYygEEQSMVAAAAAAAAZgYaqQAAAAAAADAz0EgFAAAAAACAmYFGKgAAAAAAAMwMNFIBAAAAAABgZqCRCgAAAAAAADPj2tHR0VOeuLy8TPSh2fz8o729PTyEO+c4H90exA0AUBb8kHkcA9OHdM8/1CEgLN7fv/3tb8s5KIOPPvqIXn/9dTlHtL29LadMtkbq3bt3jTcBdHyCQdkAgLLCMTAbSPdiQD5CGFxOoHxCN1L5SldSNjY2UOAAAAAgkocPH9Lc3Jycm5wHgOL4y1/+Yvx///594/8itR24XdXtdjHtMu28cLW/vx/cSE0qHEOFeLz7a9z2mnc/+Ob/Qz4CQGnhGJiupecf0PCzr1npruYhnzgfn5jtEIAJ/5XsjdRPn/6d8T8U21eu/ZucMrk1Uqd61uVGL+QfKmcAUGY4BqZLNUhVuqOBmm9ooAJAHFM986quXcg3vgoKAFBWOAZmA+leDP/9v8kJAIAIEO4LgVTIFQBAGeEYmC6E+xYL56Pem/rhPexLQPTKonkRCuG+5YRwX0gEKmcAUGY4BmYD6V4MCPcFgDimegZAuG8xIOQKAMoMx8B0qV5Tle7oRc03hPsCQBwzcZny5vr/pg/2x6/97z8nPwljjt40vvf3dFO+kzz1Gy6vtxYJp08ogq99/3tGmY62/+XReH+e2NYXFmnf+GyaxxOAcKxzo8t5Ru2vH6zj0Sww+zjEV70AmCoPHOarQn0BdFM9WoQJ9+WT8NainJGuv/Ld/FSUn3+J/rHgDVWEXJXBc/StxWeNqeuLc7kvz3xcefMbcsbH9Vf+BxqjECirY+Cd/n/QH3hCnGf+l16eX1ikH7/C++uIdg4emu8VCN+DylS6q3nIp2nuP3onh3nM1zsVvkfNF4w/k/TP0ujYiPIbXhdPn6PmW27bMl3WRbCJ9QEnZ0eb/ppO2sUpX/k01TNvcLjvHN0wGqgX9O47/0zf/dE/0xsfmgM3zWZFWVQIxDryehqvd1QFokLfSvHgkTaEupXAC3P0zeeJ7t4b5bw8myd054UvbxXaQk8UBMjsGPjoHv1feU5c/p/ji6E3V1+i6+L/u7d/QXfMtwoJ555imH4+mnWzd34rZy3P0jeXtEbCN75Ky3IySapBF+bCaBjXX6ml2iB18+D9fx3XcWHqki5DRTEjXWTjA4mxY3AD8O17ZF07/cbfW1clzFfwFSX9KhC/JjJ+Ypkxrkg8eki//ownnqWvfNl4x+D/2+oKyPfozXX1d3J7QqyTc9n2qzTa1RUrbJFf6V6Bg/z52lJFVHov6NP+J3RXlOcfrDobbupqrleZCvo8QtmV70y+F1S+eR2+Sz8QjW22fEt85hvlcCEa5aIBsPg3gfuH/7oDTM+D9wf0Lp9nnn+JfszlTpwnjIswn/0HHTkq5dHPe/p+6nNuSpnzHlTckwqRfXZhNLCuPz8+Vt/824r4V7xv1NuS89XnzSik5Lidg2FW3TlQnVe/FPUnNu7Q+tH7j413giRfhophqo3U4HDfh/Sre+YUh/i6nlS5MnqLDyw6sQO/OdmAU7jr/R+NUKgxrrBaFUvXZVZoK2rYrux94oPep3803gn+bcuztCzDK/n7IwpeJ7dlG+k20RMkvvemeaXdJNLr/8QPSUa4b9HJUN/PRvTLR3KfXPyqtn/ZG38mfR8M+jxK2Q0jufL9af93ZqPc5/vJrjvkUbbHwMfU/SezR8Mod8Z54oLe/SftQq4Q77xn309NjnPTIzmZAZx7iiGbfLygR9wYtc5lz1GFz1H3xPu2cxVTF2jUS9sn1AVRrotpF3nM/cq8OKsid1wvjE58J8iI7hrn4G959Ko5Lwhr9WbXdZUXmrT1cO84kS+c16bEq4x5lSGffC6RqR45wozue+fgA/MqsWRkkH719tE9+pG8ImG/UvEsVdyu8Iqd9B/kVeY3HN+x7j/78rNmBdf2N+Kl9966EpVjrcB8oCrKRuVe/B/mtzV/+PAD+Te/oDtB66SWzZUTGRpthWK4HMysZd8emW88/yx91ZyKDCFXBacutnz22Chrd/6dy0yFblgnPnUxZnx1cMe4uFShf+CTbuDn0cpuGO7lW1Tm3x4fT+7eFp8H7tMP6YjDKZ33/ClTWHfIn8yPgeI8+C/ygi77w4cD6uqNxzDnngjnUtu5Sb6XJnUPqkp33JOab9nsPxf0K46UUecydUvLv39ifDrGjYdvOcKAua7nvHhToR9rF3nCjWcglhP5O0S/kvei6yH+Y8/RVxyN7OVbzogHfV35Iuz3bBeolm+pbXPZdnFeC9eYhvDCljFdmHwuvhm4TMkVS3EytMW+u13dVVchnBntoBp7PKCR1aBU35En4z+aYSD2v4mZ+fd+Oa4Ih/ltywX9eqiFAQStk1r2vd+NKydaxeWFL+sHFW3Zv+XwTQBvZqivIE5ORrmTJ7PlvzWvqFqf3/vEqrCq8BYOZQn6PFrZDSPZ8q3CKV0rBImvO0A8dw5Uo3JE/+IMIYt07gk6lzrOTQA59clwZDb2xLnMuqVFRr0pX/v+35j7gXWBR13o1C7UMtFg+LVxoVK/uGPWX82Lsm4XRtXFTe8LQq7EOca4F13sz5MXTx/SO8Z6mi/zt+23nFnrqurVE+tusrad67G8PPn3RRg8cZb4lzGvMhQin0tgqo3UMKP7WtRVXquxKnfmF2T4wsQJNU4Yksxg/i1bo5hxwziooeoYOIlfoUdWDChcsdfJTbIhWgi5KrLxqL4TbCG/syTpEERxkvg5Dxj1Ev141SMtoNTyfQyU555Ez6XTpe5BVemOe1LzLbP9R40b8rw4z/E9fyrqzcUf7j2UjcvH9EujB9ZxEdL67mP6VEbrBFP7VpTvmB68z7eicO9Zjb5pvmWy9mPz5TpIoFrXR4/J3NyAfVxdoLbdRgNJCyxjujD5XAJTPXIEhvuqGHk9ht/aqUyql8YKP5poyDmoHkm3BqV4WaO/qUaxfCVylSLsb3vxWye1bH2QFxXiJQ5A07zyjXDfArNCfe1h5mbZM68kP5BXo/VGqzVAy/pc4OfRyu746rV19TGSxzSKWBkw/PYXxjZfX5S9wkqG+x3Mjpk/BoY490Q+l84AnHuKIbt8lOeD51+iH4hj9riRMGk8wNL4wu2jP2Z5fJe3oog64HUt7HM8sjfv2/bb5WJTPanqFXibDMQRpYxNJZ9zKNvLwypUzy1EyXHFSw2sNL7S4xE2IRp6Ziie4/5RfqnGsHYDuXqZVykmQ0EiCfPbXoLWyVo2967Kv1FpoYciAkRgVVwdJ2/zvlQZ8muNYj0u1+YALRf0bv9hiM/DlN3xlWbzvvTJQWCich3Ewof1TEod9jvIgwjnntDn0gw570HFPakQlzqXeV1UVD2WVm/ivhoEcES/CupYcIh6zglijeyt+eQzswfOPE86ByyMZnyBWW27+cJYC8mKUsZUGeLRqa35K+Zznk21kRpmdN933K4QcK+OvJLDj6Qxe3VMfBXYfJaqd68n3w+nf8egLZN7TawBVyx878C/XrnSGfjbXkKsEy9bPUdWMa6KT/lh7gj3LSp1Nc/l5K0uIBm9o/YBiUx62Qz6PEzZFcuQI5gqd2/b758Ja1wpEaIMGCYq+uqZlLqs9juYHXk4Bgade+KcS7OGc08xZJqP6lzmGerL9VDnuYYjEsIPGuZ6gTMR8lYUjdXgYfd+ObnPRyHOeZO3mUHygsuYswx9kmQ+59i1o6OjpzxxeXlpNCovLiYraXG0Wi1aXl7GSaYAOFQH+QgAZYVjYDaQ7sUwzXzkRy9tLUZrVEJIfF/kmy8RffhB6Od9xvGVa/8mp0yfPv07OQVF5sz3/f192t7elnMmHP0BAAAAIKfMMPd8hKmq0bXtr1lbd2NcCeuWAIBsTLUndW9vz7gJGwAAACAMvgdVH9HXOQ8AxYGe1HLKvCf17bfftg14wNOYz9+8GpnP63MF85jHPOaVIs3zMdDvc4Z5zGPefR77D+aD5p2C/h7z+Z8PA/ekQiDcFwQAZYZjYDaQ7sWAfAQ/6EktpzA9qbZGatKeeeYZOTXp61//upyCWff73/9eTk1CPgJA0eEYCAAwHXxroI4bqXxhg/HFDUwXc/ruXft4x4GN1OBHxoS3sbFh9KR+8YtflO/YNRoNOQWzrtvtIh8BoLRwDEwX3yr01ltvGenO9RI1D/mk8hHADbcXdM5GKxSTM99D3ZPK4b5JvNhf//pX+tOf/mRMM57W5/nEwy8F87M5rypnzvzj6TDfVzCPecxjXsnTPB8D3Y5/+rzf9xnmo88/fDh+FnGYv8f8bM6rCw1enzPMl3veKejvMZ//+TCmfk8qrj7nH3oRAKDMcAzMBnrgigH5CH7Qk1pOsXpSvXCjU/GaDsInc/WC/EI+AkCZ4RgIAAAwXaEaqdwQ7XQ6vtNuvK48Q74gHwGgzHAMTJcKBVO9b2FDw2A2oRcVAOIIbKTGbaCyP//5z3IK8gz5CABlhmNgNjhMFPIP+QgAceTwwVUDai+tUXckZwEAAGBK0j/nOkfyDR7Z128d+bMlag/kbCGhXpScopcllJVkoJykYaqNVHuI1H8ambakZ9yoS2vyPeOVSo6ahcf6zZnbWeX6rXVpcrWyKTSB+SgN2uL9md0rnWmXTDqPumuyHLXFt+KS6zKRpiPqrpnvJ/Mbs1LWk0n7JCSTf1B07uG+9v2Wj39rzoI7aHuU86uYnf0njij7nB4maj8Waq/A9K1Rezikdk3OhpBeXrrIpF40XfZwX/t+Y0hpm42y1+5OtSylWnYyKCvOet70zqEoJ4nJcTmZaiN1MkRqlXZUxnGiNY6p3hvSULzHr171POFC7mWBNuXv9jaJdlspnHiiOt2l1ozUNHzzURH5eXC2SqtnJynlYUKumM6V5qEoRzsiRa7KmaZ8gG7Qcb1n7R/DXpXOc5W4AWagjCeXf1Bk3uG+4/22dotPJre149+Iugd9Wl1vUkW+k6gZOkdEEWafU/egqjBRnm8eqroCf3d8Dh8eJp++qeelkmm9aHomw30zqAuK32kd16nXbk61LKVWdjIqK7V2jzbPDqwLYdM9h6KcXFnOy0m24b4Ldbqp5Ual2aRxHb09bvWLl3fDX+9tclxBti3DvfVeaa7T6ukx3bG+57U8eYV6IDKcP1NXO3x+w7iSoD6zXR1xLEu87NvHO8MOze82fLZb8Ewjj+Vrf29frk8ahjS6c0xUv0W36md04LIAPS30q0dh3ndNO+sNfd5ju3knXdqiPp3SbkO8b218yHQWvNdnOgbtLTrb7NFh07aDUNPYQfzSgGcdZdJz+/22yyMtI5chx3Ks38hfGQfwJfbP9dX++Pg3ukPHtEm3rJNa2HOVTzmPtP84jgu2+aj7j/Bw/LeT59Oo+/+UaOs4Pp840sGW1h5X9VPNS4cZqBelznObHXlnm3ekp0oPjzI8uL0r6ig3wzcA4palNMtOJmWlQs31edG+8lzg9KCcmEpUTlIM93Wo3KQ67VLD9UAtEuOkSj3Z6h/urFJ/y/1gOmg3aHd+x/y7Xp2OrV5RkVlbZFyFMa8etMeZ4sN7eUxU8g+IOvyZcSXF+ze4a3vrbNPahp15sa223NeW5bp9NWr7bHdwGjmXLwrPyYrr37ptsy54ZMsRGW1UsRdUbtaJju9oaeZMix6tV6O875Z2flzSVRwMDvUrYbYu4KB0vur6xDGgk/4qresN1NBcyqTH9kcvo9HKkPd+o2RbxiePOwDugo+BJv3qOFd25rWr4t5lMEI5j7T/BAm//9j+Vrx6m2e0pZ274+3/3tQ9qCpMNPieVGbfnlNbL4USvl6Qbl5KM1ovuirf0X19tzlI2DI8ovOzBaOOEs7VylIqZSfLsjJXpYWzc5ffnSKUE7nd5SonKYf76kQr+1Cc7OrH1ODWty0BxWdt7QBeWxEV7DM6n9hSrsyLire69GBkht4r6vYdu1H3gPrWVYag5YnPOtp6GdzXiy/G6H9ba4tGQl8PhdWW5bV9tfZEZWAsKI2cy9e2y/a37tusCxzZ0rgCJNNwIs2caVGhWo2nwr7PyeBMOz8h0tXJN52vuj5iB7auMPldpdKMzsVaX0WIbQ61XVcrQ/77jZRhGR+vH4C/wGOgoq6Ot9t0cKZfFfcrgxHKuZPv/hMk7P7D7Otgj0C6wv4fINqosM7t0ddf5/W+Q9p5aRDfn4F6UdL889Fvm4M481zbblv6PKTz03mqeie8g3O5Xmnm8X4qZSfDslKp0vzpuUjVNKGclLGcZBvuK5hxyioBxy147uUZdx9vUV++P+nUDGE0/k609E/l26It3x6u07n8zN44GH+nsTtPO7Yrml7Lc+P3G24F3asAe6s0O7RJ7vcehU+jIFG2eZIZ6qvCIypkdqbq6+u100d5P3raReGXzldbHy4j6grT0H4frxfeseVkdH5l0inJdI5fhvJQxgHCMq6O9/u2q+Im7zJ4lXLuf+yaljmqLshJQ7L7l/O5qMk9JzXK8TH9vFSyqRdly2ubE8EXfheqotQmyT8t0yo72ZQV3v+nWyfzgnJSrnKSXbivg3GiXejTCW/goE0NvnFZJCgnqv/NtqJ1r90QPBwe0jhKkhPP/D5t6THU+nec4S5+y3Pj9RtuGePVKPPDV0H43qPWRAx4+DQKMrnNOv985B65UzrdbcjCyw1/ntfDHrwKaZT346RdFB7pbEh7fXjHlvtCLF5l0inJ7Yq63+iyKeOxoqmhlKKcy8yLTAtUnajpeJTBK5dzv2PXtDh7HKazf/mGicYW9vgopJ6XdunWi6YnSj7atjkpU+v580nLlMtOumUlao9j8lBO1PvFLifZhfuKxNEHyjE3xi2juKV/4NG6r9HKqmjdB96Yy5X+UzoPLHlhl+dG/w25HCtmmzd3i/qrK+KTOEQB2Jmn3Yb3VQ7vNAoSvM3++XhC/YXxfY3mq6cdPJzLH9FgwKkS8L5n2pnprHpq42+3G7d0TjovwxCVznXzvgnbValRl7rGfNg08Cv3SW6XMy/jyLaMA/gJHe7rKXwZjFfO3fafJI+V9nW3HyuS37/UPagqTDTcPalR+R0f/UwxL2eyXnR1vuG+vtucVBnm5XhdFE9C2LRMsOwUtKx4QjlxVfRykl24b+0W1Y/HvW/czUw7smUuPuPwJSN+WrxaVPds3Zv30W2Nl2PFW+v3ApqP8ggTaum9PDfev1Fry4Fo5OfGADWhYj091Nq0oydChDQK4rbNYQ1O+rQwMRIah/wuUF9e4jKHolbLb9CJ/Gvv9/3SzmzAqZ7b8NtdI+MedQ5JcMZb6JzpLCSel2GI9Rj2NumMb/CXv7vUIrpp/KxfGniVycntT3K7ou03HlIu41M7DwG48CyDSZXziWNX3GOlmwXarJ5Y6+48VuRn/4pXL3CaWl6K789ivWiq/LY5sTJcoep80o2shOuYUctOlmWFOyemeqHeBcpJKcvJtaOjo6c8cXl5aYRkXFxcGB8orVaLOp2O77Qb/vw73/mOnGP/Sb/76RmtTITXwqzr9XpyiiEfJ/FOenKFNLnq9+FqkP7gz34MJGo0GuJflJv4/NOO70HVe0+d85BnGe03/Ci2FlEn5AjT4GVEXTMhZQORXS1PNzY25JRpb29P/Itykm/B5cSZ7/v7+7S9vS3nTCmH+/ZpS7S2/TqyYPYgH72ZN517h6iGhzTNQnL5B0XmHe6L/TaqKPtctNF9YVZN5mMG+02lSR0eNAY765WYjyJZtxoe0z2HopzkVVLlZKo9qcvLy7YBJ8yrz5A3fIJBPgJAWeEYmA1O9+kMngRpQj6CH/eeVCi6zHtSI42ICDML+QgAZYZjYDbQsCkG5CMAxJHd6L6QG8hHACgzHAPTpZ6LqsJEk3tOKmRB5SMAQBTZje4LAAAAAAAA4PCF1157rc0Tn3/+Od2/f58+/vhj2+vRo0f05MkT32m31/Xr1ydCpF588UU5BXny4MEDOWVCPgJAmeAYmK6XX37Z+J/rJPx64403jN44nl5cXMR0zqb5/yh/j+lyTXN7Qvfqq6/GWg6m8zXtzPfhcEg3btyQc6aJgZOc+MZWdROz17Qb/hwDJxUDFybkIwCUFY6B2eB0x/2M+Yd8BD/OAXQwcFI5OPM91MBJPLqv/tLf85p2ewEAAABEpe5BVQ0b3JOab2igAkAcGN0XAiEfAaDMcAzMBvfAQf4hHwEgDozuC4GQjwBQZjgGpuutt96SUybnPAAAFB9G9wUAAICZgzDRYkA+AkAcCPeFQMhHACgzHAPTheekFgvCfQEgDoT7QiDkIwCUGY6BMNsG1F5ao+5IzgJ4QlmBMGajnCDcFwAAAGaGugdVhYma80lWmvyXNWgv0Zrzw0Gblta6NPU626hLa0tLtKRe7YH8IL/s4b6c9ua2WZuW0jaPumty2dMrS6mWnQzKCm+f/jtGmhq/3xYpkSSUk8TkuJwg3BcCIR8BoMxwDMxGVmGitVubRLu3tcrUiLoHfVpdb1JFvjMVXJlsHFO9NzQebM+vXvU8UqVuFk3m4yrtiG1r18RkWtssfqd1XKee8aPTk1rZyais1No92jw7sBpbleah+O0dkaPTgHJyZTkvJwj3hUDIRwAoMxwD0+W8BzXwnlTugdB6CmwdBbbP2jTgStvSFvXplHYb4j23XoVKk9ZX+3SgalijO3RMm3TLqreKCuWaWqajp8VzXWRPyoB/X3zm1WOyUKebWi210myS9bN+22kTdv2S7v2KyXObnT1Z+rwjPVV6aNunp8/g9i5R/WZwA8AvjZ1p51aW0iw7mZSVCjXX50X7ynOB04NyYvL87eKVE4T7AgAAwMwJNyqsqDydVKknewmGO6vU31IVJVFp2yKjN8bsRWhTTVQOD40r+gu0yb0LHj0mek8HV1zntR6OQbtBu/M75jJ7dTpuqQan37owUUk9IOrwZ4cuPSaVm1SnXWq4NmCDlj3mvX4u6WG8P12++ei7zUG09DTSQ1SST1Zc0mdE52cLou0R1PRIpiylUnayLCtzVVo4O3f53SlCOZHbXa5ygnBfCIR8BIAywzEwXeqeVBUm6v+c1Ao121qDr7YiKoNndG7VjPTpCFRPR7tNB2d6D8eATvqisqneMCqBx3TH+I2gdRHf62ifTxDfPxxSr35MDe6VsFUsg5at+K0fi5keV+Aftu23zUG09DTSQ9tuW/o8pPPTeap6J7yUUFlKpexkWFYqVZo/PRepmiaUkzKWE4T7QiDkIwCUGY6Bs208KAe/OKxOqVF7uE7nHGInPvMOZXNn9HT0+7YeDtOpGbZn/F6Ddk/l24L3uoRn3r+lKpbjno3wy/Zav6ulxzR5bXMiRud0tlClOTnrJ6mylFbZyaaszFF1IWRDLGEoJ+UqJwj3BQAAgJmh7kFVYaK+96QO2tTggU5EBYwrYZODc3Alynyfthz3UgXhngBaoOpErXXBDNuzfvOQmlzDDFyXaCrNDm0u9OmEK36Rlu2xfoYrpEdM4cK2TbZtTkrYHp0ky1LKZSfdshK2x3F6UE7U+8UuJwj3hUDIRwAoMxwDsxFndN9R98CjN4Cv6p/S+ZVjFGu0snoaakAQ73XxICqN9sdScCXPrQLrt+yw65dUegTzzUffbTbX8VjGFUZOT0u8Hp3ky1KCZaegZcUTyomropcThPtCIOQjAJQZjoHpct6DOp4XFSUr5Ew+Z7B2izZ5YBD5XovqWm/AgNSzFjlE7bjeMx9nISpdxpglvKwYMa+19g6t9res9bDu8/JdlxDE9+vHjfFyl3hEEtljEWHZnuvnmR4Z8ttmvmdufZVOd83PI6enpULVeWflOZuylFjZybKsDE6ov7oitjxFKCelLCfXjo6OnvLE5eWlEZJxcXFhfKC0Wi3qdDq+02748+XlZdsV6EajIacgT/gqKPIRAMoKx0CApHCl9oRWUhpZ2MKPAWkRddxGVYYIRtQ1E1I2ENnV8nRjY0NOmfb29sS/KCf5FlxOnPm+v79P29vbcs6EcF8IhHwEgDLDMTBd6h5UFSYa+JxUmGmT4b592lpKeeCmSpM6PGhMqj9aPOajSNathoc58E64wXuiQznJq6TKCcJ9IRDyEQDKDMdAgKSoQVaGqYcaGyOcpv2jBVNri7zT0lCNGpv8M3dRTvIsqXKC0X0BAABgZqh7UNWosP7PSYVZF2V0XwAABeG+EAj5CABlhmNgNnxHhYXcQD4CQBxfeO2119o88fnnn9P9+/fp448/tr0ePXpET5488Z12e12/ft0YjOmZZ54xfoi9+OKLcgry5De/+Q3yEQBKC8fAdPE9qC+//LJRJ+HX+++/T5988okxvbi4aDR6MJ2faf4/yt9julzT3J7Qvfrqq7GWg+l8TTvzncOBb9y4IedME6P7OvHoS+ZIW97TbvhzjO5bDFyYkI8AUFY4BqaLG6kc4svpzvUSNQ/5pPIRwI1zlFe/tgUUhzPfQ43uy4+g0V/6e17Tbi+GEKliQD4CQJnhGJgu3JNaLGigAkAcGN0XAiEfAaDMcAzMBvfAQf4hHwEgDozuCwAAADPD+VxUPCcVAKB8MLovBEI+AkCZ4RiYDYSJFgPyEQDiQLgvBEI+AkCZ4RiYLnUPqgoTxT2p+YZwXwCIA+G+AAAAAAAAMDMQ7guBkI8AUGY4BqZL3YOqwkTD35M6oPbSErUHarZNS2J+aa1LI7d5SAXCfXUoowBhIdwXAiEfAaDMcAzMhj1M1KzcGxV467VGXVtNvkbt4ZDaNZ4eUfegT6s7QxoeNqkyMQ9p8cpHq6E26tKanq/WB8kaddeMZQ/aS7RmLzhm4zBEw9D/u+Upo5wOej4ZaWtsb1ukAkAyEO4LAAAAM8N5D+p4foE2e6ICLyr5xmtnnnYbWmPH5iGdny5QdU7OTsxDdlZpRzXUuIHaOKa6lq+96nnyDR3xO63jOvXEj9ZubRLt3tZ+QzYO14MbhsHfLUcZrbV7tHl2YDXAK81Dsb07ImcBkoNwXwiEfASAMsMxMBuBYaK1tmgErFL/QPWAcU8W91zx/1vUp1OjgbDUbjvmVYtBNDDWuPeHX3qPl1zOQPbwWT1sIf9evJyNEqPnSX427onzWl6xBObjQp1uaq3Divh7o6PRyk9jRtDnPdJchcuqeWlwe5eoftNsSFaatL7apwO14NEdOqZNumX+qEHPL1sPa4jv2hS2jFaouS4a4LcdCwFIEMJ9IRDyEQDKDMfAdKl7UFWYqO89qbUVWj09pjtW5ZlxSCX36sheLW4A2OZlE6jdoN35HbPHq1en45bWGOHGwgFRhz+ToZeh/54bJVvjsEcOhdw626Qefzbs0XrVfN9/ecXhO7pv5SbVaZcaIUJtJznTXDSkTlbM9LTlwYjOzxZEG3XcEtZ7RLkBO6/1otrza0g782L9tBad33ddFbWMzlVp4ew8Rr4BhINwXwAAACiZAZ30RYNAdYEZjSW9ISE+6+iNjwh/z40SOqNz47MBcSfeeFkVqtWM5kTA8sqiQs3DIfXqx9Tg3rpIjVVnmmvpacsDDqOdp+o4M8XPyh5R0Tg8EI2zcU+oM7/Eotqi8dg/EZ9Int9N2oyX0UqV5k/PReoCTAfCfSEQ8hEAygzHwHSpe1BVmGjwc1IdDZDQTs3QSiOUsUG7p/JtT1H/XvFav7jLy5cwo/ua9zSqxmrCg++MzulsoUrOWz2NHtF+36Un1C2/VIPO5P1dL0Uso3NUXbCnC0CSEO4LgZCPAFBmOAZmwzdMVBp1D6i/uiLvYYxqwT7IzfCQmr4Niah/r3hV5OMuL1/C5KNSaXZoc6FPJ0m2Ur16/Ph9kQeTAxW55ZejEef53UnFLaMuPdQACUK4LwAAAMwM5z2oXvek8n10DQ5TjBVvWaOV1dMIA79E/XvF+b0RDQbcGoi7vIIZtB2PdNFHuOWeulM6lvGlRmPPmIoqSo+fzBft3stBeyt2IxNlFCA+hPtCIOQjAJQZjoHZmAwTFRVmK/RwiRr8SJEr9D6a9xpuWcsLuh8y6t8r5uM61PcadCIDROMuL298w31rt6h+3BinwdIW0Y7KUx5BdpVOd83PW1SneI84qVB1/pTOQ948WWvLwZLkOhkDCsmBjIKVqIwOTq7QQwwQ7NrR0dFTnri8vDQOJBcXF8YHSqvVok6n4zvthj9fXl62ndwbjYacgjzhUB3kIwCUFY6B2eB0D3M/I8w2ez7yo1BOaGXYTrdxw89jbRF15Ci4cFX8aBojQbUGeLy83djYkFOmvb09OQVF5sz3/f192t7elnMmhPsCAAAAQEr6tLU0+ZzOqao0qcODMqX6o8VlPppm3Wqgclgz94LHC8cGcIdwXwiEfASAMsMxMF3qHlTV++b7nFSYefbecH4+qDkIT+gI2oQYIwin/aMFxSHRelqq0ZmHafeQQ6FhdF8IhHwEgDLDMTAbUUaFhdmFfASAOBDuCwAAADPD+VzU4OekAgBA0Xzhtddea/PE559/Tvfv36ePP/7Y9nr06BE9efLEd9rtdf369YkQqRdffFFOQZ48ePBATpmQjwBQJjgGZoPrJPxaXFw0euMwnc9p/j/K32O6XNPcntC9+uqrsZaD6XxNO/Odw8Vv3Lgh50wTo/s68ehLaqQtr2k3/DlG9y0GLkzIRwAoKxwD08X3oHLvKac710vUPOSTykcAN85RXjG6bzk48z3U6L78CBr9pb/nNe32AgAAAIgLDZtiQD4CQBwY3RcCIR8BoMxwDEyX6jXlHjiGXtR8U/kIABAFRveFQMhHACgzHAMBAADShdF9AQAAYGbgOanFgnBfAIgD4b4QCPkIAGWGY2A2ECZaDMhHAIgD4b4QCPkIAGWGY2C6nPegpnNP6oDaS0vUHqjZNi2J+aW1Lo3c5gFShzIK5YJwXwAAAJg5E2GiqhKuV9SFUXeN1rp+1XKzcq++a77WyP6VGrWHQ2rXeHpE3YM+re4MaXjYpMrEPERhz8dxXlh5OOrSmp43euYmiMsJL3vQXposL1y2QjTu/L/rLGfOMsZEWVrT/6YtvsXKU0Y5DaeVx1AsCPeFQMhHACgzHAPTpe5BVWGi5jxXwol2RCV9ONwhOpANCtHAaZ2v02EzqFq+QJs9/q587czTbsPe2B17SOenC1Sdk7MT8xDFZLjvqpGPRmOLG6iNY6predOrnsuGW4K4nBzXqSd+tHZrk2j3tvYbsoG3Hty4C/7uuJz1+E9besOXG6INOq73xuWwV6Vza2HlKKO1do82zw5cGvAAdgj3hUDIRwAoMxwDZ4GohFOVzDr4nJg6F++IBkJLNHBuGV1L0dTaohGwSn3V2DUaENxzxf9vUZ9OjQbCUrvtmFctBvHbVo+Y3uMllzOQvYNW71zIvxcvZ6PE6HmSn4178byWl0MLdbqptQ4rzSaZOaryxJgR9HmPdPPobR/c3iWq3zQbkpUmra/26UAteHSHjmmT9GKkp7mthzXEd5VKc51WT4/pjvzTQXuLzjZ79gsqYnlNr+Jb2DJaoea6aIDfdiwEwAHhvgAAADAz1D2oKkzUnFcNU8FoGIgG6+A2Hdc7FNiJ6qW2YmtEmDikcodWVa8WNwBs87L51G7Q7vyO7A2r07Gtx0w0Fg6IOvyZDL0M/ffcKNlSIaBiU7trtHW2ST3+bNij9ar5vv/yZovv6L6Vm1SnXWqECLWd5Ew30Rg6WZG9kHo6juj8bEG0UccFRe8R5QbsvNaLak/zIe3Mi/XTWmV+3/U2oJP+Kq1HLaxFLaNzVVo4O5czAO4Q7guBkI8AUGY4BmbDHibKvS9EW9wrw+GhnSrdPqhSp3rb6sFx9u5MDzc4RINAdZ8ZDS29ISE+6+gNlwh/z40SOqNz47MBcQfgeFkVqtWM5kTA8maL/+i+Il8Ph9SrH1OD8zFSY9WZblqa2NKRQ2HnqTrOEPGzskdUNPAORANr3BPqTHOxqLZoAPZPxCeS53ftRt0D6qte4tG5WJs0zXgZrVRp/hSNVPCHcF8IhHwEgDLDMTBdzueiWvMc/mj01hxS9fYWcZcN/2fdp6r17oTnaLyEdmqGVhoN5Abtnsq3PUX9e8Vr/eIubzZVmodG3pqN1Tj56IMbiAsqVHzM6BHt9116Qt3SXDXKTN7fHedLY3eedtQgRtwoMz6Po4hldI6qC+k22yF/EO4LAAAAM8czTHTUpQPaofacaHysrsj7F7nSa0yEZvR0Wd+PasE+yI1oOPtHckb9e8XeOBqLu7z0+Yb7OlSaHdpc6NNJkq1U2WtnhIrrjIaj22BDbmnuaIh5flfPl7ZWtrh8Rt+u4pZRs3cbwA/CfSEQ8hEAygzHwHSpe1JVmKj9Oakj6rbOaZ3vu+OGghWGqQ+sFIzvo2twmKJXrKavGq2snkYY+CXq3yvO741oMODWQNzlZcM33HfQdjzSRR+llht2p3QsY0SNBpsxFZXZa+fekHKSaavdP8kDHsVvKCocrm7eN2sLSx91qeuRjSijUHYI94VAyEcAKDMcA2fHqNui4/ot2WCo0a3NM/M+1aUDqtrusXMSFWYr9HCJGvw4kiv0Ppr3KW5Zywu6lzLq3yvm4zrU9xp0Ircw7vJmTu0W1Y8b4+1Y4vhtlS9mw+501/y8RXVaNb4UVYWq86d0PtGV6q7WloMlyXUyBgWSgxFdCYer9zbpjAd4UtvbIrppLbpEZXRwYjT8AfxcOzo6esoTl5eXRkjGxcWF8YHSarWo0+n4Trvhz5eXl21XoBuNhpyCPOGroMhHACgrHAPTxfeg6r2nznnIM36cyQmt2EJhU8DPYxUNwo66RxQyxI+mMTLDaIBvbGzI9017e3tyCorMme/7+/u0vb0t50yBPancEOUGp9+0F4RIFQPyEQDKDMfAbPiPCgt5MZmPfaP3O73RmIVKkzo8KFOqPwpuzEfTrMfuIYbyCBXuG7eh6gyR6vV61gvyA/kIAGWGY2C6nL2m6EUtEn7GpzmQThIRtFEYIwin/aMwgcOpkQ8QRuh7UrlBqnhNAwAAACQhyqiwMLuQjwAQB0b3hUDIRwAoMxwD06Wei6rCRJ3PTYV8Qdg2AMRx7fHjx8bASe+9957rwElxcRiwc+AkHQaeyA/noCE65CMAFB2OgelSAyVxunO9BAMn5ZvKRwA3zgF0oJzcBk6yNVLv3r1rvJkUFDwAAAAAAHCD0XyB+TZSf/rTnxpvJOVnP/sZPX1qLBoAAAAAAMAGHVrAXB9B8/Of/5z4BQAAAAAAAJC1a0dHR0Z3571794w3koKeVAAAAAAAAHBz7do1OUWOnlSi/w9Z5P1x4ZrlrwAAAABJRU5ErkJggg==

    declare @Startdate date = (select DATEADD ( year, -1, [Year] ) from [Time] where pk_date = cast(current_timestamp as date) )

    declare @EndDate date = (select DATEADD ( year, 1, [Year] ) from [Time] where pk_date = cast(current_timestamp as date) )

    declare @CurrentYear date = (select year from [Time] where pk_date = cast(current_timestamp as date) )

    select * ,

    case when count ( ThisYearSales ) over (partition by AccountNo ) + count ( LastYearSales ) over (partition by AccountNo ) > 0 then 0 else 1 end as NoRecentSalesFlag

    from (

    select Customers.AccountNo ,

    Customers.CustomerName ,

    SalesPersonCurrent.SalesPersonName ,

    [Month] ,

    [Year] ,

    year (Year) as YearNumber ,

    month (Month) as MonthNumber ,

    Time.Year_Month ,

    Time.Fiscal_Month_Friendly_Name as [Month_Name],

    LineSalePrice as ThisYearSales , --YTDCmlSales ,

    LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]) as LastYearSales ,

    LineSalePrice - LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]) as YoYDifference ,

    (LineSalePrice - LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]))

    / LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]) as YoYPCDifference

    from [Time]

    cross join

    Customers

    inner join

    SalesPerson SalesPersonCurrent ON Customers.Company = SalesPersonCurrent.Company AND Customers.SalesPerson = SalesPersonCurrent.SalesPerson

    left join

    ( select Headers.Company , Headers.AccountNo , Customers.CustomerName , Time.Year_Month ,

    Sum ( lines.quantity ) as Quantity ,

    sum ( lines.linesaleprice ) as LineSalePrice

    FROM ThinTable -- (various tables joins here)

    INNER JOIN

    [Time] on Headers.InvoiceDate = [Time].PK_Date

    where Headers.Company = 1

    and Time.PK_Date >= @Startdate

    and CustomerGroups.CustomerGroupDescription = 'Required Business Area'

    group by Headers.Company , Headers.AccountNo , Customers.CustomerName , [Time].[Month] , [Time].[Year], Time.Year_Month

    ) Sales on Sales.AccountNo = Customers.AccountNo and Sales.Company = Customers.Company and sales.Year_Month = Time.Year_Month

    where Time.Day_Of_Month = 1 and Time.PK_Date >= @Startdate and Time.PK_Date < @EndDate

    ) a where [Year] = @CurrentYear

Viewing 5 posts - 1 through 4 (of 4 total)

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