MSSQL Date Range

AND pe.timestamp >= ('2013-04-01')  AND pe.timestamp <= ('2013-04-30')

Why is this returning many other dates that do not fall into this date range? How do I fix the SQL so that it does not return any other dates but those I need? HELP I'm am about to pull out my last hair.
rlhardenAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
WHERE AND OR AND OR AND OR AND AND

The query engine is going to interpret the above as indivudal and's and or's, instead of coleectively.   To change this, you need parentheses marks ( ) to set order of precedence.

For example.
( 2 x 3 ) - 4 = 2
2 X ( 3 - 4 ) = -2

... so in your SQL, based on your needs perhaps ...
WHERE pe.billable_ind = 'Y'
    AND ( ep.payer_id = 'A212B895-DFEE-432F-AC4F-E81661A54C5E' OR ep.payer_id = '3729D520-F93E-4CB8-A783-9B50099A4347' )
    AND ( pe.create_timestamp >= '2013-04-01' AND pe.create_timestamp <= '2013-04-30' )
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us your entire SQL statement.
0
 
rlhardenAuthor Commented:
SELECT pe.[enc_id]
      ,ed.[icd9cm_code_id]
      ,pml.other1_name
      ,lm.location_name
      ,pml.first_name
      ,pml.last_name
      ,pe.[person_id]
      ,pe.[create_timestamp]
      ,pm.[payer_name]
  FROM  [MyData].[dbo].[patient_encounter] pe
  JOIN [MyData].[dbo].[encounter_diags] ed ON ed.enc_id = pe.enc_id
  JOIN [MyData].[dbo].[encounter_payer] ep ON ep.enc_id = pe.enc_id
  JOIN [MyData].[dbo].[location_mstr] lm ON lm.location_id = pe.location_id
  JOIN [MyData].[dbo].[payer_mstr] pm ON pm.payer_id = ep.[payer_id]
  JOIN [MyData].[dbo].[provider_mstr] pml ON pml.provider_id = pe.rendering_provider_id
  where pe.billable_ind = 'Y'
AND ep.payer_id = 'A212B895-DFEE-432F-AC4F-E81661A54C5E'
OR ep.payer_id = '3729D520-F93E-4CB8-A783-9B50099A4347'
AND pe.create_timestamp >= ('2013-04-01')
AND pe.create_timestamp <= ('2013-04-30')
ORDER BY pe.create_timestamp
--AND lm.location_name LIKE '%SomeLocation%'
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Surendra NathTechnology LeadCommented:
try the below code

AND month(pe.timestamp) = 4 and  year(pe.timestamp) = 2013 

Open in new window

0
 
jrm213jrm213Commented:
what datatype is pe.timestamp
0
 
rlhardenAuthor Commented:
Nope not the answer still getting 2012-01-31 12:57:21.083
2012-04-06 10:11:32.630
0
 
rlhardenAuthor Commented:
AND ( pe.create_timestamp >= '2013-04-01' AND pe.create_timestamp <= '2013-04-30' )

Still returns unwanted dates

2012-01-31 12:57:21.083
2012-04-06 10:11:32.630
2012-07-02 13:22:43.743
2012-07-02 13:22:43.743
2012-07-03 09:43:38.290
2012-07-05 09:59:33.153
2012-07-05 09:59:33.153
2012-07-05 09:59:33.153
2012-07-06 14:44:45.530
2012-07-06 14:44:45.530
2012-07-06 14:44:45.530
2012-07-09 09:59:51.010
2012-07-09 09:59:51.010
2012-07-09 09:59:51.010
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Perhaps your JOINs are returning duplicate rows, where it is evaluating the dates across multiple rows, and returning all of them in your recordset.

>AND pe.create_timestamp <= '2013-04-30' )
btw this won't catch '2013-04-30 12:00:00 PM' values, so either use neo_jarvis' code of month and hear, or use pe.create_timestamp < '2013-05-01' )
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<slight clarification on the above comment>  duplicate and/or multiple rows with different dates
0
 
SharathData EngineerCommented:
What is the datatype of create_timestamp?
Can you just run this and see what is returned?
SELECT distinct case when pe.create_timestamp >= '2013-04-01' AND pe.create_timestamp <= '2013-04-30' then 'within range' else 'outside' end data_check
  FROM  [MyData].[dbo].[patient_encounter] pe
  JOIN [MyData].[dbo].[encounter_diags] ed ON ed.enc_id = pe.enc_id
  JOIN [MyData].[dbo].[encounter_payer] ep ON ep.enc_id = pe.enc_id
  JOIN [MyData].[dbo].[location_mstr] lm ON lm.location_id = pe.location_id
  JOIN [MyData].[dbo].[payer_mstr] pm ON pm.payer_id = ep.[payer_id]
  JOIN [MyData].[dbo].[provider_mstr] pml ON pml.provider_id = pe.rendering_provider_id
  where pe.billable_ind = 'Y'
AND ep.payer_id IN('A212B895-DFEE-432F-AC4F-E81661A54C5E','3729D520-F93E-4CB8-A783-9B50099A4347')
AND pe.create_timestamp >= '2013-04-01'
AND pe.create_timestamp <= '2013-04-30'

Open in new window

0
 
jrm213jrm213Connect With a Mentor Commented:
This part of his statement is more important

AND ( ep.payer_id = 'A212B895-DFEE-432F-AC4F-E81661A54C5E' OR ep.payer_id = '3729D520-F93E-4CB8-A783-9B50099A4347' )


If you don't have that in parens, it will bring back anything where ep.payer_id = '3729D520-F93E-4CB8-A783-9B50099A4347' regardless of your date range.
0
 
rlhardenAuthor Commented:
The data type for pe.timestamp - datetime, NOT NULL
0
 
rlhardenAuthor Commented:
jrm213jrm213

You Are A Genius!!!

this worked AND ( ep.payer_id = 'A212B895-DFEE-432F-AC4F-E81661A54C5E' OR ep.payer_id = '3729D520-F93E-4CB8-A783-9B50099A4347' )
 
AND ( pe.enc_timestamp >= '2013-04-01' AND pe.enc_timestamp <= '2013-04-30' )

yes yes yes!!!!
0
 
Surendra NathTechnology LeadCommented:
I believe jrm213jrm213 solved your issue follow his instruction.
just for the convinience the entire code is put here.. ( dont award points here :), hey should go to jrm213jrm213.)

SELECT pe.[enc_id]
      ,ed.[icd9cm_code_id]
      ,pml.other1_name
      ,lm.location_name
      ,pml.first_name
      ,pml.last_name
      ,pe.[person_id]
      ,pe.[create_timestamp]
      ,pm.[payer_name]
  FROM  [MyData].[dbo].[patient_encounter] pe
  JOIN [MyData].[dbo].[encounter_diags] ed ON ed.enc_id = pe.enc_id
  JOIN [MyData].[dbo].[encounter_payer] ep ON ep.enc_id = pe.enc_id
  JOIN [MyData].[dbo].[location_mstr] lm ON lm.location_id = pe.location_id
  JOIN [MyData].[dbo].[payer_mstr] pm ON pm.payer_id = ep.[payer_id]
  JOIN [MyData].[dbo].[provider_mstr] pml ON pml.provider_id = pe.rendering_provider_id
  where pe.billable_ind = 'Y'
AND (ep.payer_id = 'A212B895-DFEE-432F-AC4F-E81661A54C5E'
OR ep.payer_id = '3729D520-F93E-4CB8-A783-9B50099A4347')
AND pe.create_timestamp >= ('2013-04-01')
AND pe.create_timestamp <= ('2013-04-30')
ORDER BY pe.create_timestamp
--AND lm.location_name LIKE '%SomeLocation%'

Open in new window

0
 
rlhardenAuthor Commented:
jrm213jrm213 was a very big help... I need this report to be acurate Thanks jrm213jrm213
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok, I'll bite.

Looking at the point made here, it was made previously in SQL in my comment here, yet the comment made later received all the points.

Let me know if you wish to correct this before I object to the grading of this question.
0
 
jrm213jrm213Commented:
I agree Jimhorn should get the majority of the points, I quoted his post. The OP had just done the parens around the wrong part in his response (39175887) and I was pointing out that the other part having the parens was more important.
0
 
SharathData EngineerCommented:
I agree with jimhorn. He pointed out the issue in your code and fixed it. He should be awarded for his work.
0
 
PortletPaulConnect With a Mentor freelancerCommented:
btw: I believe jimhorn recognized the need for for those parentheses at 05:28:47 ID: 39175875

but: there is just one problem left remaining

you will exclude datetimes that you actually do want

the end of April, is not '2013-04-30 00:00:00 +0000000'

You will get far greater reliablity and precision in your date range filtering by using a commbination of >= with <
like this:

AND ( pe.enc_timestamp >= '2013-04-01' AND pe.enc_timestamp < '2013-05-01' )

for more on this topic please see: "Beware of Between"
0
 
PortletPaulfreelancerCommented:
(C) Question answered by one or more Expert comments
without any doubt
jimhorn recognized the need for for 2 pairs of parentheses at 05:28:47 ID: 39175875

The author also needs to be aware that his date range filtering is in error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.