?
Solved

MSSQL Date Range

Posted on 2013-05-17
22
Medium Priority
?
395 Views
Last Modified: 2013-05-27
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.
0
Comment
Question by:rlharden
[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
  • 6
  • 5
  • 3
  • +3
22 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39175856
Show us your entire SQL statement.
0
 

Author Comment

by:rlharden
ID: 39175863
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39175865
try the below code

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

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 39175866
what datatype is pe.timestamp
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 39175875
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
 

Author Comment

by:rlharden
ID: 39175878
Nope not the answer still getting 2012-01-31 12:57:21.083
2012-04-06 10:11:32.630
0
 

Author Comment

by:rlharden
ID: 39175887
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39175894
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39175905
<slight clarification on the above comment>  duplicate and/or multiple rows with different dates
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39175921
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
 
LVL 17

Assisted Solution

by:jrm213jrm213
jrm213jrm213 earned 600 total points
ID: 39175926
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
 

Author Comment

by:rlharden
ID: 39175931
The data type for pe.timestamp - datetime, NOT NULL
0
 

Author Comment

by:rlharden
ID: 39175947
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39175955
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
 

Author Comment

by:rlharden
ID: 39175988
jrm213jrm213 was a very big help... I need this report to be acurate Thanks jrm213jrm213
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39176004
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 39176051
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39176337
I agree with jimhorn. He pointed out the issue in your code and fixed it. He should be awarded for his work.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 39176418
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39177185
(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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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