will this work as this is the actual query
SELECT o.OrderID,
o.ExternalOrderID,
TRIM(ISNULL(o.CustFn, '') + ' ' + ISNULL(o.CustLN, '')) as CustomerName,
p.Name as PartnerName,
CASE
WHEN (o.BrandName IS NOT NULL)
THEN o.BrandName
ELSE s.StoreName
END AS StoreName,
a.AreaName,
o.ZoneName,
o.PickupAddress,
o.MerchantZipCode as 'PickupZip',
o.DropoffAddress,
****CAST(DATEADD(HOUR,-6,j.JobStarted) AS varchar) AS 'DelWindowStart', this are the one that I need to get them correct for me as 02/01/21
CAST(DATEADD(HOUR,-6,j.JobEnded) AS varchar) AS 'DelWindowEnd',**** this are the one that I need to get them correct for me as 02/01/21
CAST(DATEADD(HOUR,-6,o.DriverArriveTime) AS varchar) as 'DriverArriveTime',
DATEDIFF(minute, j.JobStarted, o.DriverArriveTime) as 'ArrivalDrift',
CAST(DATEADD(HOUR,-6,o.DriverOutForDeliveryTime) AS varchar) AS 'PickTime', --CASE WHEN o.DriverOutForDeliveryTime IS Not NULL THEN o.DriverOutForDeliveryTime ELSE jo.PickTime END AS 'PickTime' ,
DATEDIFF(minute, o.DriverArriveTime, o.DriverOutForDeliveryTime) as 'DriverWaitTime',
CAST(DATEADD(HOUR,-6,jo.DropTime) AS varchar) as DropTime,
DATEDIFF(minute, j.JobEnded, jo.DropTime) as 'DeliveryDrift',
j.Status,
ROUND(ISNULL(((SELECT TotalDistance From Jobs Where JobId = j.JobId) / 1609.344),0),2) as 'TotalMiles',
ROUND(ISNULL(j.MileageRate, 0),2) as 'MileageRate',
(ROUND(ISNULL(((SELECT TotalDistance From Jobs Where JobId = j.JobId) / 1609.344),0),2) * ROUND(ISNULL(j.MileageRate, 0),2)) as 'TotalMileageRate',
ROUND(ISNULL(j.BaseRate, 0),2) as 'BaseRate',
ROUND(ISNULL(j.GuaranteeRate, 0),2) as 'GuaranteeRate',
ROUND(ISNULL(j.DeliveryFee, 0),2) as 'DeliveryFee',
ROUND(ISNULL(j.MileageFee, 0),2) as 'MileageFee',
ROUND(ISNULL(j.OtherFee, 0),2) as 'OtherFee',
ROUND(ISNULL(j.ReturnFee, 0),2) as 'ReturnFee',
ROUND(ISNULL(j.PeakHourRate, 0),2) as 'PeakHourRate',
ROUND(ISNULL(j.CancellationFee, 0),2) as 'CancellationFee',
ROUND(ISNULL(j.ExtraRate, 0),2) as 'Tips',
ROUND(ISNULL((select sum(ot.CalculatedTip) from OrderTips as ot where ot.Orderid = o.OrderId), 0.00),2) as 'PostTips',
convert(varchar(50),jo.DelayTime) as DelayTime,
jo.DelayReason,
CASE
WHEN o.IsOrderReturned=1 THEN 'True'
ELSE 'False'
END as IsOrderReturned,
CASE
WHEN o.ActualDeliveryTime IS NULL THEN 'No Info'
WHEN o.ActualDeliveryTime > o.DelWindowEnd THEN 'Late'
WHEN o.ActualDeliveryTime < o.DelWindowStart THEN 'Early'
WHEN o.ActualDeliveryTime BETWEEN o.DelWindowStart AND o.DelWindowEnd THEN 'Ontime'
ELSE 'Other'
END AS OnTimeDeliveryFlag,
o.IsSkipcartCancelled as 'IsAdminCanceled',
o.IsRetailerCancelled as 'IsRetailerCancelled',
o.DescriptionOfRetailerCancel as 'OrderStateAtRetailerCancel',
(SELECT top 1 ExceptionalEvent FROM JobEvents WHERE OrderId = O.OrderID and EventName = 'close_order') as 'ReasonCode',
TRIM(ISNULL(d.FirstName, '') + ' ' + ISNULL(d.LastName, '')) as DriverName,
-- o.NoOfBags,
-- jo.NoOfBags as NoOfBagsPicked,
-- o.OrderStatus
ISNULL(o.BillingDeliveryFee,0) as 'BillingDeliveryFee',
ISNULL(o.BillingReturnFee,0) as 'BillingReturnFee',
ISNULL(o.BillingCancellationFee,0) as 'BillingCancellationFee',
ISNULL(o.BillingMileageFee,0) as 'BillingMileageFee',
ISNULL(o.BillingTip,0) as 'BillingTip'
FROM Orders o WITH (NOLOCK)
LEFT JOIN JobOrders jo WITH (NOLOCK) ON o.OrderID = jo.OrderId and jo.IsArchived = 0
LEFT JOIN Jobs j WITH (NOLOCK) ON jo.JobId = j.JobId
LEFT JOIN Drivers d ON J.DriverId = d.DriverId
LEFT JOIN Store s WITH (NOLOCK) ON s.StoreId = o.StoreID
LEFT JOIN Partner p WITH (NOLOCK) ON p.PartnerId = o.PartnerId
LEFT JOIN Regions r WITH (NOLOCK) ON o.RegionId = r.RegionId
LEFT JOIN OrderArea oa WITH (NOLOCK) ON o.OrderID = oa.OrderId
LEFT JOIN Areas a WITH (NOLOCK) ON oa.AreaId = a.AreaId
WHERE o.DelWindowStart BETWEEN '02/01/2021'and '02/28/2021'
and (j.Status ='cancelled' or j.Status='delivered')
GROUP BY o.OrderID,
o.ExternalOrderID,
o.CustFn,
o.CustLN,
a.AreaName,
o.ZoneName,
o.PickupAddress,
o.MerchantZipCode,
o.DropoffAddress,
j.JobStarted,
j.JobEnded,
s.StoreName,
-- o.NoOfBags,
-- jo.NoOfBags,
--- o.OrderStatus,
jo.DelayReason,
o.IsOrderReturned,
j.Status,
jo.PickTime,
jo.DropTime,
jo.DelayTime,
j.JobId,
j.MileageFee,
j.BaseRate,
j.GuaranteeRate,
j.DeliveryFee,
j.OtherFee,
j.ReturnFee,
j.PeakHourRate,
j.CancellationFee,
j.ExtraRate,
o.Tip,
d.FirstName,
d.LastName,
j.MileageRate,
p.Name,
o.BrandName,
o.IsSkipcartCancelled,
o.DriverArriveTime,
o.DriverOutForDeliveryTime,
o.ActualDeliveryTime,
o.DelWindowStart,
o.DelWindowEnd,
o.IsRetailerCancelled,
o.DescriptionOfRetailerCancel,
o.ExternalOrderID,
o.BillingDeliveryFee,
o.BillingReturnFee,
o.BillingCancellationFee,
o.BillingMileageFee,
o.BillingTip
ORDER BY p.Name, DelWindowStart