Solved

Filter results based on 2 differnt time fields - SQL

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

623 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