Need to update the query for Fiscal year weekly Report for different time of Shift

Ignition
1

Hello Team,
We have a SQL query in place for the subject report. The query is set to grab the data from each shift (morning,afternoon,night). Each shift is for 8 hours. Now one of our line is running 10 hours afternoon shift and 7 hours night shift instead of 8 hours. Our query is grouped by 8 hours shift. I am trying to edit the query but I am not getting accurate the data for afternoon and night shift. If someone has the idea on this will really appreciate.

2

Please edit your post to remove the leading indent. That causes a quotation format that put everything on one line. I'm not going to scroll sideways through your whole question.

3

And, perhaps, share your query and what you have tried.

4

Sorry for that. I changed it

5

@pturmel , Can you see whole topic now?

6

Yes, but I have nothing to add to what Jordan requested. We cannot read your mind or see your screen or know what you've already tried.

When answering Jordan, be verbose.

1 Like
7 
select Uptime, Downtime, Stops, Efficiency, Totaltime, concat (Date,'-', case when Shift = 'Morning' then substring (shift, 1,3) when Shift = 'Afternoon' then substring (shift, 1,3) else substring(shift, 1,5) end ) as Date, tstamp, row_number() over (order by (select null)) as indx,shift, Date as date1
from
(select case when Production_Day = 1 then Uptime end as Uptime,
case when Production_Day = 1 then Downtime end as  Downtime,
case when Production_Day = 1 then Stops end as  Stops,
case when Production_Day = 1 then Efficiency end as Efficiency,
case when Production_Day = 1 then Totaltime end as Totaltime,
case when Production_Day = 1 then Date  end as Date,
case when Production_Day = 1 then tstamp  end as tstamp,Shift
from
(select  cast(sum(case when Production_Day = 1 then (Bread_1hrUpTime_Minutes_History) end) as varchar(254)) as Uptime,
cast(sum(case when Production_Day = 1 then (Bread_1hrDownTime_Minutes_History) end) as varchar(254)) as Downtime,
cast(sum(case when Production_Day = 1 then (Bread_StoppageHistory) end) as varchar(254)) as Stops,
SUM(Bread_1hrTotalTime_Minutes_History) as Totaltime,
cast(Avg(case when Production_Day = 1 then (Bread_1hrEfficiency_Percentage_History) end) as varchar(254)) as Efficiency
, MAX(Current_Period) as Period , Max(Shift) as Shift, max(case when Production_Day = 1 then 1 else 0 end) as Production_Day , format((min (t_stamp)), 'MMM dd') as Date, min(t_stamp) as tstamp
FROM Fiscal_Data_Bread
WHERE Fiscal_Year = :Fiscal_Year  and Current_Quarter = :Current_Quarter and Current_Period = :Current_Period and Current_Week = :Current_Week
GROUP BY DATEADD(hh, ((DATEDIFF(hh, 0, t_stamp)+2)/10*10)-2, 0)
) t
)m
where Uptime is not null