Solved

Filter results based on 2 differnt time fields - SQL

Posted on 2013-12-13
5
211 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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