Date Range Selection in Query Templates

[Former Member]

Hi,

Can someone please tell me how to use <b>Date Range Selection</b> Tab details in SQL Query and TAG Query (Using Examples).

Thanks in advance

Muzammil P.T

[Former Member · Accepted solution]

Hi Muzammil,

In data range section you can have multiple options like setting start datetime and end datetime.. And you can set the shift (or) time period and also the format of the time periods.

Primarily we use the start date and end date querying to fetch data between two time labels. I can explain this one with example..

You have batch production table with columns <b>Production Time, Batch Id, Production Qty</b>. Then you want all the details between the 02/02/2007 to 05/02/2007.

Solution :

1.Map 02/02/2007 with Start date.

2.Map 05/02/2007 with end dare.

Now these two become the variables [SD] and [ED].

Now you have mentioned the date range, but you need to mention for which column these things to be applied. For that

3.In Query tab enter Production Time column name in Date column at the bottom.

Now you have written query like Select * from batch prodcution where production time > 02/02/2007 and production time < 05/02/2007.

4. Even those values you mapped (SD and ED) you can change from the front page through Java script.

______________________

Like wise you have so many other advantages also.

If you have any other specific doubt let me know.

Thanks,

Rajesh.

PS : Please ward points if answer is useful.

[Former Member · Accepted solution]

>>>>>>>>>>

Re: Date Range Selection in Query Templates

Posted: Feb 15, 2007 6:43 AM in response to: Muzammil Ahamed Reply E-mail this post

Hi Muzammil,

In data range section you can have multiple options like setting start datetime and end datetime.. And you can set the shift (or) time period and also the format of the time periods.

Primarily we use the start date and end date querying to fetch data between two time labels. I can explain this one with example..

You have batch production table with columns Production Time, Batch Id, Production Qty. Then you want all the details between the 02/02/2007 to 05/02/2007.

Solution :

1.Map 02/02/2007 with Start date.

2.Map 05/02/2007 with end dare.

Now these two become the variables [SD] and [ED].

Now you have mentioned the date range, but you need to mention for which column these things to be applied. For that

3.In Query tab enter Production Time column name in Date column at the bottom.

Now you have written query like Select * from batch prodcution where production time > 02/02/2007 and production time < 05/02/2007.

4. Even those values you mapped (SD and ED) you can change from the front page through Java script.

______________________

Like wise you have so many other advantages also.

If you have any other specific doubt let me know.

Thanks,

Rajesh.

PS : Please ward points if answer is useful. <<<<<<<<

Message was edited by:

David Dreyer

[Former Member · Accepted solution]

Rajesh:

From your message above (I re-posted) it sounds like the Query Template is one approach to establishing restricted date/time ranges for routine queries, like those that may be needed to show shift results from the plant floor.

We are looking at a limited implementation of xMII and historian for plant activity, before we have our ERP system in place. Thus we'll have no schedule data available to xMII for plant shifts from a planning MRP system. Have you seen this used in some manner for creating and managing the shift schedules for a plant floor?

Thanks.

david

Message was edited by:

David Dreyer

[Former Member · Accepted solution]

Hi David Dreyer,

I have done this type of implimentation where we used javascripting to define what are the shifts in a day and there by we used these dates to query the data from from back end system.

Say if you want the data for the first shift then you would specify just shift one on date x.I back end using javascript we determine start datetime and end datetime and then passed these values to the query.

Let me know if you want more detail.

Thanks,

Srinivas.

[jamie_cawley]

Hi Muzammil,

You can also use [SD] and [ED] in fixed queries.

For example

select * from Employees

where HireDate between [SD] and [ED]

[SD] and [ED] will be translated into what was chosen on the date range tab.

Regards,

Jamie

[Former Member]

Hi,

Generally date range tab details are used when we were using a tag query.Like if u need to find out history data of a tag say temperature between a start date and a end date u can give it there and query it for results.U can also define the interval for which u like to have the tagvalue.And also can define the various formats of datetime available.

If u were using a sql query then u need to define which column is having tht datetime format.for that u need to set that column in datecolumn under sql query details tab.And the rest is same as with the tag query.

regards,

Ravi Kumar