Solved

Selecting a week back date range

Posted on 2012-04-12
3
270 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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