CODEWITHSUNDEEP
sqlsql-servert-sqlrange
Jenesis
Jenesis

Reputation: 109

SQL range conditions less than, greater than and between

What I would like to accomplish is; query if 'email ocr in' & 'universal production' rows in the "documents created column" field, total the same amount as "email OCR" 'documents_created. If not, pull that batch. finally if the attachment count is less than 7 entries after the email ocr in & universal production files are pulled then return said result

enter image description here

enter image description here

current query below: 

use N
SELECT   id, 
         type, 
         NAME, 
         log_time , 
         start_time , 
         documents_created , 
         pages_created, 
         processed, 
         processed_time  

FROM     N_LF_OCR_LOG 

WHERE    

    -- Log time is current day
    log_time between  CONVERT(date, getdate()) AND CONVERT(datetime,floor(CONVERT(float,getdate()))) + '23:59:00'  

    -- Documents created is NULL or non zero
    AND (documents_created IS NULL OR documents_created <> 0)

  or  ( documents_created is null and log_time between  CONVERT(date, getdate()) AND CONVERT(datetime,floor(CONVERT(float,getdate()))) + '23:59:00')  

    -- Filter for specific types
    AND type IN ('Email OCR In', 
            'Universal Production') 

    -- Filter to rows where number of pages and documents created are not equal
    AND documents_created <2 and pages_created >2 

ORDER BY log_time
,id asc 
,processed_time asc

any idea how to incorporate that? Im a novice. thanks

Upvotes: 2

Views: 1038

Answers (1)

Simulant
Simulant

Reputation: 20112

When creating an index, you just specify the columns to be indexed. There is no difference in creating an index for a range query or an exact match. You can add multiple columns to the same index so all columns can benefit from the index, because only one index per table at the time can be selected to support a query.

You could create an index just covering your where-clause:

alter table N_LF_OCR_LOG add index test1(log_time, documents_created, type, pages_created);

Or also add the required columns for the ordering into the index. The ordering of the columns in the index is important and must be the same as for the ordering in the query:

alter table N_LF_OCR_LOG add index test1(log_time, id, processed_time, documents_created, type, pages_created);

Or add a covering index that also contains the returned columns so you do not have to load any values from your tables and can answer to complete query by just using the index. This gives the best response time for the query. But the index takes up more space on the disk.

alter table N_LF_OCR_LOG add index test1(log_time, id,  processed_time, documents_created, type, pages_created, NAME, start_time, processed);

Use the explain keyword infront of your query to see how good your index performs.

Upvotes: 1

Related Questions