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
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,398 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

861 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