Solved

how do I get pull data from last Monday to This past Sunday in a MS SQL

Posted on 2014-02-20
2
1,416 Views
Last Modified: 2014-02-20
I have a query that pulls data but the client wants it weekly every Tuesday, but he wants it to show from Monday to Sunday of the week before.  I have seen the set datefirst function but i am just not sure how to incoportate it into my query.  So for example next Tuesday is
Feb 25 so on that day they would like to see the data from  Feb 17(Mon) to Feb 23(Sun).

Here is my query, notice what I put in my in the where  clause, this doesnt give me last week only this week.

Select  c.episode_no as Pt_No,
           c.pt_id_start_dtime as Adm_Date,
           c.rpt_name as Pt_Name,
           convert(varchar(10),c.birth_dtime,101) as 'DOB',
           o.orgz_short_name as 'Entity',
           c.cre_dtime as Case_Date,
           a.last_name+', ' + a.first_name as Guarantor_Name,
            v.prim_pyr_cd
           
        from smsmir.mir_pms_case as c
  inner join smsmir.mir_pers_addr as a
     on   c.src_sys_id = a.src_sys_id
     and c.pt_id = a.pt_id
     and c.episode_no = a.episode_no
     and c.pt_id_start_dtime = a.pt_id_start_dtime    
   inner join smsmir.mir_vst_pms as v
     on   c.src_sys_id = v.src_sys_id
     and c.pt_id = v.pt_id
     and c.episode_no = v.episode_no
     and c.pt_id_start_dtime = v.pt_id_start_dtime
where c.pt_type like 'E%'
    and c.pt_id_start_dtime between  DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0)      and DATEADD(week, DATEDIFF(day, 0, getdate())/7, 5)
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
  • 2
2 Comments
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 370 total points
ID: 39873585
You can not incorporate DateFirst into the query.

It is a setting for the server which you can change for your session.

If you need the last sundaythen you should set your DateFirst to Saturday (which means 7 and is the default)

http://msdn.microsoft.com/de-de/library/ms181598.aspx

SET @@DateFirst = 7

Then you can do something like

SELECT DateAdd(day, ((datepart(dw,GetDate())-1) * -1), GetDate())

which will always return the last sunday (except if you excute it on a sunday).

  SELECT DateAdd(day, ((datepart(dw,GetDate())-1) * -1)-6 , GetDate())

will return the monday before the last sunday.


where c.pt_type like 'E%'
    and c.pt_id_start_dtime between  DateAdd(day, ((datepart(dw,GetDate())-1) * -1)-6 , GetDate())      and DateAdd(day, ((datepart(dw,GetDate())-1) * -1), GetDate())

should do the trick for you.
0
 
LVL 12

Expert Comment

by:HugoHiasl
ID: 39873588
Sorry.. in line 3 it must read:

If you need the last sundaythen you should set your DateFirst to Sunday (which means 7 and is the default)...

I did the thing first for Saturday because I misread the requirements .-)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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