Solved

Selecting a week back date range

Posted on 2012-04-12
3
261 Views
Last Modified: 2012-04-16
select
ship_id,
item_no,
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,
invoice.ship_id,invoice.bill_id,invoice.ship_dt
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],
[HD03488],[HD04551],[HD03402],[HD03406],[HD03407],[HD03408],[HD04501],[HD04502],[HD08539])
')

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
0
Comment
Question by:GadFriedman
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 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

0
 

Author Comment

by:GadFriedman
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
hdPbs12-Thom.sql
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37851387
The SET DATEFIRST should be placed after the procedure AS statement.  The updated procedure is attached.
hdPbs12-Thom.sql.txt
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now