Getting too many records due to where clause.

I have a query that gets distinct rows.  I have a date range but the date range is still grabbing a record that I do not want.    The first row in the results I have pasted below should not be included.   There can be several rows with the same id number as in the first colum but then they are consolidated and the min function is called to get the earliest date.  But once I add the data range it no longer cares about the min function and the date of 7-13-2009 because of the fact that there is another record with a later date with the same Job ID.  Can I do anything in the where clause to keep the record out if the minimum date is out of range?

-------  This row is not to be included due to the data being out of range.  ------
11086      Test Foods      20383      200      2009-05-18 18:30:24.123      2009-06-11 09:31:02.980      2009-07-13 21:00:00.000      20383      24151      24151      NULL      NULL      Lisa                           Sales Person                       154945      20383

SELECT       DISTINCT JOB_ORDER.ID
                         , JOB_ORDER.COMPANY_NAME, JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.ESTIMATED_PARTICIPATION
                         , JOB_ORDER.CREATED_TIMESTAMP
                         , JOB_ORDER.UPDATED_TIMESTAMP
                         , (SELECT MIN(JOS.START_TIMESTAMP) FROM JOB_ORDER_SCHEDULE JOS WHERE JOS.JOB_ORDER_ID = JOB_ORDER.ID ) AS START_TIMESTAMP
                         , JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.FINAL_APPROVAL_AUTH_ID
                         , JOB_ORDER.REVIEWER_AUTH_ID
                         , JOB_ORDER.DRAFT_FLAG
                         , JOB_ORDER.DELETED_FLAG
                         , PATIENT.FIRST_NAME
                         , PATIENT.LAST_NAME
                         , PATIENT.PATIENT_ID
                         , PATIENT.AUTH_ID
            FROM         JOB_ORDER INNER JOIN
                         JOB_ORDER_SCHEDULE 
                         	ON JOB_ORDER.ID = JOB_ORDER_SCHEDULE.JOB_ORDER_ID INNER JOIN
                         PATIENT 
                         	ON JOB_ORDER.CREATED_AUTH_ID = PATIENT.AUTH_ID
            WHERE        (START_TIMESTAMP BETWEEN '07/14/2009' AND DATEADD(DAY, 1, '10/31/2009')) AND (JOB_ORDER.DELETED_FLAG IS NULL) AND (JOB_ORDER.DRAFT_FLAG IS NULL) 
                         AND (JOB_ORDER.FINAL_APPROVAL_AUTH_ID IS NOT NULL) 
            AND JOB_ORDER.DEMO_JOB IS NULL
            AND (JOB_ORDER.JOB_ORDER_TYPE_ID <> 2)
            ORDER BY LAST_NAME, FIRST_NAME, START_TIMESTAMP

Open in new window

monica73174Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
It is possibly some date format issue, try like this:+Also verify that the start_timestamp column is actual datetime data type and not a varchar field holding a date string.
SELECT       DISTINCT JOB_ORDER.ID
                         , JOB_ORDER.COMPANY_NAME, JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.ESTIMATED_PARTICIPATION
                         , JOB_ORDER.CREATED_TIMESTAMP
                         , JOB_ORDER.UPDATED_TIMESTAMP
                         , (SELECT MIN(JOS.START_TIMESTAMP) FROM JOB_ORDER_SCHEDULE JOS WHERE JOS.JOB_ORDER_ID = JOB_ORDER.ID ) AS START_TIMESTAMP
                         , JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.FINAL_APPROVAL_AUTH_ID
                         , JOB_ORDER.REVIEWER_AUTH_ID
                         , JOB_ORDER.DRAFT_FLAG
                         , JOB_ORDER.DELETED_FLAG
                         , PATIENT.FIRST_NAME
                         , PATIENT.LAST_NAME
                         , PATIENT.PATIENT_ID
                         , PATIENT.AUTH_ID
            FROM         JOB_ORDER INNER JOIN
                         JOB_ORDER_SCHEDULE 
                                ON JOB_ORDER.ID = JOB_ORDER_SCHEDULE.JOB_ORDER_ID INNER JOIN
                         PATIENT 
                                ON JOB_ORDER.CREATED_AUTH_ID = PATIENT.AUTH_ID
            WHERE        (START_TIMESTAMP BETWEEN CONVERT(DATETIME, '2009-07-14') AND CONVERT(DATETIME, '2009-10-31')+1) AND (JOB_ORDER.DELETED_FLAG IS NULL) AND (JOB_ORDER.DRAFT_FLAG IS NULL) 
                         AND (JOB_ORDER.FINAL_APPROVAL_AUTH_ID IS NOT NULL) 
            AND JOB_ORDER.DEMO_JOB IS NULL
            AND (JOB_ORDER.JOB_ORDER_TYPE_ID <> 2)
            ORDER BY LAST_NAME, FIRST_NAME, START_TIMESTAMP

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Another think to try if START_TIMESTAMP is a DATETIME field.


( START_TIMESTAMP >= CONVERT(DATETIME, '2009-07-14') 
   AND START_TIMESTAMP < CONVERT(DATETIME, '2009-10-31')+1 )

Open in new window

0
monica73174Author Commented:
START_TIMESTAMP is generated as a timestamp in the database.  I really think the issue is in my where clause.  The problem is this, sometime I can have the same job id but it will have different start_timestamps.  
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Kevin CrossChief Technology OfficerCommented:
What SQL platform and version is this?
0
Kevin CrossChief Technology OfficerCommented:
But you have a filter indicating a date filter and you have all AND logical operators in your where clause.  Therefore, I agree it is in the where clause, but believe it has to be with how the date expression is evaluating.  If you had an OR in the statements I would say you are getting results for dates out of range that meet that criteria, but since these are connected with AND then your date criteria must be upheld as well.  Therefore, the query believes it is, it is just doing it incorrectly.
0
onethreefourCommented:
It always sucks to find every little bug, why not just throw a hack into it   ;]
in your where clause just add another condition to stop anything before your begin date, like:
....and START_TIMESTAMP > date_sub(NOW(),interval 2 day)....
0
monica73174Author Commented:
It is SQL Server 2005 on Windows 2003.

I will try the date_sub function.  
0
monica73174Author Commented:
date_sub is for MySQL?  
0
onethreefourCommented:
Yes date_sub is for MySQL...
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Are you sure you are using MySQL and not MSSQL (Microsoft SQL Server)?

0
Kevin CrossChief Technology OfficerCommented:
For MS SQL Server 2005, a TIMESTAMP field is not same as DATETIME:
http://msdn.microsoft.com/en-us/library/ms182776(SQL.90).aspx

Please check the table structure again for the actual datatype definition of START_TIMESTAMP.
0
monica73174Author Commented:
I said above that I was using SQ Server 2005, I asked if the date_sub function for MySQL.  The datatype for the field is datetime that gets generated automatically by SQL Server.  It is in the format of 2009-07-13 11:31:23:360.  I still think it is the way my where clause is written.  Does the where clause look at the distinct row in the select or does it look at all the records?  
0
Kevin CrossChief Technology OfficerCommented:
You are applying the WHERE to the entire table to filter it down and then the DISTINCT operates on the results to ensure you only get unique rows.

So back to my original suggestions, can you try changing this:

(START_TIMESTAMP BETWEEN '07/14/2009' AND DATEADD(DAY, 1, '10/31/2009'))

To this:

( START_TIMESTAMP >= CONVERT(DATETIME, '2009-07-14')
   AND START_TIMESTAMP < CONVERT(DATETIME, '2009-10-31')+1 )
0
Kevin CrossChief Technology OfficerCommented:
TIMESTAMP is one of the data types that can store DATETIME in MySQL which was part of the confusion I think.  I saw where you said MS SQL Server 2005 which is why I asked for clarification as TIMESTAMP doesn't work the same in MS SQL as I pointed out.
0
monica73174Author Commented:
Using the convert statement did not seem to work.  It came back with the same results.  
0
Kevin CrossChief Technology OfficerCommented:
LOL, it helps if I really look at your query.  You are filtering the records correctly, but then going back and querying the table to get the min ID.

(SELECT MIN(JOS.START_TIMESTAMP) FROM JOB_ORDER_SCHEDULE JOS WHERE JOS.JOB_ORDER_ID = JOB_ORDER.ID ) AS START_TIMESTAMP

This is what is incorrect.  You will need to use a GROUP BY with all the columns except START_TIMESTAMP in the list and then apply MIN() function to that column OR alternatively use an OVER analytical statement with ROW_NUMBER() function as long as your database is set to 2005 compatibility and not older version.
-- in order for this to work all the other fields must be same across all the rows which would be the case for DISTINCT to work anyway
SELECT       JOB_ORDER.ID
                         , JOB_ORDER.COMPANY_NAME, JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.ESTIMATED_PARTICIPATION
                         , JOB_ORDER.CREATED_TIMESTAMP
                         , JOB_ORDER.UPDATED_TIMESTAMP
                         , MIN(JOB_ORDER_SCHEDULE.START_TIMESTAMP) AS START_TIMESTAMP
                         , JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.FINAL_APPROVAL_AUTH_ID
                         , JOB_ORDER.REVIEWER_AUTH_ID
                         , JOB_ORDER.DRAFT_FLAG
                         , JOB_ORDER.DELETED_FLAG
                         , PATIENT.FIRST_NAME
                         , PATIENT.LAST_NAME
                         , PATIENT.PATIENT_ID
                         , PATIENT.AUTH_ID
            FROM         JOB_ORDER INNER JOIN
                         JOB_ORDER_SCHEDULE 
                                ON JOB_ORDER.ID = JOB_ORDER_SCHEDULE.JOB_ORDER_ID INNER JOIN
                         PATIENT 
                                ON JOB_ORDER.CREATED_AUTH_ID = PATIENT.AUTH_ID
            WHERE        (START_TIMESTAMP BETWEEN CONVERT(DATETIME, '2009-07-14') AND CONVERT(DATETIME, '2009-10-31')+1) AND (JOB_ORDER.DELETED_FLAG IS NULL) AND (JOB_ORDER.DRAFT_FLAG IS NULL) 
                         AND (JOB_ORDER.FINAL_APPROVAL_AUTH_ID IS NOT NULL) 
            AND JOB_ORDER.DEMO_JOB IS NULL
            AND (JOB_ORDER.JOB_ORDER_TYPE_ID <> 2)
	    GROUP BY JOB_ORDER.ID
                         , JOB_ORDER.COMPANY_NAME, JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.ESTIMATED_PARTICIPATION
                         , JOB_ORDER.CREATED_TIMESTAMP
                         , JOB_ORDER.UPDATED_TIMESTAMP
                         , JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.FINAL_APPROVAL_AUTH_ID
                         , JOB_ORDER.REVIEWER_AUTH_ID
                         , JOB_ORDER.DRAFT_FLAG
                         , JOB_ORDER.DELETED_FLAG
                         , PATIENT.FIRST_NAME
                         , PATIENT.LAST_NAME
                         , PATIENT.PATIENT_ID
                         , PATIENT.AUTH_ID
            ORDER BY LAST_NAME, FIRST_NAME, START_TIMESTAMP

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Other approach:
SELECT *
FROM (	
	SELECT       JOB_ORDER.ID
                         , JOB_ORDER.COMPANY_NAME, JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.ESTIMATED_PARTICIPATION
                         , JOB_ORDER.CREATED_TIMESTAMP
                         , JOB_ORDER.UPDATED_TIMESTAMP
                         , JOB_ORDER_SCHEDULE.START_TIMESTAMP
                         , JOB_ORDER.CREATED_AUTH_ID
                         , JOB_ORDER.FINAL_APPROVAL_AUTH_ID
                         , JOB_ORDER.REVIEWER_AUTH_ID
                         , JOB_ORDER.DRAFT_FLAG
                         , JOB_ORDER.DELETED_FLAG
                         , PATIENT.FIRST_NAME
                         , PATIENT.LAST_NAME
                         , PATIENT.PATIENT_ID
                         , PATIENT.AUTH_ID
			 , ROW_NUMBER() OVER (PARTITION BY JOB_ORDER_ID /* may need to include other columns to make unique */ ORDER BY START_TIMESTAMP) AS row
            FROM         JOB_ORDER INNER JOIN
                         JOB_ORDER_SCHEDULE 
                                ON JOB_ORDER.ID = JOB_ORDER_SCHEDULE.JOB_ORDER_ID INNER JOIN
                         PATIENT 
                                ON JOB_ORDER.CREATED_AUTH_ID = PATIENT.AUTH_ID
            WHERE        (START_TIMESTAMP BETWEEN CONVERT(DATETIME, '2009-07-14') AND CONVERT(DATETIME, '2009-10-31')+1) AND (JOB_ORDER.DELETED_FLAG IS NULL) AND (JOB_ORDER.DRAFT_FLAG IS NULL) 
                         AND (JOB_ORDER.FINAL_APPROVAL_AUTH_ID IS NOT NULL) 
            AND JOB_ORDER.DEMO_JOB IS NULL
            AND (JOB_ORDER.JOB_ORDER_TYPE_ID <> 2)
            ORDER BY LAST_NAME, FIRST_NAME, START_TIMESTAMP
) t
WHERE row = 1

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.