asked on

Filter results based on 2 differnt time fields - SQL

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

Simone B

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.

Butterfly2

ASKER

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.

awking00

In addition to buttercup's questions, can last_data_cngdtime be null?

Simone B

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial