Selecting a week back date range

Posted on 2012-04-12
Medium Priority
Last Modified: 2012-04-16
sum(item_qty) as item_qty,
sum(amount) as amount,
avg(item_price)as item_price
into #scanCt
from openquery(PP_NEW,
'select item_no,item_qty, amount,item_price,category,
from invoice left outer join line_it
on invoice.invoice_no=line_it.invoice_no
left outer join ship_to
on invoice.ship_id=ship_to.ship_id
where status=[Posted]
and type=[Invoice]
and invoice.ship_dt>={01/01/12}
and item_qty>0
and lscanpo=.T.
and invoice.bill_id =[CHOMEV]
and ship_to.ship_id in ([[HD04279],[HD04280],[HD04282],[HD04283],[HD04284],[HD04285],[HD04286],
[HD04287],[HD02401],[HD02403],[HD02404],[HD02405], [HD02406],[HD02407],[HD02408],[HD02409],[HD02410],
[HD02412],[HD02414],[HD03401],[HD03403],[HD03480],[HD03481],[HD03482], [HD03484],[HD03485],[HD03486],

where bill_id=@customer and
ship_dt between @startDate and @endDate
group by ship_id,item_no

users are entring a start date and end date 02/01/12 to 04/12/12
I am loking for to be able to calculate dates a week mack Mon to Sunday

So if the end date is falling in the week of (04/09 – 04/14) the week back dates will be 04/02/12 to 04/08/12
Question by:GadFriedman
  • 2
LVL 23

Accepted Solution

wdosanjos earned 2000 total points
ID: 37839693
Here is one solution.  For a given date @dt it calculates the previous Monday to Sunday week.
SET DATEFIRST 1 -- Week starts on Monday

declare @dt date
set @dt = getdate()

select dateadd(d, -datepart(dw, @dt) - 6, @dt) PreviousMonday,
       dateadd(d, -datepart(dw, @dt), @dt) PreviousSunday

/* Output

PreviousMonday PreviousSunday
-------------- --------------
2012-04-02     2012-04-08


Open in new window


Author Comment

ID: 37844734
This works, one problem in the attach SP where do I place the command
SET DATEFIRST 1 -- Week starts on Monday
so when I call the sp it will set the day of the week to Monday
I am using the function you sugested for the insert code #salesLastWeek
LVL 23

Expert Comment

ID: 37851387
The SET DATEFIRST should be placed after the procedure AS statement.  The updated procedure is attached.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

587 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