Solved

Selecting a week back date range

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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