GadFriedman
asked on
Selecting a week back date range
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.bi ll_id,invo ice.ship_d t
from invoice left outer join line_it
on invoice.invoice_no=line_it .invoice_n o
left outer join ship_to
on invoice.ship_id=ship_to.sh ip_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],[HD0 4282],[HD0 4283],[HD0 4284],[HD0 4285],[HD0 4286],
[HD04287],[HD02401],[HD024 03],[HD024 04],[HD024 05], [HD02406],[HD02407],[HD024 08],[HD024 09],[HD024 10],
[HD02412],[HD02414],[HD034 01],[HD034 03],[HD034 80],[HD034 81],[HD034 82], [HD03484],[HD03485],[HD034 86],
[HD03488],[HD04551],[HD034 02],[HD034 06],[HD034 07],[HD034 08],[HD045 01],[HD045 02],[HD085 39])
')
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
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.bi
from invoice left outer join line_it
on invoice.invoice_no=line_it
left outer join ship_to
on invoice.ship_id=ship_to.sh
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],[HD0
[HD04287],[HD02401],[HD024
[HD02412],[HD02414],[HD034
[HD03488],[HD04551],[HD034
')
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The SET DATEFIRST should be placed after the procedure AS statement. The updated procedure is attached.
hdPbs12-Thom.sql.txt
hdPbs12-Thom.sql.txt
ASKER
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