-
Goal: <!--- My app was working fine for 6 weeks up until 4/17 11am PST. Goal is to upsert data into a table from an array
-
Steps: <!--- Verified table schema didn't change and can properly import the data, field names are identical for proper mapping. Preview displays the data up won't insert into table.
-
what have you tried so far? specific actions we can take to reproduce the problem/where you're stuck? links to documentation you've consulted? -->
-
Details: <!--- The query it query of the array its upserting
-
Select * , (((HoursToFinish *
(CASE
WHEN [Hours to Date] = 0
THEN (Cost_budget) / (hours_budget)
ELSE (CostToDate) / ([Hours to Date])
END)) + CostToDate)) AS ProjectedFinalCost,
((TPHoursEOM *
(CASE
WHEN [Hours to Date] = 0 THEN (Cost_budget) / (hours_budget)
ELSE (CostToDate) / ([Hours to Date])
END)- CostToDate
)) AS ProjectedEOMCost
, CONCAT(Jobnum, '', Phase_Cat, '', SUBSTRING(CONVERT(VARCHAR, GETDATE(), 112), 5, 2)) as ID,
null as notes
- CostToDate
from
(
SELECT
Concat(a.phasenum,'.',a.catnum) as 'Phase_Cat',
a.catnum,
a.phasenum,
b.name as Phasename,
c.name as CatName,
SUM(a.[cost]) AS Costtodate,
a.cost_budget,
a.hours_budget,
a.hours as 'Hours to Date',
Round(Case
when b.name like '%indirect%' and a.hours > a.hours_budget then 0
when a.hours > hours_budget and tp.tphours is null then 0
when a.hours = 0 then hours_budget
when a.hours > 0 and tp.tphours is null then hours_budget - a.hours
when a.hours > 0 then a.hours + tp.tphours
--When a.hours - hours_budget < 0 and TPEOM.TPhoursEOM is null then hours_budget - a.hours
when tp.tphours is null and b.name like '%indirect%' then hours_budget - a.hours
--when tp.tphours is null then 0
else tp.tphours
end ,2) as HoursToFinish,
ROUND(
(
a.hours +
CASE
WHEN b.name LIKE '%indirect%' AND a.hours > a.hours_budget THEN 0
WHEN tp.tphours IS NULL AND b.name LIKE '%indirect%' THEN a.hours_budget - a.hours
WHEN tp.tphours IS NULL THEN 0
ELSE tp.tphours
END
) - a.hours_budget,
2
) AS Over_UnderHours
,
case when TPEOM.TPhoursEOM is null then 0
else TPEOM.TPhoursEOM
end as TPhoursEOM,
a.jobnum
FROM ComputerEase.query_jcpcnt a
LEFT JOIN ComputerEase.jcphase b
ON a.jobnum = b.jobnum
AND a.phasenum = b.phasenum
LEFT JOIN ComputerEase.jccat c
ON a.jobnum = c.jobnum
AND a.phasenum = c.phasenum
AND a.catnum = c.catnum
Left join (SELECT
[JobNumber]
,[Phase]
,[Cat]
,Sum([laborhours]) as TPhours
FROM [dbo].[TPLaborHoursIndividualDays]
where workingdate >= (GETDATE())
group by jobnumber, phase,cat) TP on a.jobnum = tp.jobnumber
and a.phasenum = tp.phase and a.catnum = tp.cat
Left join (SELECT
[JobNumber]
,[Phase]
,[Cat]
,Sum([laborhours]) as TPhoursEOM
FROM [dbo].[TPLaborHoursIndividualDays]
where workingdate between getdate() and Eomonth(getdate())
group by jobnumber, phase,cat) TPEOM on a.jobnum = TPEOM.jobnumber
and a.phasenum = TPEOM.phase and a.catnum = TPEOM.cat
WHERE a.jobnum = {{ active_job_table.selectedRow.jobnum }}
AND a.catnum IS NOT NULL
AND a.hours_budget > 0
GROUP BY a.catnum, a.hours, tphours, TPEOM.TPhoursEOM, a.hours_budget, a.phasenum, b.name, c.name, a.cost_budget, a.jobnum
)as sub1
ORDER BY jobnum,phasenum, catnum
- Screenshots:
Database Schema
- App json export:Because I am a new user so it won't let me attach the json app file...