Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
INSERT INTO CaseHistory (Region, EmployeeID, AsOfDate, Closed, Pending, Active, LateReport, AsOfWeek, EmpWk, YearWk) -- All cases by region and employee when there is no BudgetID select -- Region set to 'TBD' during insert, to be updated after INSERT 'TBD', -- CM (either the employee ID or the budget ID if a substitute is filling in on the case) case when (proj.budgetid is not null and exists (select 'X' from acm where proj.budgetid = acm.empid)) then proj.budgetid else ACM.EmpID end as CaseMgr, -- Record time stamp of the previous week's Sunday for AsOfDate GetDate()-(datepart("weekday",GetDate())+6) as AsOfDate, /*assume job runs on Sunday for previous week*/ -- Accumulate case counts for closed, pending, active, late status sum (case when proj.projectother3 like 'clos%' then 1 else 0 end) Closed, sum (case when proj.projectother3 like 'pend%' then 1 else 0 end) Pending, sum (case when left(proj.projectother3,4) not in ('clos','pend') then 1 else 0 end) Active, sum (case when left(proj.projectother3,1) in ('0','1') then case when left(proj.projectother3,5) < Left(convert(varchar,Getdate()-4,10),5) /*compare to 4 days earlier to determine late, gives a grace period to allow for reports that weren't processed until Mon/Tue for previous week */ then 1 else 0 end else 0 end) LateReport, -- Store week as a value (NOTE: this may not be needed for reporting since YearWk contains this data) datepart("week",GetDate() -(datepart("weekday",GetDate())) ) as AsOfWeek, /*Using the week number of the beginning of the previous week since the job runs on Sunday of following week */ -- Build Primary Key field using the employeeID and YearWk fields concatenated (case when proj.budgetid is null then ACM.EmpID else proj.budgetid end)+ right(str(datepart("year",GetDate()-(datepart("weekday",GetDate()))),4),2) + '-' + left(convert(char,datepart("week",getdate() -(datepart("weekday",GetDate())) ),1),2) as EmpWk, -- Build YearWk to create unique time records for each week of each year, not just the week) right(str(datepart("year",GetDate()-(datepart("weekday",GetDate()))),4),2) + '-' + left(convert(char,datepart("week",getdate() -(datepart("weekday",GetDate())) ),1),2) as YearWk FROM ACM left outer join proj on ACM.EmpID = proj.employeeid WHERE proj.projectstatus = 'Active' and proj.budgetid is null GROUP BY /*ACM.Region, */ACM.EmpID, proj.budgetid ORDER BY /*ACM.Region, */ACM.EmpID
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
18 Experts available now in Live!