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,324 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
  • 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now