Query reporting improperly

Ignition
1

image

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.

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
2

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

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).

3

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

1 Like
4

Good! Now show us your modified query that uses the t_stamp index properly!

2 Likes
5

@Transistor

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
6

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.
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.

7

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.

8

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

9

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.

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,  
...