# Simplifying a Complex Nested Expression - Hold on Tight

Good afternoon,
I have a relatively complex BAQ that is designed to give me an idea about when a job needs to be pushed to Engineering. It is working as expected. There are several factors that go into this.

1. The Customer Required Due Date. JobHead.ReqDue.
2. The date our sales department wants to ship by to meet goals. JobHead.Date08.
3. The soonest unlinked release date for the part. This helps catch cases when the demand is not linked to a job. The part number is the only match between Order and Job, no demand link is established.
4. The number of Extra Days that Engineering requests per job. JobHead.Number10 (QDays)

I return all three dates (CustReqDue, SalesShipBy, FirsUnlinkedRel) to the BAQ and then I do some work using those dates. I want to determine the optimal date to send the job to engineering based on each date. So, I just back up the number of QDays from the Req Date. Obviously, this isnâ€™t enough. There is also all the time it takes to run the job. In my BAQ this is calculated as OpDiffDays = JobHead.ReqDate - JobHead.StartDate. This is just the number of days the job is schedule to run. In the end, I use both the QDays and the OpDiffDays to figure out the various dates to pull to engineering.

If there is not a Sales Ship By Date or an Unlinked Release Date, then the engineering pull date is just JobHead.StartDate - QDays. Since we donâ€™t want to count the weekends, I try to convert QDays into working days from the StartDate.

I did all this in several steps in my calculated fields, then I tried to merge them all together to make a simpler expression.

Here are my intermediate steps:
int OpDiffDays =

``````DateDiff(DD, JobHead.StartDate, JobHead.DueDate)
``````

date WorkingQDays =

``````WHEN DATENAME(WEEKDAY, JobHead.ReqDueDate ) = 'Sunday'
END
``````

date Test =

``````CASE
THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.ReqDueDate)
THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.ReqDueDate)
END
``````

date Test2 =

``````CASE
WHEN DATEPART(WEEKDAY, TestNoWeekends) = 1 -- Sunday
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), TestNoWeekends)
WHEN DATEPART(WEEKDAY, TestNoWeekends) = 7 -- Saturday
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), TestNoWeekends)
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), TestNoWeekends)
END
``````

date TestNoWeekends =

``````iif(DATEPART(WEEKDAY, Test) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, Test) = 7 THEN -1 ELSE -2 END, Test),Test)
``````

date RelToEngDate =

``````iif(DATEPART(WEEKDAY, Test2) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, Test2) = 7 THEN -1 ELSE -2 END, Test2),Test2)
``````

If there is a Sales Ship By Date, or an Unlinked Release Date, then the engineering pull date is calculated using either of those dates as the start date and subtracting the working QDays and OpDiffDays.

In order to replicate these steps using the two other fields as the start date (Date08 and FirstUnlinkedRelesaseDate), I have to repeat all those calculated fields. This quickly pops an error in the BAQ designer about too many expressions.

So, I just worked backwards from the RelToEng calculated field and replaced each calculated field with the calculation. These two huge expressions do the same as the above expressions, just using the other two dates as the start dates:

``````iif(DATEPART(WEEKDAY, CASE
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 1 -- Sunday
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 7 -- Saturday
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
END) IN (1, 7),
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 1 -- Sunday
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 7 -- Saturday
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
END) = 7 THEN -1 ELSE -2 END, CASE
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 1 -- Sunday
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 7 -- Saturday
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
END),CASE
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 1 -- Sunday
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END)) = 7 -- Saturday
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) IN (1, 7),
DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END) = 7 THEN -1 ELSE -2 END, CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END),CASE WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) WHEN DATEPART(WEEKDAY, OpenReleasesByPart.Calculated_FirstDate) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), OpenReleasesByPart.Calculated_FirstDate) END))
END)
``````
Calc RelToEngDate from SalesShipBy (Date08)
``````iif(DATEPART(WEEKDAY, CASE
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
END) IN (1, 7),
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
END) = 7 THEN -1 ELSE -2 END, CASE
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
END),CASE
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 2) + ((OpDiffDays + 2) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
WHEN DATEPART(WEEKDAY, iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
THEN DATEADD(DAY, -((OpDiffDays + 1) + ((OpDiffDays + 1) / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
ELSE DATEADD(DAY, -(OpDiffDays + (OpDiffDays / 5 * 2)), iif(DATEPART(WEEKDAY, CASE WHEN DATEPART(WEEKDAY, JobHead.Date08) = 1 THEN DATEADD(DAY, -((QDays + 2) + ((QDays + 2) / 5 * 2)), JobHead.Date08) WHEN DATEPART(WEEKDAY, JobHead.Date08) = 7 THEN DATEADD(DAY, -((QDays + 1) + ((QDays + 1) / 5 * 2)), JobHead.Date08) ELSE DATEADD(DAY, -(QDays + (QDays / 5 * 2)), JobHead.Date08) END) IN (1, 7),
END)
``````

Man, those are mess! I canâ€™t believe you even tried to look at them!
I want to simplify this BAQ and these huge expressions are getting in my way! Do you have any ideas for trimming this down to a readable expression?
Nate

Something came to mindâ€¦

What if you make a Recursive CTE:

``````WITH RecursiveDates AS (
SELECT StartDate, EndDate
FROM Erp.FiscalPer
WHERE StartDate IS NOT NULL AND EndDate IS NOT NULL
AND Company = 'WCI'
AND FiscalYear >= YEAR(GETDATE()) - 1

UNION ALL

SELECT DATEADD(DAY, 1, StartDate) AS StartDate, EndDate
FROM RecursiveDates
WHERE DATEADD(DAY, 1, StartDate) <= EndDate
)
SELECT StartDate AS GeneratedDate
FROM RecursiveDates
WHERE DATEPART(WEEKDAY, StartDate) NOT IN (1, 7)  -- Exclude Sundays (1) and Saturdays (7)
ORDER BY StartDate;
``````

This will give you a table with business datesâ€¦ I wonder if you can use `MIN(CASE WHEN JobHead.DueDate >= GeneratedDate THEN GenerateDate ELSE NULL)`

Perhaps Join on the CTE or maybe use it as an Inner-SubQuery, not sure just an idea, which also opens you up to the ability to at some point incorporate ProdCal into FiscalPer, what if you do work 7 days a week, what about holidays etcâ€¦

2 Likes

You all are just bound and determined to get me to learn CTEs! Seriously though, this is an interesting approach. I like it. I will see what I can come up with. Thank you!

If I understand correctly, the recursive CTE will return a list of weekdays between start and end dates. This should eliminate the need to account for weekends inside every case statement. I like the idea of being able to pull in holidays at some point. How would you tweak your recursive CTE to exclude holidays and weekends? I guess you would have to pull in the production calendar instead of the fiscal calendar, right?