Suggestion Required in Name Query - Multiple Date Range

Ignition
1 
DECLARE @StartDate DATE = :dd

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';

SELECT (MAX(CONVERT(FLOAT, KWH)) - MIN(CONVERT(FLOAT, KWH))) AS Diff
FROM Main
WHERE BG = :bg
    AND Plant = :pl
    AND Line = :ll
    AND Machine = :ma
    AND KWH !='0'
    AND (
        (Date BETWEEN DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @StartTime1), CAST(@StartDate AS DATETIME)) AND DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @EndTime1), CAST(@StartDate AS DATETIME)))
        OR
        (Date BETWEEN DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @StartTime2), CAST(@StartDate AS DATETIME)) AND DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @EndTime2), CAST(@StartDate AS DATETIME)))
        OR
        (Date BETWEEN DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @StartTime3), CAST(@StartDate AS DATETIME)) AND DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', @EndTime3), CAST(DATEADD(DAY, 1, @StartDate) AS DATETIME)))
    );

With this query I can able to fetch data for single date with the declared time ranges. What do I need to add if I want to fetch data for multiple dates I need to include the date range condition in the query.. If it is not possible to add within the query.. kindly give suggestion to handle this in property binding to add one more date parameter in the query

2

It's not very clear what you're asking, but it seems like you want to parameterize your query ?

If that's the case, I suggest you read this:
https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters

3

Your question is unclear. Do you mean a date range [e.g., 5th to 9th] or a list of dates [e.g., 5th, 11th, 14th, 15th, ...]?

It looks like you still have dates and times strored as strings in your data table. Again, this is going to slow down performance and what you are trying to do in this question will make it worse. You really need to fix this as suggested in the answers to your previous questions on this query.
Issue in data fetching from DB using Ignition Named query - #2 by Transistor

2 Likes
4

I think your looking for 'Group By'. Include a Date column in your select statement and 0 out the time. to group by the day. or create a column that renders to a shift name/number. and group by that. Here is something I wrote to do something similar.

/*Cross Tab from RunTime table  by the day*/
SELECT sum(BoilerRT) as BoilerRT,
  sum(DrumRT) as DrumRT,
  sum(FluidRT) as FluidRT,					/* add up values taken for each day */
  format(datetimefromparts(
  datepart(year,TStamp),
  datepart(month,TStamp),
  datepart(day,TStamp),0,0,0,0),'MM/dd/yy') as TStamp	/* put in normal date format 0 out time*/
FROM TblDataLog
WHERE (BoilerRT + DrumRT + FluidRT) > 0			/* Not empty weekend days */
group by datetimefromparts(				/* Group Each day, could be month */
  datepart(year,TStamp),
  datepart(month,TStamp),
  datepart(day,TStamp),0,0,0,0)
5

You really should change the design of your table to use actual Date and Time types.

That aside why are you declaring variables? Just use named query parameters as you already have for BG, Plant, Line, and Machine? Why would the dates be any different?

2 Likes