Solved

Getting too many records due to where clause.

Posted on 2009-07-14
17
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
17 Comments
 
LVL 60

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 60

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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
LVL 60

Expert Comment

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

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

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 60

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 60

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 60

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 60

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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