simplify                         select n.id, nt.Name as [Type], n.CreatedBy as FromId, n.CreatedOn as [SentAt], n.ContextId as [ViewId],
                        o.name as OrgName, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0 as BreakMinutes, Comment as Message,
                        null as OrgLocation, null as CurrentLocation,n.ReadAt, n.SentTo, tz.Name as Orgtimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN jobs j on n.ContextId = j.Id
                            INNER JOIN orgs o on j.orgid = o.id
                            INNER JOIN timezones tz on tz.Id=o.TimezoneId
                            INNER JOIN JobComments m on j.id = m.jobid and n.CreatedBy = m.UserId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.Name = 'JOB_COMMENT'
                    UNION
                        -- job related
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as [SentAt], n.ContextId as [ViewId],
                        o.name as OrgName, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, j.WithdrawReason as Message,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimeZone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN jobs j on n.ContextId = j.Id
                            INNER JOIN orgs o on j.orgid = o.id
                            INNER JOIN timezones tz on tz.Id=o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name like 'JOB_%' and nt.name not in ('JOB_COMMENT', 'JOB_START_REMIND') and nt.name not like 'JOB_REQUEST_%'
						and nt.name not like 'JOB_END_DATE_UPDATED_%' and nt.name not like 'JOB_SITE_%'
                    UNION
                        -- clock in/out related
                        select n.id, nt.Name as Type, 
                        case when nt.name = 'CC_CDD_HASNT_CLOCKED_IN' then jc.CandidateId 
                        when nt.name =  'CC_JOB_STARTED_YOU_HAVENT_CLOCKED_IN' then j.CreatedBy else n.createdby end as [FromId],n.CreatedOn as [SentAt], 
                        case when nt.name = 'CC_CDD_HASNT_CLOCKED_IN' then j.Id else n.ContextId end as [ViewId],
                        o.name as OrgName, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, '' as Message, 
                        Cast(a.Location as varbinary(MAX)) as OrgLocation, Cast(ccl.CurrentLocation as varbinary(MAX)) as CurrentLocation,
                        n.ReadAt, n.SentTo, tz.Name as OrgTimeZone
                        from notifications n 
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN JobTimesheets jt on n.ContextId = jt.Id
                            INNER JOIN JobCandidates jc on jc.Id = jt.JobCandidateId
                            INNER JOIN Jobs j on j.Id = jc.JobId
                            INNER JOIN orgs o on j.orgid = o.id
                            INNER JOIN timezones tz on tz.Id = o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                            LEFT JOIN CandidateCurrentLocations ccl on ccl.TimesheetId = n.ContextId
                        where nt.name like 'CC_%'
                    UNION
                        -- reminder before job start
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as [SentAt], n.ContextId as [ViewId],
                        o.name as OrgName, a.Suburb, j.JobTitle as Title, jd.StartTime, jd.EndTime, 0, CAST(j.Id as varchar(10)) as Message,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimeZone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN JobDays jd on n.ContextId = jd.Id
                            INNER JOIN Jobs j on j.Id = jd.JobId
                            INNER JOIN orgs o on j.orgid = o.id
                            INNER JOIN timezones tz on tz.Id=o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.Name = 'JOB_START_REMIND'
                    UNION
                        -- timesheet
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, n.ContextId as [ViewId],
                        OrgName, Suburb, JobTitle, StartTime, EndTime, BreakMinutes, '' as Message, null as OrgLocation, null as CurrentLocation, 
                        n.ReadAt, n.SentTo, t.OrgTimezone
                        from notifications n INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                        INNER JOIN (
                                select t.id as jobtimesheetid, c.jobid, o.name as OrgName, a.Suburb,
                            JobTitle, t.StartTime, t.EndTime, t.BreakMinutes, tz.Name as OrgTimezone
                        from JobTimesheets t
                            INNER JOIN JobCandidates c on t.JobCandidateId = c.Id
                            INNER JOIN jobs j on c.jobid = j.id
                            INNER JOIN orgs o on j.orgid = o.id
                            INNER JOIN timezones tz on tz.Id=o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                            ) t on n.ContextId = t.jobtimesheetid
                        where nt.name like 'TIMESHEET_%'
                    UNION
                        -- fav added
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, 0 as [ViewId],
                        o.name as OrgName, a.Suburb, 'Added as a favourite!' as Title,
                        null, null, 0, '' as Message, null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN orgs o on n.contextId = o.id
                            INNER JOIN timezones tz on tz.Id=o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name = 'FAVOURITE_ADDED'
                    UNION
                        -- expense claim
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, n.ContextId as [ViewId],
                        o.name as OrgName, a.Suburb, 'Expense Claim Notification' as Title, ts.StartTime, ts.EndTime, 0,
                        CONVERT(varchar, CONVERT(DECIMAL(10,2), ec.Quantity * ec.UnitPrice)) as Message, null as OrgLocation, --Message is used as amount here
                        null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN ExpenseClaims ec on n.ContextId = ec.Id
                            INNER JOIN JobTimesheets ts on ec.TimesheetId = ts.Id
                            INNER JOIN JobCandidates jc on ts.JobCandidateId = jc.Id
                            INNER JOIN Jobs j on jc.JobId = j.Id
                            INNER JOIN Orgs o on j.OrgId = o.Id
                            INNER JOIN timezones tz on tz.Id=o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name like 'EXPENSE_CLAIM_%'
                    UNION
                        -- expiring verifications
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, v.Id as [ViewId],
                        s.name as OrgName, a.Suburb, CONCAT('Your ',vt.Name,' is expiring!') as Title,
                        null, null, 0, CONCAT('Your ', vt.Name,' in ', s.Name,' is expiring in ',DATEDIFF(day,GETUTCDATE (), v.DocExpiryDate),
                        case when DATEDIFF(day,GETUTCDATE (), v.DocExpiryDate) > 1 then ' days' else ' day' end, '!') as Message,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN verificationLogs v on v.Id = n.contextId
                            INNER JOIN verificationTypes vt on vt.Id = v.VerificationTypeId
                            INNER JOIN jbusers sndr on n.CreatedBy = sndr.AspNetUserId INNER JOIN orgs o on sndr.orgid = o.id
                            INNER JOIN syndicates s on s.Id=v.syndicateid
                            INNER JOIN JBUsers vc on v.CreatedBy = vc.AspNetUserId
                            INNER JOIN orgs vco on vc.OrgId = vco.Id
                            INNER JOIN timezones tz on tz.Id = vco.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name = 'VERIFICATION_EXPIRING'
                    UNION
                        -- job requests
                        select n.Id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, n.ContextId as [ViewId],
                        o.name as OrgName, a.Suburb, tp.Name as Title,
                        (select min(StartTime) from JobTimesheetRequests where JobRequestId = jr.Id), 
                        (select max(EndTime) from JobTimesheetRequests where JobRequestId = jr.Id), 0, 
                        case when nt.Name = 'JOB_REQUEST_SUBMITTED' then CONCAT('Please confirm this job request from ', sndr.FirstName, ' ', sndr.LastName, '.')
                            when nt.Name = 'JOB_REQUEST_APPROVED' then CONCAT('Your job record at ', o.Name, ' is approved.')
                            when nt.Name = 'JOB_REQUEST_DECLINED' then CONCAT('Your job record at ', o.Name, ' is declined.')
                            end as Message,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN JobRequests jr on jr.Id = n.contextId
                            INNER JOIN JobTimesheetRequests jtr on jtr.JobRequestId = jr.Id
                            INNER JOIN TalentPools tp on tp.Id = jr.TalentPoolId
                            INNER JOIN jbusers sndr on sndr.AspNetUserId = n.CreatedBy
                            INNER JOIN orgs o on o.id = jr.OrgId
                            INNER JOIN timezones tz on tz.Id = o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name like 'JOB_REQUEST_%'
                    UNION
                        -- job extended to org contact
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId],
                        o.name as OrgName, a.Suburb, j.JobTitle as Title,
                        null starttime, j.EndDate as endtime /* newEndDateTime */, 0, STRING_AGG(jc.FirstName, ', ') as Message /* candidatefirstnames */,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN jobs j on n.ContextId = j.Id
                            inner join (select cdd.FirstName, jc.JobId from JobCandidates jc inner join JBUsers cdd on jc.CandidateId = cdd.AspNetUserId where jc.InviteStatusId = 1) jc on j.id = jc.JobId
                            INNER JOIN orgs o on j.OrgId = o.Id
                            INNER JOIN timezones tz on tz.Id = o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name = 'JOB_END_DATE_UPDATED_TO_ORG_CONTACT'
                        group by n.id, nt.name, n.CreatedBy, n.CreatedOn, j.id, o.name, a.Suburb, j.JobTitle, n.ReadAt, n.SentTo, tz.Name, j.EndDate
                    UNION
                        -- job extended to cdd
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId],
                        o.name as OrgName, a.Suburb, j.JobTitle as Title,
                        null starttime, j.EndDate as endtime /* newEndDateTime */, 0, '' as Message,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN jobs j on n.ContextId = j.Id
                            INNER JOIN orgs o on j.OrgId = o.Id
                            INNER JOIN timezones tz on tz.Id = o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id

                        where nt.name = 'JOB_END_DATE_UPDATED_TO_CDD'
                    UNION
                        -- site changed to orgcontact
                        select n.id, nt.Name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId],
                        case when isnull(s.name, '') = '' then o.name else s.name end as OrgName /* sitename */, a.Suburb, j.JobTitle as Title,
                        j.StartDate, j.EndDate, 0, STRING_AGG(jc.FirstName, ', ') as Message /* candidatefirstnames */,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN jobs j on n.ContextId = j.Id left join sites s on j.SiteId = s.id
                            inner join (select cdd.FirstName, jc.JobId from JobCandidates jc inner join JBUsers cdd on jc.CandidateId = cdd.AspNetUserId where jc.InviteStatusId = 1) jc on j.id = jc.JobId
                            INNER JOIN orgs o on j.OrgId = o.Id
                            INNER JOIN timezones tz on tz.Id = o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name = 'JOB_SITE_UPDATED_TO_ORG_CONTACT'
                        group by n.id, nt.name, n.CreatedBy, n.CreatedOn, j.id, o.name, a.Suburb, j.StartDate, j.EndDate, j.JobTitle, s.name, n.ReadAt, n.SentTo, tz.Name, j.EndDate
                    UNION
                        -- site changed to cdd
                        select n.id, nt.name as [Type], n.CreatedBy as [FromId], n.CreatedOn as SentAt, j.Id as [ViewId],
                        case when isnull(s.name, '') = '' then o.name else s.name end as OrgName /* sitename */, a.Suburb, j.JobTitle as Title, j.StartDate, j.EndDate, 0, '' as Message,
                        null as OrgLocation, null as CurrentLocation, n.ReadAt, n.SentTo, tz.Name as OrgTimezone
                        from notifications n
                            INNER JOIN NotificationTypes nt on n.NotificationTypeId = nt.id
                            INNER JOIN jobs j on n.ContextId = j.Id inner join sites s on j.SiteId = s.id
                            INNER JOIN orgs o on j.OrgId = o.Id
                            INNER JOIN timezones tz on tz.Id = o.TimezoneId
                            INNER JOIN Addresses a ON o.AddressId = a.Id
                        where nt.name = 'JOB_SITE_UPDATED_TO_CDD'