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