Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails
SELECT * FROM ijob WHERE ijob_id IN ( SELECT ijobelement.ijob_id FROM ijobelement LEFT JOIN ijob T1 ON ijobelement.ijob_id = ijob.ijob_id LEFT JOIN ijobtype T2 ON T1.ijobtype_id = T2.ijobtype_id WHERE -- Do not include jobs which have been invoiced in the old system ijob.iinvoice_id is null AND -- Do not include jobs with non-billable job types (T2.ijobtype_name <> 'Meeting' AND T2.ijobtype_name <> 'Other' AND T2.ijobtype_name <> 'Training' AND T2.ijobtype_name <> 'Sign Making') AND -- This is a billable job type but we now check to see if it is eligible to be billed -- Startup job items and not billed ((ijobelement_type = 0 AND ijobelement_billdate is null) OR -- ONE-OFF JOB items which have not been billed and we are > 7 days from their start date (ijobelement_type = 1 AND ijobelement_billdate is null AND ((DATEDIFF(d, ijobelement_startdate, GETDATE())) >= 7)) OR -- RECURRING JOB items which have NOT been invoiced and we are >= 7 Days from the start date (ijobelement_type = 2 AND ((ijobelement_billdate is null AND ((DATEDIFF(d, ijobelement_startdate, GETDATE())) >= 7)) OR -- RECURRING JOB which is NOT COMPLETE and has not been invoiced in the last 7 days (ijobelement_billdate is not null AND ((ijob.ijob_complete = 'N' AND ((DATEDIFF(d, ijobelement_billdate, GETDATE())) >= 7)) OR -- RECURRING JOB which IS COMPLETE and its last invoice date is before the job completed date (ijob.ijob_complete = 'Y' AND (ijobelement_billdate < ijob.ijob_completed_date))) ) ) ) OR -- Closedown task which is not billed and the parent job is complete (ijobelement_type = 3 AND ijobelement_billdate is null AND ijob.ijob_complete = 'Y')) )
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
19 Experts available now in Live!