Last full week in sql

Noreen McHugh
Noreen McHugh used Ask the Experts™
on
I am having a problem with selecting the last full week , the code below shows every thing after midnight Sunday to before midnight the next Sunday.
 I just want to see from 08.00 Monday morning to 07.00 am the next monday morming.
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
SET @TodayDayOfWeek = datepart(dw, GetDate())
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
SELECT     TOP (100) PERCENT dbo.CreateOrder.n_OrderOriginallyPlacedTime, dbo.Plu.Plu, dbo.Plu.ItemName, dbo.Plu.Price, dbo.t_OrderLines.n_Amount, dbo.t_OrderLines.n_OrderNum, 
                      dbo.t_OrderLines.n_Type, dbo.t_OrderLines.n_MenuId, dbo.t_OrderPayment.n_Order, dbo.t_OrderPayment.n_PayAmount 
FROM         dbo.t_OrderPayment FULL OUTER JOIN
                      dbo.CreateOrder ON dbo.t_OrderPayment.nStoreID = dbo.CreateOrder.nStoreID AND 
                      dbo.t_OrderPayment.n_Order = dbo.CreateOrder.n_OrderNum FULL OUTER JOIN
                      dbo.t_OrderLines ON dbo.CreateOrder.n_OrderNum = dbo.t_OrderLines.n_OrderNum FULL OUTER JOIN
                      dbo.Plu ON dbo.t_OrderLines.n_MenuId = dbo.Plu.n_MenuId
WHERE     (dbo.t_OrderLines.nStoreID = '528') AND (dbo.CreateOrder.nStoreID = '528') AND (dbo.CreateOrder.n_StatusFlags <> '6') AND 
                      (dbo.t_OrderLines.n_Type <> '26') AND (dbo.t_OrderLines.n_Type <> '18') AND(dbo.CreateOrder.n_OrderOriginallyPlacedTime BETWEEN 
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7))
 AND (dbo.t_OrderLines.n_MenuId <> '2719')
ORDER BY dbo.CreateOrder.n_OrderOriginallyPlacedTime

Open in new window

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
SET @TodayDayOfWeek = datepart(dw, GetDate())
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
SELECT     TOP (100) PERCENT dbo.CreateOrder.n_OrderOriginallyPlacedTime, dbo.Plu.Plu, dbo.Plu.ItemName, dbo.Plu.Price, dbo.t_OrderLines.n_Amount, dbo.t_OrderLines.n_OrderNum, 
                      dbo.t_OrderLines.n_Type, dbo.t_OrderLines.n_MenuId, dbo.t_OrderPayment.n_Order, dbo.t_OrderPayment.n_PayAmount 
FROM         dbo.t_OrderPayment FULL OUTER JOIN
                      dbo.CreateOrder ON dbo.t_OrderPayment.nStoreID = dbo.CreateOrder.nStoreID AND 
                      dbo.t_OrderPayment.n_Order = dbo.CreateOrder.n_OrderNum FULL OUTER JOIN
                      dbo.t_OrderLines ON dbo.CreateOrder.n_OrderNum = dbo.t_OrderLines.n_OrderNum FULL OUTER JOIN
                      dbo.Plu ON dbo.t_OrderLines.n_MenuId = dbo.Plu.n_MenuId
WHERE     (dbo.t_OrderLines.nStoreID = '528') AND (dbo.CreateOrder.nStoreID = '528') AND (dbo.CreateOrder.n_StatusFlags <> '6') AND 
                      (dbo.t_OrderLines.n_Type <> '26') AND (dbo.t_OrderLines.n_Type <> '18') AND(dbo.CreateOrder.n_OrderOriginallyPlacedTime BETWEEN 
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7))
 AND (dbo.t_OrderLines.n_MenuId <> '2719')
ORDER BY dbo.CreateOrder.n_OrderOriginallyPlacedTime

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
May not be the best solution, but it seems to work

declare @date datetime
set @date = GETDATE()

declare @lastWeekStarts datetime
declare @lastWeekEnds datetime

select @lastweekStarts = dateadd(hour, 8, floor(cast(dateadd(d, -datepart(weekday, @date) - 5, @date) as float)))
select @lastweekEnds = dateadd(hour, (24 * 7) - 1, @lastweekStarts)

select
@LastWeekStarts,  @LastWeekEnds
Try to use the attached code. It only works fine if you run only on Mondays.

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
SET @TodayDayOfWeek = datepart(dw, GetDate())
SET @EndOfPrevWeek = CONVERT(VARCHAR, DATEADD(dd, -(@TodayDayOfWeek-2), GetDate()), 112) + ' 07:00'
SET @StartOfPrevWeek = CONVERT(VARCHAR, DATEADD(dd, -(@TodayDayOfWeek+5), GetDate()), 112) + ' 08:00'
SELECT     TOP (100) PERCENT dbo.CreateOrder.n_OrderOriginallyPlacedTime, dbo.Plu.Plu, dbo.Plu.ItemName, dbo.Plu.Price, dbo.t_OrderLines.n_Amount, dbo.t_OrderLines.n_OrderNum,
                      dbo.t_OrderLines.n_Type, dbo.t_OrderLines.n_MenuId, dbo.t_OrderPayment.n_Order, dbo.t_OrderPayment.n_PayAmount
FROM         dbo.t_OrderPayment FULL OUTER JOIN
                      dbo.CreateOrder ON dbo.t_OrderPayment.nStoreID = dbo.CreateOrder.nStoreID AND
                      dbo.t_OrderPayment.n_Order = dbo.CreateOrder.n_OrderNum FULL OUTER JOIN
                      dbo.t_OrderLines ON dbo.CreateOrder.n_OrderNum = dbo.t_OrderLines.n_OrderNum FULL OUTER JOIN
                      dbo.Plu ON dbo.t_OrderLines.n_MenuId = dbo.Plu.n_MenuId
WHERE     (dbo.t_OrderLines.nStoreID = '528') AND (dbo.CreateOrder.nStoreID = '528') AND (dbo.CreateOrder.n_StatusFlags <> '6') AND
                      (dbo.t_OrderLines.n_Type <> '26') AND (dbo.t_OrderLines.n_Type <> '18') AND(dbo.CreateOrder.n_OrderOriginallyPlacedTime BETWEEN
@StartOfPrevWeek
AND
@EndOfPrevWeek
 AND (dbo.t_OrderLines.n_MenuId <> '2719')
ORDER BY dbo.CreateOrder.n_OrderOriginallyPlacedTime
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
FYI, this works any day of the week, but does assume your week starts on a Sunday (US Settings). If you need it to be more flexible, you will have to set (and restore DATEFIRST) or use a modulus and a calculation to determine what day of the week monday actually is.

Anyway, here is a working example in a loop, to show that it works for any day.

declare @date datetime
declare @lastWeekStarts datetime
declare @lastWeekEnds datetime

declare @counter int
set @counter = 0
while @counter < 15 begin
  set @date = dateadd(d, @counter, GETDATE())
  set @counter = @counter + 1

select @lastweekStarts = dateadd(hour, 8, floor(cast(dateadd(d, -datepart(weekday, @date) - 5, @date) as float)))
select @lastweekEnds = dateadd(hour, (24 * 7) - 1, @lastweekStarts)

select 
@LastWeekStarts,  @LastWeekEnds

end

Open in new window

Hi jmauel,

my information above was incorrect. My code works fine every day.

But I'm assuming the week starts on Sundays, therefore if you run my query on Sunday you won't get the results from full previous week. You'll get full week when you run the code from Mondays to Saturdays.
Top Expert 2011
Commented:
TRY

DECLARE @EndOfPrevWeek DateTime
. @StartOfPrevWeek DateTime
, @cdatefirst int
,@NOW DATETIME

select @cdatefirst=@@datefirst,@NOW=GETDATE()
set DATEFIRST 1
 
SET @StartOfPrevWeek = CONVERT(CHAR(8),DATEADD(dd, -(datepart(dw, @NOW)+6), @NOW),112)+' 08:00:00.000'

SET @EndOfPrevWeek = DATEADD(MS,-3,DATEADD(dd, 7, @StartOfPrevWeek))

SELECT     TOP (100) PERCENT OL.n_OrderOriginallyPlacedTime, OL.Plu, OL.ItemName,OL.Price, OL.n_Amount, OL.n_OrderNum,
                      OL.n_Type, OL.n_MenuId, OP.n_Order, OP.n_PayAmount

FROM dbo.t_OrderPayment as OP

 
Right OUTER JOIN (SELECT O.n_OrderOriginallyPlacedTime
                       ,P.Plu, P.ItemName, P.Price
                       ,OL.n_Amount, OL.n_OrderNum
                       ,OL.n_Type, OL.n_MenuId
                       ,ol.NSTOREID
                   FROM dbo.t_OrderLines AS ol
                  inner jOIN dbo.CreateOrder as O
                     ON O.n_OrderNum = OL.n_OrderNum
                   left OUTER JOIN dbo.Plu as P
                     ON OL.n_MenuId = P.n_MenuId
                  wHERE OL.nStoreID = '528'
                    AND O.nStoreID = '528'
                    AND OL.n_Type <> '26'
                    AND OL.n_Type <> '18'
                    AND OL.n_MenuId <> '2719'
                    AND O.n_StatusFlags <> '6'
                    AND O.n_OrderOriginallyPlacedTime BETWEEN
                        @STARTOFPREVWEEK AND @ENDOFPREVWEEK
                ) as OL
ON OP.nStoreID = OL.nStoreID
 AND OP.n_Order = OL.n_OrderNum

ORDER BY 1

SET dateFIRST @CDATEFIRST

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial