Suggestion required in Datetime configuration in Named Queries

General Discussion
, ,
1

Select
Case
when (CAST (getdate() as time )) >='06:30:00.000' and (CAST (getdate() as time )) <='14:30:00.000' THEN 'Shift1'
when (CAST (getdate() as time )) >='14:30:00.000' and (CAST (getdate() as time )) <='22:30:00.000' THEN 'Shift2'
Else 'Shift3' END as Shift

In this query im getting an result as
shift 1 starts at 6:30AM and ends at14:25 PM in shift 1
shift 2 starts at 2:30 PM and end at 10:25PM in shift 2

but i expect the result as
shift 1 starts at 6:30AM and ends at14: 30 PM in shift 1
shift 2 starts at 2:30 PM and end at 10:30PM in shift 2

2

  • Any given row timestamp can only be assigned to a single shift. In a CASE statement like shown, the first match wins.

  • You are using <= for your shift ending times. That is likely incorrect, as a row exactly on a boundary matches two conditions. You should use half-open intervals.

Side note: This query will run very, very slowly when you get a bunch of rows, because you are converting your timestamps to strings to do your comparisons. This prevents efficient use of an index on your timestamps. Instead, convert your constant strings, with dates, to true timestamps, and compare as timestamps.

1 Like
3

what exact modification i need to do in my query

4

Ah, wait, you aren't comparing the row timestamps, you are using the date/time of execution to decide what shift it is, without regard to the data. ):

1 Like
5

I understood, But i dont know what exactly need to change in the given Query. you can give suggestion to get the data points in all shifts

Below is the Query I am using it for shiftwise split what possible modifications i can do in my query to get a shiftwise energy
select events,total from(
SELECT top(3) cast(Mod_Date as date) as time
,CASE WHEN (Shift='Shift1' ) THEN
(max(convert(float,KWH))-min(convert(float,KWH)))
END AS Shift1
,CASE WHEN (Shift='Shift2') THEN
(max(convert(float,KWH))-min(convert(float,KWH)))
END AS Shift2
,CASE WHEN (Shift='Shift3') THEN
(max(convert(float,KWH))-min(convert(float,KWH)))
END AS Shift3
from Main where Mod_Date between :st and :sp and BG=:bg and Plant=:pl and Line=:ln and Machine =:ma and KWH !='0'
GROUP BY cast(Mod_Date as date),Shift
order by time asc
) as p
UNPIVOT
(total for events IN
(Shift1,Shift2,Shift3 )
)AS unpvt;

1 Like