sql

DECLARE

@startDate DATE,

@endDate DATE



DECLARE @resultsTable TABLE (

       Wono INT,

       EquipmentCount INT,

       PersonnelCount INT,

       PMPEquipmentCount INT,

       PMPPersonnelCount INT,

       EquipmentDifference INT,

       PersonnelDifference INT

)



DECLARE @personnelTable TABLE (

       Wono INT,

       PersonnelCount INT

)



DECLARE @pmpTable TABLE (

       WorkOrderNo INT,

       EquipmentCount INT,

       PersonnelCount INT

)



DECLARE @pmpPersonnel TABLE (

       WorkorderNo INT,

       personnelcount INT

)



SET @startDate = '4/18/2018'

SET @endDate = '4/30/2018'



INSERT INTO @resultsTable (Wono, EquipmentCount)

       SELECT

             Wono,

             COUNT(wono) AS EquipmentCount

       FROM (SELECT

            Wono,

             iiae.[InvoiceItemActualEquipmentId] AS InvoiceItemEquipmentId,

             iiae.[TaskItemActualEquipmentId] AS TaskItemEquipmentId

       FROM [FDSS_Prod].[dbo].[InvoiceItemActualEquipment] iiae

       INNER JOIN [dbo].[TaskItemActualEquipment] tiae

             ON tiae.TaskItemActualEquipmentId = iiae.TaskItemActualEquipmentId

       INNER JOIN [dbo].[TaskItemPlanEquipment] tipe

             ON tipe.TaskItemPlanEquipmentId = tiae.TaskItemPlanEquipmentId

       INNER JOIN [dbo].[TaskItem] ti

             ON ti.TaskItemId = tipe.TaskItemId

       CROSS APPLY (SELECT TOP 1

             *

       FROM TaskItemWorkOrderLink

       WHERE TaskItemId = ti.TaskItemId

       AND isactive = 1

       ORDER BY updatetimestamp DESC) tiwo

       WHERE CONVERT(DATE, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), ti.TASkItemActualStartTime)) BETWEEN @startDate AND @endDate

       UNION

       SELECT

             Wono,

             iiae.[InvoiceItemActualOtherEquipmentId] AS InvoiceItemEquipmentId,

             iiae.[TaskItemActualOtherEquipmentId] AS TaskItemEquipmentId

       FROM [FDSS_Prod].[dbo].[InvoiceItemActualOtherEquipment] iiae

       INNER JOIN [dbo].[TaskItemActualOtherEquipment] tiae

             ON tiae.TaskItemActualOtherEquipmentId = iiae.TaskItemActualOtherEquipmentId

       INNER JOIN [dbo].[TaskItem] ti

             ON ti.TaskItemId = tiae.TaskItemId

       CROSS APPLY (SELECT TOP 1

             *

       FROM TaskItemWorkOrderLink

       WHERE TaskItemId = ti.TaskItemId

       AND isactive = 1

       ORDER BY updatetimestamp DESC) tiwo

       WHERE CONVERT(DATE, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), ti.TASkItemActualStartTime)) BETWEEN @startDate AND @endDate) AS Equipment

       GROUP BY Wono

       ORDER BY Wono



INSERT INTO @personnelTable (wono, PersonnelCount)

       SELECT

             Wono,

             COUNT(wono) AS PersonnelCount

       FROM (SELECT

             Wono,

             iiap.[InvoiceItemActualPersonnelId] AS InvoiceItemPersonnelId,

             iiap.[TaskItemActualPersonnelId] AS TaskItemPersonnelId

       FROM [FDSS_Prod].[dbo].[InvoiceItemActualPersonnel] iiap

       INNER JOIN [dbo].[TaskItemActualPersonnel] tiap

             ON tiap.TaskItemActualPersonnelId = iiap.TaskItemActualPersonnelId

       INNER JOIN [dbo].[TaskItemPlanPersonnel] tipe

             ON tipe.TaskItemPlanPersonnelId = tiap.TaskItemPlanPersonnelId

       INNER JOIN [dbo].[TaskItem] ti

             ON ti.TaskItemId = tipe.TaskItemId

       CROSS APPLY (SELECT TOP 1

             *

       FROM TaskItemWorkOrderLink

       WHERE TaskItemId = ti.TaskItemId

       AND isactive = 1

       ORDER BY updatetimestamp DESC) tiwo

       WHERE CONVERT(DATE, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), ti.TASkItemActualStartTime)) BETWEEN @startDate AND @endDate

       UNION

       SELECT

             Wono,

             iiap.[InvoiceItemActualOtherPersonnelId] AS InvoiceItemPersonnelId,

             iiap.[TaskItemActualOtherPersonnelId] AS TaskItemPersonnelId

       FROM [FDSS_Prod].[dbo].[InvoiceItemActualOtherPersonnel] iiap

       INNER JOIN [dbo].[TaskItemActualOtherPersonnel] tiap

             ON tiap.TaskItemActualOtherPersonnelId = iiap.TaskItemActualOtherPersonnelId

      INNER JOIN [dbo].[TaskItem] ti

             ON ti.TaskItemId = tiap.TaskItemId

       CROSS APPLY (SELECT TOP 1

             *

       FROM TaskItemWorkOrderLink

       WHERE TaskItemId = ti.TaskItemId

       AND isactive = 1

       ORDER BY updatetimestamp DESC) tiwo

       WHERE CONVERT(DATE, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), ti.TASkItemActualStartTime)) BETWEEN @startDate AND @endDate) AS Personnel

       GROUP BY Wono

       ORDER BY Wono



INSERT INTO @resultsTable (wono, PersonnelCount)

       SELECT

             p.wono,

             p.personnelcount

       FROM @personnelTable p

       LEFT OUTER JOIN @resultsTable r

             ON r.Wono = p.wono

       WHERE r.wono IS NULL



UPDATE r

SET r.PersonnelCount = p.PersonnelCount

FROM @resultsTable r

INNER JOIN @personnelTable p

       ON r.Wono = p.Wono



INSERT INTO @pmpTable (WorkOrderNo, EquipmentCount)

       SELECT

             WorkOrderNo,

             COUNT(workorderno) AS EquipmentCount

       FROM [PMP2007].dbo.WOEquipment woe

       INNER JOIN [pmp2007].[dbo].[WorkOrder] wo

             ON wo.WorkOrderNo = woe.EquipWONo

       INNER JOIN @resultsTable rt

             ON rt.wono = wo.workorderno

       WHERE CONVERT(DATE, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), wo.WOActualWODate)) BETWEEN @startDate AND @endDate

       GROUP BY WorkOrderNo



INSERT INTO @pmpPersonnel (WorkorderNo, Personnelcount)

       SELECT

             Workorderno,

             COUNT(workorderno) AS PersonnelCount

       FROM [PMP2007].[dbo].[WOEmployees] woe

       INNER JOIN [PMP2007].[dbo].WorkOrder wo

             ON wo.WorkOrderNo = woe.empwono

       INNER JOIN @resultstable rt

             ON rt.wono = wo.workorderno

       WHERE CONVERT(DATE, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), wo.WOActualWODate)) BETWEEN @startDate AND @endDate

       GROUP BY WorkOrderNo



INSERT INTO @pmpTable (WorkOrderNo, PersonnelCount)

       SELECT

             p.workorderno,

             p.personnelcount

       FROM @pmpPersonnel p

       LEFT OUTER JOIN @pmpTable r

             ON r.WorkOrderNo = p.WorkorderNo

       WHERE r.WorkOrderNo IS NULL



UPDATE r

SET r.PersonnelCount = p.PersonnelCount

FROM @pmpTable r

INNER JOIN @pmppersonnel p

       ON r.WorkOrderNo = p.WorkOrderNo



UPDATE r

SET r.pmpequipmentcount = p.equipmentcount

FROM @resultsTable r

INNER JOIN @pmpTable p

       ON r.Wono = p.WorkOrderNo



UPDATE r

SET r.PMPPersonnelCount = p.PersonnelCount

FROM @resultsTable r

INNER JOIN @pmpTable p

       ON r.Wono = p.WorkOrderNo



UPDATE @resultsTable

SET EquipmentDifference = PMPEquipmentCount - EquipmentCount,

PersonnelDifference = PMPPersonnelCount - PersonnelCount



SELECT

       Wono AS WorkOrderNumber,

       ISNULL(EquipmentCount, 0) AS NeptuneEquipmentCount,

       ISNULL(PersonnelCount, 0) AS NeptunePersonnelCount,

       ISNULL(PMPEquipmentCount, 0) AS PMPEquipmentCount,

       ISNULL(PMPPersonnelCount, 0) AS PMPPersonnelCount,

       ISNULL(EquipmentDifference, 0) AS EquipmentDifference,

       ISNULL(PersonnelDifference, 0) AS PersonnelDifference

FROM @resultsTable

WHERE EquipmentDifference != 0 OR PersonnelDifference != 0

ORDER BY Wono ASC