Ufdjrw

Hi I am trying to create a report that doesn't take into consideration weekends. Basically today plus 2 days excluding weekends. But I am having issues with it.

 Where [Location Code]='MO3TRAILS'
 and
 Case when datepart(weekday,[Shipment Date]+2)='1'then [Shipment Date]+4 
 else
 Case when datepart(weekday,[Shipment Date]+2)= '7' then [Shipment Date]+4 
 else
 [Shipment Date]+2 end end

I keep getting "An expression of non-boolean type specified in a context where a condition is expected, near 'end'" as an error message.

Plan on adding this in order to filter a report in visual studio for building orders. based on today. Here is the whole current query:

SELECT CASE WHEN h.[Sent To WMS] = '1' THEN 'YES' ELSE 'NO' END AS 
'Ready to Ship'
 , h.[Internal Comment]
 , CASE WHEN h.[Customer Price Group] <> 'INTERNAL' AND l.[No_] 
LIKE 'P-%' THEN l.[Gen_ Prod_ Posting Group] ELSE 
 CASE WHEN l.[No_] LIKE 'H-%' THEN l.[Gen_ Prod_ Posting Group] 
ELSE '' END END AS 'POS/Repack'
 , h.No_
 , h.[Ship-to Name]
 , h.[Bill-to Name]
 , l.[Location Code]
 , h.[Pool Load]
 , h.[Shipping Agent Code]
 , l.No_ AS 'SKU'
 , CAST(ROUND(l.[Quantity (Base)], 0) AS INT) AS 'QTY'
 , CAST(ROUND(m.[Qty_ per Unit of Measure], 0) AS INT) AS 'ITEMS 
PER PALLET'
 , CAST(CONVERT(varchar, h.[Shipment Date], 101) AS date) AS 'Slot 
Date'
 , h.[Ship-to County], ROUND(l.[Gross Weight], 0) AS 'Weight'
 , CASE WHEN l.[No_] LIKE 'F-00%' THEN FLOOR(l.[Quantity (Base)] / 
m.[Qty_ per Unit of Measure]) ELSE 0 END AS [Full Pallet]
 , CASE WHEN l.[No_] LIKE 'F-00%' THEN CAST(ROUND(l.[Quantity 
(Base)], 0) AS INT)- FLOOR(l.[Quantity (Base)] / m.[Qty_ per Unit of 
Measure]) * CAST(ROUND(m.[Qty_ per Unit of Measure], 0) AS INT) ELSE 0 END 
AS Picks
 , l.[Quantity (Base)] / m.[Qty_ per Unit of Measure] AS [Pallet 
Spots]

FROM DATASERVER.[Dynamics NAV].dbo.[Sales Header] AS 
h INNER JOIN
 DATASERVER.[Dynamics NAV].dbo.[Sales Line] AS l ON 
h.No_ = l.[Document No_] INNER JOIN
 DATASERVER.[Dynamics NAV].dbo.[Item Unit of Measure] 
AS m ON l.No_ = m.[Item No_]

WHERE (l.[Location Code] = 'MO3TRAILS') 
 AND (m.Code = 'PALLET') 
 AND (l.[Location Code] <> 'ECW') 
 AND (m.[Item No_] <> 'D-000006') 
 AND (m.[Item No_] <> 'D-000008')

ORDER BY 'Slot Date'

This is what I think you want to achieve. When today plus 2 days is a weekend then you want the entries with shipment date in 4 days and if today plus 2 days is a weekday you want the entries with shipment date in 2 days. If I am correct, the following Where statement should do the trick.

 WHERE 
[Shipment Date] =
 case when datepart(weekday,getdate() + 2) in (1,7) then getdate() + 4
 else getdate() + 2 end

This code is part of a WHERE clause, meaning everything here must resolve to a boolean "yes" or "no" result.

CASE statements create values. However, the SQL language does not have real boolean values; it only has comparison operators. Therefore, CASE by itself cannot produce a complete boolean expression; it can only produce the value used on one side of a comparison operator.

For this question, we have code structured like so:

WHERE column = value AND CASE

As just shown, this is not value. The result of the CASE expression must compare to something, e.g.:

 WHERE column = value AND CASE < column/value

Additionally, in producing those values, a CASE expression can have more than one WHEN section. There is no need to nest multiple CASE expressions here. Even better, us an IN() clause, then the first part of each WHEN is the same and the latter part is constant.

OK. So I finally got this sorted out to work properly using this:

 WHERE h.[Shipment Date]= CASE WHEN datepart(weekday,GETDATE()+2)IN (1) THEN (datediff(DAY,-4,getdate()))
 ELSE CASE WHEN datepart(weekday,GETDATE()+2)IN (7) THEN (datediff(DAY,-4,getdate())) ELSE (datediff(DAY,-2,getdate())) END END

Using this returned all rows that have a h.[Shipment Date] of 3/12/19 which is = getdate()+4.