Where CreatedOn = dateadd(day, -1, getdate()) {exclude weekend}

Question

Post reply

Where CreatedOn = dateadd(day, -1, getdate()) {exclude weekend}

iklektic

Ten Centuries

Points: 1236
More actions
June 18, 2010 at 9:56 am

#139475

Hi all, I have a report with a nested parameter where the WHERE statement incorporates a date selector.
My question is, how can I make this statement ignore Saturdays and Sundays and jump to the Friday prior?
EG. I run the report on a monday, I would like the results returned to be Fridays results...
Thank you!!
Also, I will eventually want to exclude holidays as well. This site is a great help for me. Thanks again.
~ iklektic

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

declare @dt_today datetime,

@dt_theDateIWant datetime

select @dt_today = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

select @dt_theDateIWant = case DATEPART(DW,@dt_today)

when 2 then DATEADD(dd, -3, @dt_today)

when 1 then DATEADD(dd, -2, @dt_today)

else DATEADD(dd, -1, @dt_today) end

select somedata

from sometable

where somedatefield = @dt_theDateIWant

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

iklektic Ten Centuries Points: 1236 More actions · Answer 2

Alright, I apologize. I should have incorporated the code... was in a hurry and didn't have it in front of me... but here it is:

SELECT COUNT(DISTINCT AllWorkOrderItemReportView.WorkOrderID) AS TotalOrders,

LineItemServiceReportView.WorkOrderCreditGroupOwnerStaffName AS Staff, Staff.Department

FROM AllWorkOrderItemReportView INNER JOIN

LineItemServiceReportView ON

AllWorkOrderItemReportView.LineItemServiceReportView_LineItemServiceID = LineItemServiceReportView.LineItemServiceID INNER JOIN

Staff ON LineItemServiceReportView.WorkOrderCreditGroupOwnerStaffID = Staff.StaffID

WHERE /*I need this parameter to exclude weekends and also holidays for which I have a table created called dbo.holidays*/(dbo.dateonly(AllWorkOrderItemReportView.CreatedOn) = DATEADD(DAY, -1, dbo.dateonly(getdate()))) AND

(AllWorkOrderItemReportView.AllWorkOrderComponentView_IsCancelled = 0) AND (AllWorkOrderItemReportView.OrderSourceID <> '21')

GROUP BY LineItemServiceReportView.WorkOrderCreditGroupOwnerStaffName, Staff.Department

ORDER BY Staff.Department, TotalOrders DESC

Thank you in advance for your help!

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

Add this above your code

declare @dt_today datetime,

@dt_theDateIWant datetime

select @dt_today = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

select @dt_theDateIWant = case DATEPART(DW,@dt_today)

when 2 then DATEADD(dd, -3, @dt_today)

when 1 then DATEADD(dd, -2, @dt_today)

else DATEADD(dd, -1, @dt_today) end

Then, replace your

= DATEADD(DAY, -1, dbo.dateonly(getdate()))

with

= @dt_theDateIWant

and you're done. You don't want to put a case in your where clause. It is better to figure out the date you're trying to get, then assign that to a variable, then use that variable in your where statement.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

iklektic Ten Centuries Points: 1236 More actions · Answer 4

Thank you @bt. Your help is greatly appreciated!

I was able to incorporate your query into my report.

~ iklektic

iklektic Ten Centuries Points: 1236 More actions · Answer 5

Thanks again, I was able to accomplish it by placing the CASE statement into the query itself.

SELECT COUNT(DISTINCT AllWorkOrderItemReportView.WorkOrderID) AS TotalOrders,

LineItemServiceReportView.WorkOrderCreditGroupOwnerStaffName AS Staff, Staff.Department

FROM AllWorkOrderItemReportView INNER JOIN

LineItemServiceReportView ON

AllWorkOrderItemReportView.LineItemServiceReportView_LineItemServiceID = LineItemServiceReportView.LineItemServiceID INNER JOIN

Staff ON LineItemServiceReportView.WorkOrderCreditGroupOwnerStaffID = Staff.StaffID

WHERE (dbo.DateOnly(AllWorkOrderItemReportView.CreatedOn) = CASE DATEPART(DW, dbo.dateonly(getdate())) WHEN 2 THEN DATEADD(dd, - 3,

dbo.dateonly(getdate())) WHEN 1 THEN DATEADD(dd, - 2, dbo.dateonly(getdate())) ELSE DATEADD(dd, - 1, dbo.dateonly(getdate())) END) AND

(AllWorkOrderItemReportView.AllWorkOrderComponentView_IsCancelled = 0) AND (AllWorkOrderItemReportView.OrderSourceID <> '21')

GROUP BY LineItemServiceReportView.WorkOrderCreditGroupOwnerStaffName, Staff.Department

ORDER BY Staff.Department, TotalOrders DESC

The reason I needed it to be in the query itself is because this is going to be a nested query in a report that I'm creating (I'm not sure I'm using the correct term) with multiple graphs/tables. kind of like a dashboard... but ... not...

DW - does this represent the 'day of the week'?

~ iklektic