Solved

MSSQL Date Range

Posted on 2013-05-17
22
390 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 40

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…

856 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