Solved

Filter results based on 2 differnt time fields - SQL

Posted on 2013-12-13
5
218 Views
Last Modified: 2014-01-03
Good Morning Experts,

I need some assistance I have a query that I am  running with a date filter called a
actv_entry_dtime, which is the date the order was posted,2 days b/f today(get date) .  Then there is another update feld call last_data_cngdtime which is the last update.  I also want this day to be getdate() -2 but when I try to filter on both I dont  get any records I tried AND and EXISTs.  I need to do it like this because I need to include financial data.   And it has to match another report that runs 2 days behind.

Here is the query:
Select  a.pt_id    as [Acct],
          pt.rpt_name      as [PT Name],
          pt.pt_id_start_dtime,
         au.user_text as [PCP Cd],
         a.actv_dtime,
         a.actv_entry_dtime,
         a.actv_tot_qty,
         a.chg_tot_amt,
        a.last_data_cngdtime
             
From smsmir.mir_actv a
    Left Outer Join smsmir.mir_acct_user  as au
            on au.src_sys_id = a.src_sys_id
                  and au.pt_id = a.pt_id
                  and au.user_comp_id = 'pcpdoct'
    Inner Join smsmir.mir_pt as pt
            on pt.src_sys_id = a.src_sys_id
            and pt.pt_id = a.pt_id
       Inner Join  smsmir.mir_acct as acc
                     on acc.src_sys_id = a.src_sys_id
                    and acc.orgz_cd = a.orgz_cd
                    and acc.pt_id = a.pt_id
    Inner Join smsmir.mir_orgz as o
             on   o.src_sys_id = a.src_sys_id
             and o.orgz_cd = a.orgz_cd  
Where o.orgz_short_name = 'SMH'
    and (acc.adm_dtime BETWEEN '11-01-2013 00:00:00' and '11-30-2013 23:59:59'
    or acc.dsch_dtime  BETWEEN '11-01-2013 00:00:00' and '11-30-2013 23:59:59')
    and a.actv_cd BETWEEN '49000000'
    and '49999999'
   and (Convert(varchar(10),a.actv_entry_dtime,101) <= Convert(varchar(10),getdate() -2,101)
   and exists(select actv_cd
                              from smsmir.mir_actv
                              where  Convert(varchar(10),last_data_cngdtime) <= Convert(varchar(10),getdate() -2,101)))

ORDER BY a.actv_entry_dtime desc,
                a.pt_id
0
Comment
Question by:Butterfly2
5 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39717128
You could try the DATEDIFF function. If you want something more granular, you can use hours, minutes or even seconds instead of days (as shown below). This will also make it dynamic, so that you don't have dates hard-coded in your query.

Question: Do you want both dates to be within the last 2 days (use AND), or at least one of the dates within the last 2 days (use OR)? I used AND below.

WHERE DATEDIFF(d,acc.adm_dtime,GETDATE()) < 2 AND DATEDIFF(d,acc.dsch_dtime,GETDATE()) < 2

Open in new window


If you still are getting no data, try running your query with only one filter, then add one at a time until you see which one is causing the problem.
0
 

Author Comment

by:Butterfly2
ID: 39717160
These are not the fields that are giving me problems, its  this part
 and (Convert(varchar(10),a.actv_entry_dtime,101) <= Convert(varchar(10),getdate() -2,101)
   and exists(select actv_cd
                              from smsmir.mir_actv
                              where  Convert(varchar(10),last_data_cngdtime) <= Convert(varchar  (10),getdate() -2,101)))

both work fine separelty its when you put them together, I dont get any records.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39717161
In addition to buttercup's questions, can last_data_cngdtime be null?
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39717369
If all of the fields you mention are datetime, there should be no need to convert anything to varchar. What happens when you compare the dates directly, without the convert?

   and DATEDIFF(d,a.actv_entry_dtime,GETDATE()) <= 2
   AND DATEDIFF(d,last_data_cngdtime,GETDATE()) <=2

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39717446
Format 101, mm/dd/yyyy, for a date time will never compare correctly as a string value.  For example, month 12 will always be greater than month 01, no matter what the year.

Besides, you should never convert date/datetime into a string value to do comparisons anyway.  It is a huge hit to performance and often to accuracy, as you've seen.

Try these conditions instead:

Where o.orgz_short_name = 'SMH'
    and ((acc.adm_dtime >= '20131101' and acc.adm_dtime < '20131201')
    or (acc.dsch_dtime >='20131101' and acc.dsch_dtime < '20131201'))
    and a.actv_cd BETWEEN '49000000'
                      and '49999999'
   and a.actv_entry_dtime <= getdate() - 2
   and exists(select actv_cd
                              from smsmir.mir_actv
                              where  last_data_cngdtime <= getdate() - 2)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

756 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