Show only past 3 Business Days

techsupport18 · May 7, 2018, 7:00pm

Hello,
So I thought this would show the past 3 BUSINESS / WORK days

BETWEEN DATEADD(DAY, CASE (DATEPART(WEEKDAY , GETDATE() - 2) + @@DATEFIRST) % 7 WHEN 1 THEN - 2 WHEN 2 THEN - 3 ELSE - 1 END, DATEDIFF(DAY, 0, GETDATE() - 2)) AND GETDATE() - 1

against

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END As BizDate

which gives a date so Sat. & Sun. are marked as Mon. which is what's needed. Nothing wrong here. Just the date range to show past 3 business days is the issue.

It's only giving Fri., 5/4 instead of Wed., 5/2, Thu., 5/3, Fri., 5/4

On Tue., it should show Thu., 5/3, Fri., 5/4, and Mon., 5/7, past 3 business days and so on.

Thu., does give Mon,, Tue., Wed., but not on Mon., Tue., & Wed.,

What will produce this?

bitsmed · May 7, 2018, 7:52pm

 where yourdatefield>=dateadd(day
                             ,case datepart(weekday,current_timestamp)
                                 when 1 then -4
                                 when 5 then -3
                                 when 6 then -3
                                 when 7 then -3
                                 else        -5
                              end
                             ,cast(current_timestamp as date)
                             )
   and yourdatefield<cast(current_timestamp as date)

techsupport18 · May 7, 2018, 8:20pm

Thank you

Getting a Data type error in expression

on this part:

>=dateadd(day, case datepart(weekday,current_timestamp) when 1 then -4 when 5 then -3 when 6 then -3 when 7 then -3 else -5 end, cast(current_timestamp as date))

bitsmed · May 7, 2018, 8:35pm

What's your Microsoft SQL Server version?
What's the data type of your field?

Does this work:

select current_timestamp

Does this work:

select dateadd(day
              ,case datepart(weekday,current_timestamp)
                  when 1 then -4
                  when 5 then -3
                  when 6 then -3
                  when 7 then -3
                  else        -5
               end
              ,cast(current_timestamp as date)
              )

techsupport18 · May 7, 2018, 8:36pm

Ah, yea Datepart doesn't work
it's 2008

current_timestamp results to Null values

bitsmed · May 7, 2018, 8:46pm

Both datepart and current_timestamp works from 2008 and up

techsupport18 · May 7, 2018, 8:47pm

Ok, getting Data type error in expression though

dateadd(day,case datepart(weekday,current_timestamp) when 1 then -4 when 5 then -3 when 6 then -3 when 7 then -3 else -5 end,cast(current_timestamp as date))

bitsmed · May 7, 2018, 8:51pm

What's the data type of your field?

You could try replacing current_timestamp with getdate()

techsupport18 · May 7, 2018, 8:53pm

Yes, tried it with GetDate.
Still not working

the field is a translated one

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END As BizDate

yosiasz · May 7, 2018, 8:58pm

curly brackets { ?
No ELSE in the case statement?

techsupport18 · May 7, 2018, 9:02pm

?

the As BizDate works as expected
trying to add a filter for past 3 business days to produce as described

bitsmed · May 7, 2018, 9:08pm

What's your database compatibility level?

select compatibility_level
  from sys.databases

and what's the data type of dbo.SO_SalesOrderHistoryHeader.OrderDate?

yosiasz · May 7, 2018, 9:10pm

are you using Microsoft Sql Server? never seen
DAYSADDNOWK

yosiasz · May 7, 2018, 9:12pm

using bitsmed code with sample data

create table #techsupport(OrderDate  datetime)

;with cte
as
(
select getdate() as OrderDate
)
insert into #techsupport
select OrderDate - object_id
  from cte 
 cross apply (select object_id  
from sys.objects where object_id <= 100 ) datesgalore

 select * 
   From #techsupport
   where OrderDate>=dateadd(day
                             ,case datepart(weekday,current_timestamp)
                                 when 1 then -4
                                 when 5 then -3
                                 when 6 then -3
                                 when 7 then -3
                                 else        -5
                              end
                             ,cast(current_timestamp as date)
                             )
   and OrderDate<cast(current_timestamp as date)

 drop table  #techsupport

techsupport18 · May 7, 2018, 9:19pm

says compatibility_level
100

DateTime
reason for part of the translation, remove the time and to handle weekends

bitsmed · May 7, 2018, 9:26pm

So my first suggestion should work if you do it like @yosiasz showed you

techsupport18 · May 7, 2018, 10:13pm

Still getting the same error message

dateadd(day,case datepart(weekday,GetDate()) when 1 then -4 when 5 then -3 when 6 then -3 when 7 then -3 else -5 end,cast(GetDate() as date))
>=dateadd(day,case datepart(weekday,current_timestamp) when 1 then -4 when 5 then -3 when 6 then -3 when 7 then -3 else -5 end,cast(current_timestamp as date)) and OrderDate<cast(current_timestamp as date)

Entered another way and it did this

(DATEADD(day, 
                         CASE datepart(weekday, GetDate()) WHEN 1 THEN - 4 WHEN 5 THEN - 3 WHEN 6 THEN - 3 WHEN 7 THEN - 3 ELSE - 5 END, CAST(GETDATE() AS date)) >= DATEADD(day, CASE datepart(weekday, 
                         CURRENT_TIMESTAMP) WHEN 1 THEN - 4 WHEN 5 THEN - 3 WHEN 6 THEN - 3 WHEN 7 THEN - 3 ELSE - 5 END, CAST(CURRENT_TIMESTAMP AS date))) AND 
                         (dbo.SO_SalesOrderHeader.OrderDate < CAST(CURRENT_TIMESTAMP AS date))

however it is not just showing past 3 business days, it's showing all records

Here's the entire SQL

SELECT PERCENT YEAR(CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, 
                         dbo.SO_SalesOrderHeader.OrderDate)) END) AS OrderYear, 'Q' + CONVERT(VARCHAR(20), DATEPART(qq, CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) 
                         THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) END)) AS Quarter, 
                         MONTH((CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) 
                         END)) AS OrderMonth, DATENAME(month, (CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, 
                         dbo.SO_SalesOrderHeader.OrderDate)) END)) AS MonthName, { fn WEEK((CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) 
                         ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) END)) } AS Week, { fn DAYOFWEEK((CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) 
                         THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) END)) } AS DayOfWeek, DATEDIFF(d, 
                         (CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) END) + 1, 
                         GETDATE()) AS NoOfDays, CASE WHEN { fn DAYOFWEEK(OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) 
                         END AS OrderDate, DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHeader.OrderDate)) AS ActualOrderDate, dbo.SO_SalesOrderDetail.ItemCode, 
                         CASE WHEN UDF_CATEGORY_SPECIFIC = '' THEN 'Not Labeled' ELSE UDF_CATEGORY_SPECIFIC END AS SpecificCategory, 
                         CASE WHEN UDF_CATEGORY_GENERAL = '' THEN 'Not Labeled' ELSE UDF_CATEGORY_GENERAL END AS Category, CASE WHEN UDF_MACHINE = '' THEN 'Not Labeled' ELSE UDF_MACHINE END AS Machine, 
                         dbo.SO_SalesOrderDetail.QuantityOrdered - dbo.SO_SalesOrderDetail.QuantityShipped AS QOO, dbo.SO_SalesOrderDetail.ExtensionAmt AS Amt, dbo.SO_SalesOrderHeader.OrderStatus
FROM            dbo.SO_SalesOrderDetail INNER JOIN
                         dbo.SO_SalesOrderHeader ON dbo.SO_SalesOrderDetail.SalesOrderNo = dbo.SO_SalesOrderHeader.SalesOrderNo INNER JOIN
                         dbo.CI_Item ON dbo.SO_SalesOrderDetail.ItemCode = dbo.CI_Item.ItemCode
WHERE        (CASE WHEN UDF_CATEGORY_SPECIFIC = '' THEN 'Not Labeled' ELSE UDF_CATEGORY_SPECIFIC END <> 'WHOLESALE') AND 
                         (CASE WHEN UDF_CATEGORY_SPECIFIC = '' THEN 'Not Labeled' ELSE UDF_CATEGORY_SPECIFIC END <> '') AND 
                         (CASE WHEN UDF_CATEGORY_SPECIFIC = '' THEN 'Not Labeled' ELSE UDF_CATEGORY_SPECIFIC END <> 'Not Labeled') AND (NOT (dbo.SO_SalesOrderHeader.OrderStatus IN ('C', 'Z', 'X'))) AND (DATEADD(day, 
                         CASE datepart(weekday, GetDate()) WHEN 1 THEN - 4 WHEN 5 THEN - 3 WHEN 6 THEN - 3 WHEN 7 THEN - 3 ELSE - 5 END, CAST(GETDATE() AS date)) >= DATEADD(day, CASE datepart(weekday, 
                         CURRENT_TIMESTAMP) WHEN 1 THEN - 4 WHEN 5 THEN - 3 WHEN 6 THEN - 3 WHEN 7 THEN - 3 ELSE - 5 END, CAST(CURRENT_TIMESTAMP AS date))) AND 
                         (dbo.SO_SalesOrderHeader.OrderDate < CAST(CURRENT_TIMESTAMP AS date))

yosiasz · May 7, 2018, 10:27pm

you need to provide sample data/

create table #SO_SalesOrderDetail 

create table #SO_SalesOrderHeader 

create table #CI_Item 

insert into #SO_SalesOrderDetail

etc

techsupport18 · May 7, 2018, 11:05pm

provided the wrong SQL

SELECT  OrderYear, Quarter, OrderMonth, MonthName, Week, DayOfWeek, NoOfDays, OrderDate, ActualOrderDate, ItemCode, SpecificCategory, Category, Machine, QOO, Amt
FROM            dbo.vAE_OpenOrders
WHERE        (OrderDate BETWEEN DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE() - 2) + @@DATEFIRST) % 7 WHEN 1 THEN - 2 WHEN 2 THEN - 3 ELSE - 1 END, DATEDIFF(DAY, 0, GETDATE() - 2)) AND GETDATE() 
                         - 1)

Results in 5/4/2018
need to display past 3 business days
so need 5/2, 5/3, 5/4

2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	SUB	BLANKETS	HOME DÉCOR	Reggiani	1.000000	20.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	T156	TABLECLOTHES	EXHIBITION	Reggiani	2.000000	170.00
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	T156	TABLECLOTHES	EXHIBITION	Reggiani	1.000000	85.00
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	T132	TABLECLOTHES	EXHIBITION	Reggiani	1.000000	72.00
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	T132	TABLECLOTHES	EXHIBITION	Reggiani	12.000000	804.00
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	2.000000	45.00
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50
2018	Q2	5	May	18	6	2	2018-05-04 00:00:00.000	2018-05-04 00:00:00.000	HW35	BANNERS	EXHIBITION	FLORA	1.000000	22.50

yosiasz · May 7, 2018, 11:49pm

please provide data as DML and DDL

create table #SO_SalesOrderDetail 

create table #SO_SalesOrderHeader 

create table #CI_Item 

insert into #SO_SalesOrderDetail 
select 2018 ,'Q2'  ---etc