I have this query that calculates the next possible shipping day, based on 3 conditions:- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green- There might be extra days (@xdays) required by the process - marked blue- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate from WORKCALENDARDATEwhere Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and (select SUM(WCD.WORKTIMECONTROL) from WORKCALENDARDATE as WCD where WCD.Transdate > @startday and WCD.WORKTIMECONTROL = 1 and WCD.Transdate >= WORKCALENDARDATE.Transdate) > @xdaysORDER BY Transdate
The table that has the information regarding shipping days has this structure:CollectiveShipment:DELIVERYDAY CUSTACCOUNT------------------------ 2 123456 4 123456 3 789101
So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456' for xml path('')),1,1,''))Anybody sees how to acchieve this?
use Stuff to define range of values?
Posted - 2014-11-13 : 07:16:12
