Solved

Filter results based on 2 differnt time fields - SQL

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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