Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

simplify this sql select n.id, nt.Name as [Type], n.CreatedBy as FromId, n.CreatedOn as…

  1. User

    simplify this sql

                        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'
    
  2. Assistant

    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, CASE WHEN nt.Name = 'JOB_COMMENT' THEN Comment WHEN 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_%' THEN j.WithdrawReason WHEN nt.Name LIKE 'CC_%' THEN '' WHEN nt.Name = 'JOB_START_REMIND' THEN CAST(jd.Id AS varchar(10)) WHEN nt.Name LIKE 'TIMESHEET_%' THEN '' WHEN nt.Name = 'FAVOURITE_ADDED' THEN 'Added as a favourite!' WHEN nt.Name LIKE 'EXPENSE_CLAIM_%' THEN CAST(CONVERT(DECIMAL(10,2), ec.Quantity * ec.UnitPrice) AS varchar) WHEN nt.Name = 'VERIFICATION_EXPIRING' THEN CONCAT('Your ',vt.Name,' is expiring in ', DATEDIFF(day, GETUTCDATE (), v.DocExpiryDate), CASE WHEN DATEDIFF(day, GETUTCDATE (), v.DocExpiryDate) > 1 THEN ' days' ELSE ' day' END, '!') WHEN nt.Name LIKE 'JOB_REQUEST_%' THEN 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 WHEN nt.Name = 'JOB_END_DATE_UPDATED_TO_ORG_CONTACT' THEN STRING_AGG(jc.FirstName, ', ') WHEN nt.Name = 'JOB_END_DATE_UPDATED_TO_CDD' THEN '' WHEN nt.Name = 'JOB_SITE_UPDATED_TO_ORG_CONTACT' THEN STRING_AGG(jc.FirstName, ', ') WHEN nt.Name = 'JOB_SITE_UPDATED_TO_CDD' THEN '' 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 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 LEFT JOIN JobComments m ON j.id = m.jobid AND n.CreatedBy = m.UserId LEFT JOIN ExpenseClaims ec ON n.ContextId = ec.Id LEFT JOIN JobTimesheets jt ON n.ContextId = jt.Id LEFT JOIN JobCandidates jc ON jc