Key Architecture Principles for Non-Profit Organisations


On a number of occasions I’ve worked with clients to help settle on the ‘right’ architecture for their systems and have often had a discuss a range of architecture principles to tease out the ones most relevant. Over time I’ve refined that list, clarified common misconceptions and got better at explaining those principles. Being the generous chap that I am….I thought I’d share my ideas. All I ask is that, if you use this information, you cite this web site as the source.


The main body of this document contains a set of principles with a brief description. You should feel free to supplement this list with other patterns or principles of specific interest to your organisation.

By studying the list you should identify two things:

  • Principles which are of fundamental important to the organisation’s to-be system
  • Identify trades between principles which might oppose. For example systems can find it hard to be highly experimental/agile/flexible and at the same time highly stable/repeatable

This list is not exhaustive but is provided to stimulate debate and discussion. A very effective way to use the information within is as part of small group, facilitated discussions with key stakeholders. To help such an exercise, the principles are divided into groups which roughly align along the differing concerns that different stakeholder groups have.

Enjoy…and happy architecture planning……

Catalogue 1: Altering Systems

This catalogue contains non-functional requirements related to how easy (or otherwise) it is to change a system when it has entered operation. These are of direct relevance to charities and non-profit organisations since this is a fast moving and creative sector in which new approaches, ideas and techniques regularly come on-stream. The more nimble an organisation can be at re-configuring its technical systems, people and processes the more quickly it can take advantage of new opportunities.


Extensibility is an approach whereby the architecture, design and implementation of a system actively consider and cater for future change. For example, extensibility would allow a system (a bunch of people, technology, and information and processes all working together to achieve an objective) to:

  • Take on new responsibilities
  • Handle new information types
  • Consume or provide new feeds
  • Manage new or changed business entities

Extensible systems tend to have greater longevity, avoiding the expensive cycle of procuring large, fixed systems and then throwing them away when the world changes. They also allow organisations to quickly capitalise on opportunities or respond to risks. Extensibility is sometimes confused with modifiability but there is an important distinction. Modifiability is, if you like, the poor cousin of extensibility. Modifiability means ’that is possible to change a system’ whereas extensibility means that ’change has been actively planned for to make it as painless as possible’. Adaptability is often incorrectly used interchangeably with extensibility. However, adaptability is primarily a concern for how user interactions with a system can be adjusted.  Extensibility is of particular value to charitable and non-profit organisations since they can extract more from their upfront investment and quickly adjust to new approaches and techniques.


Modularity is an approach whereby the architecture, design and implementation of a system break it down into separate components. Each component addresses a separate concern and the components have clear boundaries and interfaces with each other. Often not all components will be required for the system to function – only those required for that specific organisation are put in place. Modularity has the advantages of making change to a system more robust since the change can be isolated to the affected module. When focusing on the technical elements of the system (and remember, modularity can be just as well applied to the people elements of a system) it also allows consumers to take advantage of more flexible pricing models – ‘use what you need’. This is of particular value to charitable and non-profit organisations since they can deploy the modular technical elements (and train the modular people elements) for just the processes they need to support. For example, why should you pay for the part of a system which does sophisticated Direct Debit processing if you don’t use Direct Debit? You should be able to opt out of that module and see your procurement and ongoing license costs adjusted accordingly.


Modifiability is simply the ability for the consumer of system to either change it themselves or request a change. At face value this may seem identical to extensibility but there is an important distinction. Modifiability is, if you like, the poor cousin of extensibility. Modifiability means ’that is possible to change a system’ whereas extensibility means that ’change has been actively planned for to make it as painless as possible’. As an illustration consider these trivial examples:

  • A microwave oven is not modifiable. Not by the end user and not by anyone else. If it does what it does and if it ceases to meet your needs you have to dispose of it.
  • Bank account validation software is modifiable – but only by the vendor. If you need a change you have to request it or wait for it on the vendor’s roadmap.
  • A supporter care system with tools to change the entity model is modifiable – both by a vendor as part of deploying/managing or by trained internal staff. How easy that change is will depend upon the quality of the underlying model and the sophistication of the tools provided (see extensibility)

I would urge organisations to regard modifiability as the bare minimum when considering a system. It implies that changes can be made but with no real sense as to how cheap/easy/quick it might be. Think hard about who needs to be able to modify the system and your organisational strategy for managing systems. If you plan to have all aspects of a system under a managed service contract then be wary of including elements that only your own staff can (technically or legally) modify. Vice versa if you want your own staff to be able to modify then consider carefully the systems you put in place (e.g. look for well published API’s, Software Development Kits etc.) so that you have the flexibility to modify using your own staff, a wider community or the vendor.

Catalogue 2: Connecting Systems

This catalogue contains non-functional requirements related to how easy (or otherwise) it is to connect one system to another, thus creating a system-of-systems in pursuit of larger scale, end-to-end processes. I have chosen the word interoperability within this catalogue because of its connotations of flexibility and loose coupling between systems, based upon standards. I have avoided the word integration because of its connotations of tight coupling and the binding of systems in a proprietary fashion to form one indivisible system. This tends to be an area of focus for the technical elements of the system, less so for the people elements. Good information syntax and semantics (meaning) is essential for connecting systems. This is of direct relevance to charities and non for profit organisations since we must interoperate between financial systems, analytic systems, storage systems, supplier systems, web platforms, on-line giving platforms….and we must do it in a way that avoids creating a fragile, hard to change behemoth of a system.


Interoperability is the ability for a range of systems within and between organisations to exchange information, understand information and act upon that information. Interoperability is achieved by each system with the bigger system:

  • Disclosing and clearly describing interfaces
  • Explaining the syntax used to communicate
  • Explaining the semantics (meaning) of information it produces and consumes
  • Using open standards to achieve communication with other systems
  • Being loosely coupled to other systems (see below)

Given the vast proliferation of platforms, and feeds within the charity and non-profit sector – if there were a top three list of non-functionals to strive for across your whole organisation then interoperability should be in it! I have used the word interoperability within this catalogue because of its connotations of flexibility and loose coupling between systems, based upon standards. I have avoided the word integration because of its connotations of tight coupling and the binding of systems in a proprietary fashion to form one indivisible system. Where two systems are integrated they will exchange information securely, safely and accurately but, due to their tight coupling, you may be unable to replace one of them independently of the other and may have to upgrade them in lock-step. For example you may wish to look at how easy or otherwise it would be to use a different financial system with your CRM system. You may need to add systems to our organisation whose sole function is to facilitate the interoperability of the other systems. This topic is one of great interest and concern to those responsible for the cross-organisation architecture and planning of charity and non-profit systems. It should also be of strategic interest since it has a dramatic impact upon the ability of an organisation to respond to new opportunities.

Catalogue 3: Deploying Systems

This catalogue contains non-functional requirements related to how easy it is to get a system set-up to meet an organisation’s needs. Remember we are talking about a rich system of people, processes, information and technology working together so the training of people is as important a deployment activity as the installation and configuration of technical elements.These requirements are of direct relevance to charities and non for profit organisations since there is a wide variety of organisations, a relatively small number of key players within the supplier community (or at least for CRM systems anyway) and only limited appetite (and funding) for fully bespoke systems. The ability to easily adjust systems to meet an organisations functional needs and technical architecture is thus vital.


Configurability is the ability of the technical elements of a system to be adjusted as part of deployment and also on-going management. Within this catalogue I have specifically disambiguated configurability and portability. There are a huge range of possible configurations. Some examples would be specifying the address of the corporate authentication system or perhaps specifying the drivers and database dialect used for an underlying, extant data storage system. When considering a system it is thus vital to consider the constraints you wish to levy upon it and then ensure it can be configured to meet those constraints. This is of particular value to charitable and non-profit organisations since they can capitalise upon existing investments when deploying new ones and ensure a smooth fit between new components and extant ones.


Distributability is the ability to physically separate parts of a system from each other.

  • When thinking about the people elements of the system this could be the ability to operate regionally, nationally or globally distributed teams without location or distance being a hindering factor. This is also related to the internationalizability requirement
  • When thinking about the technical elements of the system this could be (for example) the ability to separate storage, logic and presentation components. Another example would the ability to scale a technical element by creating many copies and distributing them. Distributability of systems is strongly related to other non-functionals like: scalability, modularity, interoperability and stability

Distributability is important to those charity and non-profit organisations who have advanced their considerations of their technical architectures (specifically to escape from the ’all the parts of the system goes on one server in that data centre’  mindset). It is also vital for those who operate on national or global scale


Portability is the ability of the technical elements of a system (e.g. storage systems, processing systems, presentation systems) to be run upon a variety of platforms. In this catalogue I have focussed portability upon server, desktop and mobile operating systems to clearly disambiguate it from configurability.  Many larger organisations lay down technical standards to define what operating systems (and specific builds thereof) and hardware platforms they will use. There is a growing availability of cheaper virtualisation solutions which are being taken up by the charity and non-profit sector and, whilst these do help to manage and instantiate a variety of platforms, there is still value in terms of staff training, skills and complexity management in  being able to narrow that set down. Smaller organisations should also consider portability since it enables them to leapfrog the common anti-pattern of accumulating a variety of technical platforms to the point at which they become a pain to manage and then having to invest to deal with that pain. Portability is important when buying a package because you may not want to introduce a new OS or hardware platform just to accommodate a specific system or application. Portability is important when building bespoke systems because you may wish, in the future, to out-source the hosting of it or move it into a cloud provisioned environment.

Catalogue 4: Protecting Systems

This catalogue contains non-functional requirements related to how easy it is to control access to systems and protect the integrity of the information contained within them. As always with non-functional requirements we are not just looking at the technical elements of the system. These are of direct relevance to charities and non for profit organisations due to the legal constraints upon data holders, the sensitivity of cash-flow to automated regular payment systems and the privacy associated with membership/support of some organisations.


Auditability is the ease with which the interactions with a system by clients (users, other systems, donors, and other organisations) can subsequently be inspected. Keeping audit logs of access to facilities, systems, and sub-systems may well be a legal requirement upon organisations managing data. Auditability is just as important for the people elements of the system and for completely manual or paper based processes as well.

There are several aspects to consider here:

  • Firstly, that all relevant systems are actually collecting and storing audit data
  • Secondly, the ease with which this audit trail can be accessed. There is an overlap here with  manageability
  • Thirdly, the quality of the security measures placed upon the audit trail to maintain its integrity

The definition of auditability can also be expanded for technical systems to allow for the debugging or fault inspection of problematic systems. For example if an extract routine fails as part of building the donor analysis data mart you need to know why it failed so that you can address the issue. This type of auditability is more commonly called traceability and supports maintainability. Auditability is important to charities and non-profit organisations because of their obligations under the Data Protection Act.


Recoverability is the ability to restore a system to a prior state after an incident. For the technical elements within a system there are several aspects of recoverability to consider:

  • The ability to go back to a previous version of a system in the event of an issue with a newer version. This comes down to good source code management, good version control and good rollout strategies
  • The ability to restore data (potentially high volume) in the event of loss or corruption. For many organisations is vital to be able to go back to the time just before the incident to avoid laborious work to recapture the delta between the last good backup and the time of the incident. Some organisations will want to be able to selectively recover beyond the point of the incident where possible.
  • Disaster recovery geographical planning. What good are all those backup tapes if they are stored on-site and you have a fire which burns hotter or longer than the rating of the vault they are in? The ability to recover must not be compromised by the storage of recovery assistance media in the same place or city as the systems they target
  • Fail-over sites or systems. In the event of a system failure you may need to be able to fail-over to an alternative (perhaps lower spec system) until the primary can be fixed. You may also need to consider the recoverability of an organisation (the rich system) by having backup premises and contingency plans.
  • Recoverability testing. The only sure fire way to know that your recoverability plans work (and work to the time-scales required) is to simulate them

Constituent data is the lifeblood of charity and non-profit systems. Losing a bit ad failingly to recover in time can impact cash flow. Losing the lot and being unable to recover could destroy the organisation…


Securability is the ability to ensure that only the right people are allowed access to a system and, once in it, they can only perform functions commensurate with their role. This is as relevant to facilities and workspaces as it is technical systems. Also, it is often the people aspects of systems which present the greatest weakness – many incidents and exploits begin with social engineering tricks to gain access. What good is a two factor technical authentication control on a merchandise order system if someone can join the sub-contracted office cleaning company, walk into the server cupboard and pull the hard drive? Auditability is also related to securability since it can be used to identify breaches or trace incidents. So think about the securability requirement in terms of:

  • Facilities: coarse grained access to the building
  • Rooms and workspaces: finer grained control to areas containing sensitive paperwork or server rooms
  • People: good management of authentication credentials
  • Technical systems: strength and number of authentication factors
  • Technical systems: grades of authorisation when authenticated
  • Technical systems communications: secure all sensitive information in transit between systems (e.g. on-line gifts, data files emailed to and from agencies etc.)
  • Personal communications: some organisations, particularly those operating in difficult or failed countries, may well need to consider how they secure personal communications to protect their staff or their operational information.

Securability is important to charities and non-profit organisations because of their obligations under the Data Protection Act, because of their processing of financial information and, in some cases, to protect their staff in hostile situations.

 Catalogue 5: Using Systems

This catalogue contains non-functional requirements related to how easy it is for an organisation to make use of a system. It is important not to just consider aspects of the technical system here. Think instead of a rich system made up of people, processes, information and technology which, when combined, offer a service to the organisation. This catalogue deals with how easy that rich system is for the organisation to make use of.


Accessibility is the ability of a system to be accessed by as wide a range of people as possible. This is most often taken to mean access by people with disabilities or special needs via the use of assistive solutions. There is a tendency to limit the word accessibility to mean ‘improving the user interface of a system to accommodate disability’ (i.e. to think it is primarily a technical concern). But, of course, because we are always thinking about rich systems, the physical factors (e.g. wheelchair access to a building) are just as important. Accessibility is important to charities and non-profit organisations because of:

  • Their obligations under the Disability and Equality Act
  • Their desire to include and attract as many people as possible
  • The fact that many charities and non-profit organisations work specifically upon disability and special needs issues


Usability looks at how easy users find it to get the hang of a system and then to make productive use of it. Although usability is often discussed with relation to the interfaces of technical systems it can just as well be applied to any tool, device computer system or rich system (composed of people, processes, information and technology). For example you might like to consider the usability of the UK Tax system which is composed of web sites, help desks, phone numbers, forms, people, offices….

I particularly like Jakob Nielsen’s framework for defining the key components of usability. To quote from his definition, usability is:

  • Learnability: How easy is it for users to accomplish basic tasks the first time they encounter the design?
  • Efficiency: Once users have learned the design, how quickly can they perform tasks?
  • Memorability: When users return to the design after a period of not using it, how easily can they reestablish proficiency?
  • Errors: How many errors do users make, how severe are these errors, and how easily can they recover from the errors?
  • Satisfaction: How pleasant is it to use the design?

Usability is also related to accessibility and adaptability. Usability is important to charity and non-profit organisations so that staff can work efficiently and donors find it easy to interact with services (e.g. supporter care teams) and technical systems (e.g. web sites).


Adaptability (not to be confused with extensibility) is the ability for a system to adjust itself to meet the needs of its users. There are two main aspects to consider:

  • Firstly, adaptability, which is the ability for a user to make changes to a system by customizing it themselves
  • Secondly, adaptivity, which is the ability for a system to automatically configure itself for its users

Both of these techniques are complementary and work together to create systems which more naturally and flexibly fit users’ needs. This approach could also be extended to consider the technical interfaces between systems (i.e. to think neutrally in terms of consumers and providers rather than users and systems). However, it is my view that this is a discussion best held under interoperability instead. You may also wish to review the sections on accessibility and internationalizability since they are also relevant to how users interact with systems). Adaptability is important to charities and non-profit organisations since it allows staff to be more productive and minimises frustration with system interfaces.


Stability is the ability of a system to maintain a regular, available, error free state of operation (providing it is operating within the load, environment and configurations it was specified to meet). For the sake of simplicity I have taken the view that our detailed discussion of reliability, availabilty, and scalability has sufficiently covered the topic of ‘whether systems can do what is expected of them when they are required to do so’.


Internationalizability is the ability for a system to adapt itself for a specific region or language without engineering changes. Strictly speaking internationalization (often written i18n due to the length of the word) is the process of designing a system so that this adaption is even possible. Localization (L10n) is the process by which the specific regional adaptions are provided. For example a supplier would use i18n when writing a system to extract all hard coded user interface messages and L10n to provide a language ‘pack’ of message translations for all the countries the system will be used in. For brevity I have collapsed both these terms under internationalizability. Related non-functional requirements which assist with internationalizability are:  adaptability, configurability and modularity. Extensibility is also important because (when combined with i18n and modularity) it provides a technique by which locale specific extensions can be created for system modules. A good example would be regional banking practices. A non-profit organisation operating a single CRM system across a number of world regions (for economy of scale) might opt to license the credit card payment module and then use (or request) extensions to that module which cater for specific in-country banking practices. The system would also use i18n to adjust for currency, date format, system messages etc. Internationalizability is of particular interest to charity and non-profit organisations who operate globally and seek the economy of scale associated with the centralised procurement, and hosting of systems.


Repeatability is the ability of a system to produce the same outputs given the same inputs (or at least to produce with them with an agreed margin of error). For the sake of simplicity I have taken the view that the detailed discussion of reliability, availabilty and maintainability has sufficiently covered the topic of ‘whether systems can do what is expected of them when they are required to do so’.

Catalogue 5: Managing Systems

This catalogue contains non-functional requirements related to how easy it is to look after a system once it is up and running. This is not the same as altering systems which is handled in a separate catalogue. Managing systems is concerned with ensuring they perform under load (technology and people), that they behave in a predictable fashion and that they are ready when called upon. These are of direct relevance to charities and non for profit organisations since in-house hosting is still the prevalent model (needs evidence). A gradual shift to outsourcing or cloud hosting for the non-profit sector will not remove the requirement, but should make it easier to satisfy.


Manageability is one of those non-functional requirements that often make it onto lists but, equally often, seems to lack a clear and consistent definition. It is most often used as a catch-all for the ease with which deployment, configuration, maintenance and troubleshooting can be conducted. However I feel there is a specific aspect which is unique to manageability and worth pulling out separately. Manageability is the ease (or otherwise) with which the technical elements of a system in operation can be monitored for health and controlled. For clarity compare these problematic examples…..

  • You only know a system is down when the users shout at you
  • You only know the storage is full when you can’t write to it any more
  • You have to access the server room to restart the machine
  • You have to be able to use vi over an SSH connection to check the logs
  • And you have to do all the above in subtly different way for every system you manage!

…..with these more positive examples….

  • You are able to use a central management system to ‘see’ all (or some) of your systems
  • You get notices and alerts as thresholds (e.g. storage, memory, processor) are approached
  • You can remotely manage systems and create new virtual instances at the ‘click of a button’
  • You get a rich graphical overview of key system metrics

There is also a strong counterpoint for the people elements (think about HR practices, reporting and communication). Manageability is very important for charity and non-profit organisations running systems in-house since investment upfront can free up staff time for more important duties and present a more proactive system support face to users.


Availability is the probability that a system is operating as intended when it is called upon. It is often expressed as a decimal percentage format. For example with a system known to have 75% availability, for every four times you use it, it will be down or not working properly once. Most technical systems aim for 99% or better and the discussion comes down to the number of 9s after the decimal place (e.g. 99.999% is ‘better’ than 99.99%). There is a subtle distinction between availability and reliability. Although you may hear these terms used interchangeably I have disambiguated them within this catalogue. Specifically availability also includes the time take to fix failures (maintainability) – whereas reliability does not. See the reliability section for a more detailed discussion on the relationship between availability, reliability and maintainability.


Maintainability expresses how quick/easy/cheap a system element (could be people, could be technology) is to repair or replace in the event of a failure. Within this catalogue maintainability is discussed with the context of reliability and availability since there is a three way relationship between these requirements.


You should read availability first since this entry aims to disambiguate between the two terms:

  • Availability is the probability that a system is operating as intended when it is called upon and is normally expressed as a percentage. For example a system with 99% availability will be down once for every hundred times it is used
  • Reliability is also the probability a system is operating as intended when it is called upon but makes no account of repair work that may be needed

For example a thatch roof on a building may have a very high reliability – perhaps operating for 20 years without a single failure. However, the effort required to repair it (maintainability) is substantial. Thus, since availability must also take account of repair time, the availability will be lower than the reliability. Compare this with a redundant hot-swappable disk array (or a team of stand-in actors in a troupe) – the repair time is close to zero (high maintainability) and thus the availability figure is very close to the reliability figure. Charity and non-profit organisations should look closely at the service level agreements offered by vendors and the cross-over of skills between people. It is a common mistake to quote the reliability of a system element whilst being unaware that (in the event of a failure) the time to fix could be large. Leading to the situation in which a highly reliable system has quite poor availability. The most important point when considering availability is to understand what the uptime of the system needs to be and then design to that. Remember we are talking about a rich system (people as well as technology here). For example the uptime of a help desk service is as only as high as the hours the phones are staffed but, when those people are at the desk, they cannot cope with a technical system which is down half the time. Thus different elements of a system will have different availability – but the net effect of combining them is to give an overall availability which meets the organisations requirements. Availability is important for a range of charity and non-profit organisations. A few examples would be those who have time-critical/short window tasks (e.g. Direct Debit processing), those with huge load at peak times (e.g telethons), or those with critical care issues.




Useful Scalability Books

The Art of Scalability Book CoverOver the holidays I’ve been re-reading ‘The Art of Scalability Scalable Web Architecture, Processes and Organizations for the Modern Enterprise’ by Martin Abott and Michael Fisher. I’d forgotten what a great book this is and thought it was worth a bit of a plug. If you looking for deep tech stuff it’s the wrong book for you – but if you want a refresher or some ideas on some great consultancy tricks and techniques for getting architecture improvements off the ground it’s a cracker. These guys boiled a bunch of wisdom down and there’s something for even the most experienced of us to be found within. It really is aimed at leadership and management folks rather than those propping up the coal-face of a creaking architecture. But I think architecture matters, and matter at every level – so go on techies…give it a whirl too – worst thing that happens is you get an insight into how your boss’s brain might work.

If all the management talk makes you feel a bit fluffy, afterwards you can always take a deep, refreshing plunge into pure techy scalability discussions…..treat yourself to one of my favourite books: Theo Schlossnagle: ‘Scalable Internet Architectures’


Five SQL Tips

In the past I’ve often been happy to use GUI tools to write queries/reports or (as a developer) to use object-relational mapping layers to handle SQL generation from my OO code. Recently however, for a client with some pretty complex reporting requirements, I found myself needing to get down to brass tacks and write large chunks of SQL by hand. I was pretty surprised at what a powerful language (although uncomfy for an OO programmer like me) I found it to be and, having spent a bunch of time crafting SQL by hand, I think I would now find it quicker to just write the SQL rather than drag, drop, scroll, select, drag, drop…..

I was always pretty familiar with all the standard SQL stuff (selecting, joining, filtering, views etc) but along the way I learnt a couple of new, powerful SQL techniques – and I thought I’d share them here with an example of each

Tip 1: Get a really good SQL book.
If you’re going to go deep under the covers of SQL you’ll need a guide. I found the O’Reilly SQL Cookbook did the trick for me – a very useful reference indeed. I’ll use SQL Server for the examples in this post – but if you’d like to see how to phrase the same techniques in Oracle, MySQL, DB2 then check out the book

Tip 2: Creating and populating a table variable
Based upon a user selected parameter one or more (often a lot more) appeal codes would be selected which would then drive the transactions selected. Using a table variable to store the appeal codes made life much easier. Here’s how it works:

  • Create the table variable

DECLARE @AppealMailingsTableVar TABLE(ID uniqueidentifier, NAME varchar(max))

  • Populate the table with values. I had a number of conditional choices here – but one example was:

IF left(@appealTreeID,16) = ‘APPEAL_CATEGORY:’
  INSERT INTO @AppealMailingsTableVar (ID, NAME)

  • Use the populated table variable in your SQL as part of a join (note that you must alias it)


  • Or, use the populated table variable in your SQL as part of a where clause (note that you must alias it)


Tip 3: Using OVER and ROW NUMBER to partition and then rank results
As part of a view within a much larger bunch of SQL I needed to do the following: ‘for each constituent, find the most recent regular giving scheme starting before a certain appeal went out’:

    REVENUE.AMOUNT as originalPaymentAmount,
    REVENUE.DATE as originalRecurringGiftStartDate,
    row_number() over (partition by REVENUE.CONSTITUENTID order by REVENUE.DATE desc) as giftStartDateRank
  giftStartDateRank = 1

Tip 4: Decision logic using CASE
The CASE construct is great for formatting results or selecting output based on other results values. Can’t believe I’d never bumped into this one before! Here’s a simple example that looks at the values in other fields to categorise the result row:

  WHEN amount12Month – preUpgrade12Month < 0 then ‘Downgrade’
  WHEN amount12Month – preUpgrade12Month > 0 then ‘Upgrade’
  WHEN amount12Month – preUpgrade12Month = 0 then ‘No Reponse’
END AS upOrDown,

Tip 5: Using CASE within SUM to decide what gets included in a total
I wanted to sum the results in several ways – but only include some of the results (different conditions for each summed field):

  SUM(paymentAmount) as amount,
  SUM(originalPaymentAmount) as preUpgradeAmount,
  SUM(CASE WHEN datediff(month,FIRST_NEW_PAYMENT.firstPaymentDate,paymentDate) <=12 THEN paymentAmount ELSE 0 END) as amount12Month,
  SUM(CASE WHEN datediff(month,FIRST_NEW_PAYMENT.firstPaymentDate,paymentDate) <=12 THEN originalPaymentAmount ELSE 0 END) as preUpgrade12Month,
  SUM(CASE WHEN datepart(year,FIRST_NEW_PAYMENT.firstPaymentDate) = datepart(year,paymentDate) THEN paymentAmount ELSE 0 END) as amountEndCurrentYear,
  SUM(CASE WHEN datepart(year,FIRST_NEW_PAYMENT.firstPaymentDate) = datepart(year,paymentDate) THEN originalPaymentAmount ELSE 0 END) as preUpgradeEndCurrentYear