Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filter results based on 2 differnt time fields - SQL

Posted on 2013-12-13
5
Medium Priority
?
233 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

580 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