# Query reporting improperly

**URL:** <https://forum.inductiveautomation.com/t/query-reporting-improperly/94267>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [August 6, 2024, 10:28pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267 "2024-08-06T22:28:47Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![thomas\_wallace](https://avatars.discourse-cdn.com/v4/letter/t/57b2e6/32.png) [@thomas\_wallace](https://forum.inductiveautomation.com/u/thomas_wallace)\
**Post date:** [August 6, 2024, 10:28pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/1 "2024-08-06T22:28:47Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/9/8922bd110a3746dc5736e880efcb3e087585204a.png)

My query is skipping days of logging days. its skipping a week and at times 2 weeks in row. Anyone see the issue with my script? My transaction group has all the correct data.

```python
SELECT Diesel, TranSynd_ATF , [75_90_WT_SYN], [75_90_WT_SYN_2], [50_WT_MTF] , [10W_30] , Diesel_2 , MD3_ATF , Coolant , DEF , t_stamp 
From TankFarm
Where DATEPART(DAY, t_stamp) >= DAY(getDate()-7) OR DATEPART(DAY, t_stamp) = DAY(getDate())
Order by t_stamp desc

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 6, 2024, 10:48pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/2 "2024-08-06T22:48:50Z")

</div>

How's that supposed to work at month end roll-over? e.g., on 2024-08-06 you'll have,

```python
DATEPART(DAY, t_stamp) >= DAY(getDate()-7) 
                          ^^^ -> 30 (returns only 30th + 31st)
OR 
DATEPART(DAY, t_stamp) = DAY(getDate())
                         ^^^ -> 6

```

General comments:

1. Your query has to scan the data table and run every row's t\_stamp through the DATEPART function before it can do the comparison. If the t\_stamp column is indexed it's no use for this query. You should be selecting by `t_stamp > ...`. This way the query only examines records within the date range.

2. `OR DATEPART(DAY, t_stamp) = DAY(getDate())` seems redundant. Today is always \> seven days ago (ignoring the problem addressed in comment 1).

---

<div class="post-metadata">

**Author:** ![thomas\_wallace](https://avatars.discourse-cdn.com/v4/letter/t/57b2e6/32.png) [@thomas\_wallace](https://forum.inductiveautomation.com/u/thomas_wallace)\
**Post date:** [August 6, 2024, 11:21pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/3 "2024-08-06T23:21:40Z")

</div>

@Transistor Ohhhh you're right. I'm only getting dates of the 6th,30th, and 31st.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 7, 2024, 2:01pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/4 "2024-08-07T14:01:20Z")

</div>

Good! Now show us your modified query that uses the t\_stamp index properly!

---

<div class="post-metadata">

**Author:** ![thomas\_wallace](https://avatars.discourse-cdn.com/v4/letter/t/57b2e6/32.png) [@thomas\_wallace](https://forum.inductiveautomation.com/u/thomas_wallace)\
**Post date:** [August 9, 2024, 5:16pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/5 "2024-08-09T17:16:38Z")

</div>

@Transistor

```python
SELECT Diesel, TranSynd_ATF , [75_90_WT_SYN], [75_90_WT_SYN_2], [50_WT_MTF] , [10W_30] , Diesel_2 , MD3_ATF , Coolant , DEF , t_stamp 
From TankFarm

Where t_stamp >= (SELECT MIN(t_stamp) FROM TankFarm)
AND t_stamp <= GETDATE()

Order by t_stamp desc

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 9, 2024, 5:22pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/6 "2024-08-09T17:22:41Z")

</div>

That should perform a lot better.

Unless your database has records from the future, the line  
`AND t_stamp <= GETDATE()`  
is redundant.

Tips:

- Decide on whether SQL keywords are uppercase or lowercase and then be consistent.
- You can wrap lines for readability.

```python
SELECT  
  Diesel,  
  TranSynd_ATF , 
  [75_90_WT_SYN], 
  [75_90_WT_SYN_2], 
  [50_WT_MTF] , 
  [10W_30] ,  
  Diesel_2 ,  
  MD3_ATF , 
  Coolant ,  
  DEF ,  
  t_stamp 
FROM TankFarm
WHERE t_stamp >= (SELECT MIN(t_stamp) FROM TankFarm)
ORDER BY t_stamp DESC

```

It's easier to find fields, count them and reorder them.

---

<div class="post-metadata">

**Author:** ![thomas\_wallace](https://avatars.discourse-cdn.com/v4/letter/t/57b2e6/32.png) [@thomas\_wallace](https://forum.inductiveautomation.com/u/thomas_wallace)\
**Post date:** [August 9, 2024, 5:29pm UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/7 "2024-08-09T17:29:39Z")

</div>

Ok I removed that line. Still shows the same data as before. Thanks.

I just took over our Ignition so been cleaning up stuff. Still have alot to learn so thanks for any tips and input.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 10, 2024, 11:41am UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/8 "2024-08-10T11:41:14Z")

</div>

Don't use column names that start with digits. There are a number of places in Ignition that will barf at that.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [August 10, 2024, 11:57am UTC](https://forum.inductiveautomation.com/t/query-reporting-improperly/94267/9 "2024-08-10T11:57:23Z")

</div>

I was thinking the same thing but thought I had enough in my post. Don't forget that you can rename the columns in the query.

```python
SELECT  
  Diesel,  
  TranSynd_ATF , 
  [75_90_WT_SYN] AS wt_syn_75_90, 
  [75_90_WT_SYN_2] AS wt_syn_2_75_90, 
  [50_WT_MTF] AS wt_mtf_60, 
  [10W_30] AS somethingElseMeaningful,  
...

```