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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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

726 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