Issue in data fetching from DB using Ignition Named query

General Discussion
1 
DECLARE @StartDate DATE = :dd
DECLARE @endDate DATE = :dd1

DECLARE @StartTime1 TIME = '06:15:00';
DECLARE @EndTime1 TIME = '14:46:00';

DECLARE @StartTime2 TIME = '14:45:00';
DECLARE @EndTime2 TIME = '23:16:00';

DECLARE @StartTime3 TIME = '23:15:00';
DECLARE @EndTime3 TIME = '06:16:00';

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

SELECT
    ds.CurrentDate,
    COALESCE(MAX(CONVERT(FLOAT, KWH)) - MIN(CONVERT(FLOAT, KWH)), 0) AS Diff
FROM DateSequence ds
LEFT JOIN Main m ON ds.CurrentDate = CAST(m.Date AS DATE)
WHERE BG = :bg
    AND Plant = :pl
    AND Line = :ll
    AND Machine = :ma
    AND 
        (
            (
                m.Date >= CAST(ds.CurrentDate AS DATETIME) + CAST(@StartTime3 AS DATETIME) AND m.Date <= CAST(ds.CurrentDate AS DATETIME) + CAST('23:59:59' AS DATETIME)
            )
            OR
            (
                m.Date >= CAST(DATEADD(DAY, 1, ds.CurrentDate) AS DATETIME) AND m.Date <= CAST(DATEADD(DAY, 1, ds.CurrentDate) AS DATETIME) + CAST(@EndTime3 AS DATETIME)
            )
        )
    
GROUP BY ds.CurrentDate
ORDER BY ds.CurrentDate;

while fetching the data from main Table using above query the data is computing till start time 3 i.e., till 11:59:00pm of the current day...the data from 12:00:AM to 06:15 AM end time 3 data is not fetching correctly and it got missed in the calculation what modification we need to do in the where condition

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!