Issue in data fetching from DB using Ignition Named query

General Discussion
2

I suspect that the problem is in here:

WITH DateSequence AS (
    SELECT @StartDate AS CurrentDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CurrentDate)
    FROM DateSequence
    WHERE DATEADD(DAY, 1, CurrentDate) <= @endDate
)

The second date isn't getting added in correctly because you did not specify a column name. This should be:

WITH DateSequence AS (
    SELECT @StartDate AS CurrentDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CurrentDate) AS CurrentDate
    FROM DateSequence
    WHERE DATEADD(DAY, 1, CurrentDate) <= @endDate
)

You can expect poor performance from this query because your Main table is storing dates as strings. That means the query needs to check every record in the table and CAST AS DATETIME before a comparison can be done. The query can't take advantage of a column index which would speed up the query a huge amount. Expect this to get worse as you collect more and more data.

This was pointed out to you by Phil Turmel in your previous question a couple of months ago, Suggestion required in Datetime configuration in Named Queries - #2 by pturmel. You should really fix your data table columns to use DATETIME.

If you can't do that then you could add a new TimeStamp column and a "trigger on insert" to cast your CurrentDate column into the TimeStamp column. You would also create an index on TimeStamp.

You are also still using overlapping time ranges in your shift start and end times. This may cause you problems in future.

Tip: I understand that English may not be your first language, but please try to use capital letters at the start of sentences and punctuate sentences properly so we know where they start and end. You've done it in your SQL!