Solved

Getting too many records due to where clause.

Posted on 2009-07-14
17
262 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:monica73174
  • 9
  • 5
  • 2
17 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24854821
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24854839
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
 

Author Comment

by:monica73174
ID: 24855230
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855244
What SQL platform and version is this?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855259
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
 
LVL 2

Expert Comment

by:onethreefour
ID: 24856117
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
 

Author Comment

by:monica73174
ID: 24856460
It is SQL Server 2005 on Windows 2003.

I will try the date_sub function.  
0
 

Author Comment

by:monica73174
ID: 24856464
date_sub is for MySQL?  
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Expert Comment

by:onethreefour
ID: 24856602
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24857424
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
 

Author Comment

by:monica73174
ID: 24859370
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24859550
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24859583
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
 

Author Comment

by:monica73174
ID: 24859620
Using the convert statement did not seem to work.  It came back with the same results.  
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24860227
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 24860257
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now