CREATE VIEW vmMaintActivity_6Months AS

SELECT  Emp.EmpID,
        Emp.LastName,
        Emp.FirstName,
        WOA.OrderID,
        MO.OrderType,
        WOA.Comments,
        cast(WOA.TransDate as date) as [Date],
        cast(WOA.TransDate as time)[Time],
        Shift_num = case when cast(WOA.TransDate as time) >=  '22:00:00.000' Then 3
                         when cast(WOA.TransDate as time) <=  '06:00:00.000' Then 3
                         when cast(WOA.TransDate as time) BETWEEN '06:00:00.000' and '14:00:00.000' Then 1
                         when cast(WOA.TransDate as time) BETWEEN  '14:00:00.000' and  '22:00:00.000' Then 2
        END,
        Shift_Date = case  when cast(WOA.TransDate as TIME) >=  '22:00:00.000' Then DATEADD(Day, 1, cast(WOA.TransDate as Date))
        ELSE cast(WOA.TransDate as Date)
        END
FROM ((tblWorkOrderActivity as WOA
INNER JOIN tblEmployees as Emp on WOA.EmpID = Emp.EmpID)
INNER JOIN tblMaintOrders AS MO on WOA.OrderID = MO.OrderID)
WHERE MO.OrderType = 'M' AND TransDate >= DATEADD(Month, -6, Cast(GetDate() as Date))