?
Solved

MSSQL Date Range

Posted on 2013-05-17
22
Medium Priority
?
396 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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