Case When with date range, adding additional Logic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case When with date range, adding additional Logic

Author

Topic

tilee001
Starting Member

4 Posts

Posted - 2015-03-09 : 00:14:21

I've just entered a new position with really demanding deadlines. I'm still trying to rap my head around all the new business logic. And then, I'm task with this request.

This is a part of a much bigger tsql logic. The request is to add in the logic that "if the calculated Delivery Time is 1-30 mins after the Schedule Time (Late), the Final Adjusted Arrival Time will be set to equal the Scheduled Time." This is to compensate for a potential 30 mins of lunch grace period.

Any help is greatly appreciative.

SELECT

CASE bd.IsOverride
WHEN 'Yes' THEN bd.AdjustedArrivalTime
ELSE COALESCE(sd.MinScan, bd.AdjustedArrivalTime) END AdjustedArrivalTime

, bd.stp_schdtearliest ScheduledEarliestTime

, CASE WHEN bd.IsOverride = 'Yes' THEN
CASE WHEN bd.AdjustedArrivalTime > bd.stp_schdtearliest
THEN 'Yes'
ELSE 'No' END
WHEN COALESCE(sd.MinScan, bd.AdjustedArrivalTime) > bd.stp_schdtearliest
THEN 'Yes'
ELSE 'No' END DeliveryLateActual

, CASE WHEN ScheduledTime = 'Offhours' AND ( DATEPART(HOUR, COALESCE(sd.MinScan, bd.AdjustedArrivalTime)) BETWEEN 0 AND 6
OR DATEPART(HOUR, COALESCE(sd.MinScan, bd.AdjustedArrivalTime)) BETWEEN 18 AND 23)
THEN 'No'
WHEN bd.IsOverride = 'Yes' THEN
CASE WHEN bd.AdjustedArrivalTime > bd.stp_schdtearliest
THEN 'Yes'
ELSE 'No' END
WHEN COALESCE(sd.MinScan, bd.AdjustedArrivalTime) > bd.stp_schdtearliest
THEN 'Yes'
ELSE 'No' END DeliveryLatePerceived

, CASE WHEN bd.IsOverride = 'Yes' THEN
CASE WHEN bd.AdjustedArrivalTime > bd.stp_schdtearliest
THEN DATEDIFF(MINUTE, bd.AdjustedArrivalTime, bd.stp_schdtearliest)
ELSE NULL END
WHEN COALESCE(sd.MinScan, bd.AdjustedArrivalTime) > bd.stp_schdtearliest
THEN DATEDIFF(MINUTE, COALESCE(sd.MinScan, bd.AdjustedArrivalTime), bd.stp_schdtearliest)
ELSE NULL END HowLateActual

, CASE WHEN bd.IsOverride = 'Yes' THEN
CASE WHEN bd.AdjustedArrivalTime > bd.stp_schdtearliest
THEN NULL
ELSE DATEDIFF(MINUTE, bd.AdjustedArrivalTime, bd.stp_schdtearliest) END
WHEN COALESCE(sd.MinScan, bd.AdjustedArrivalTime) > bd.stp_schdtearliest
THEN NULL
ELSE DATEDIFF(MINUTE, COALESCE(sd.MinScan, bd.AdjustedArrivalTime), bd.stp_schdtearliest) END HowEarlyActual

FROM #BigData bd
LEFT OUTER JOIN #ScanData sd on bd.TripStopID=sd.TripStopID
ORDER BY bd.stp_number

TTL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-09 : 11:15:45

Do you mean you want to change this part:

CASE bd.IsOverride
WHEN 'Yes' THEN bd.AdjustedArrivalTime
ELSE COALESCE(sd.MinScan, bd.AdjustedArrivalTime) END AdjustedArrivalTime

to add the additional logic?

What columns hold the Delivery Time and Schedule Time?

tilee001
Starting Member

4 Posts

Posted - 2015-03-09 : 13:38:11

Yes, I believe that's it.

I believe the delivery is technically the AdjustedArrivalTime and the scheduled time is bd.stp_schdtearliest=ScheduledEarliestTime

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-09 : 14:00:32

and how do the new requirements relate to the test for bd.IsOverride in that case statement?

tilee001
Starting Member

4 Posts

Posted - 2015-03-09 : 22:17:27

I was enformed that if the AdjustedArrivalTime is within 0-30 mins of the ScheduledEarliestTime, then it the ScheduledEarliestTime should be overwritten as AdjustedArrivalTime = ScheduledEarliestTime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources