Solved

MSSQL Date Range

Posted on 2013-05-17
22
391 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 65

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 17

Expert Comment

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

Accepted Solution

by:
Jim Horn earned 250 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 65

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 65

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 150 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 65

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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 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 48

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion schedule 13 35
query output (script) from a stored procedure 4 39
point in time restore in SQL server 26 46
Split string into 3 separate fields 5 22
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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