metropia
asked on
calculate percentage based on dates met
hello folks,
I am wondering if anyone could offer some help in a calculation based on two dates.
I have a date field called: requested delivery date, and another date called: Actual Delivered Date
I need to compare them and calculate the % based on how close, or far the Actual Delivered Date (when product actually arrived to customer) is from the Requested Delivery Date (requested by the customer).
I am wondering if anyone could offer some help in a calculation based on two dates.
I have a date field called: requested delivery date, and another date called: Actual Delivered Date
I need to compare them and calculate the % based on how close, or far the Actual Delivered Date (when product actually arrived to customer) is from the Requested Delivery Date (requested by the customer).
Give us some sample data + expected output of what you're trying to pull off here.
ASKER
here is some data attached.
if order was delivered on the requested delivery date it means work_completed 100%
if
then in item was not delivered on the date show percentage of how close or far from the requested delivery date, 70 % or 80% or 90% an so on....
salesOrders.xlsx
if order was delivered on the requested delivery date it means work_completed 100%
if
then in item was not delivered on the date show percentage of how close or far from the requested delivery date, 70 % or 80% or 90% an so on....
salesOrders.xlsx
ASKER
I tried this:
But for rows with dates like:
Requested Delivery Date
2013-04-23 00:00:00.000
Actual Delivery Date
2013-04-21 00:00:00.000
I get:
-0.547945205479452
Another one is
Requested Delivery Date
2013-06-26 00:00:00.000
Actual Delivery Date
2013-04-28 00:00:00.000
It gives me:
-0.161643835616438360
But it should be positive because it was delivered way before the Requested Delivery Date
And when the actual delivery date equals the requested delivery date, I get 0%, but is should be 100%
(DATEDIFF(DAY, [RequestedDeliveryDate], [ActualDeliveryDate]) / CAST(365 AS FLOAT)) * 100
But for rows with dates like:
Requested Delivery Date
2013-04-23 00:00:00.000
Actual Delivery Date
2013-04-21 00:00:00.000
I get:
-0.547945205479452
Another one is
Requested Delivery Date
2013-06-26 00:00:00.000
Actual Delivery Date
2013-04-28 00:00:00.000
It gives me:
-0.161643835616438360
But it should be positive because it was delivered way before the Requested Delivery Date
And when the actual delivery date equals the requested delivery date, I get 0%, but is should be 100%
try this.
;WITH CTE AS (SELECT *,DATEDIFF(DD,RequestedDeliveryDate,ActualDeliveryDate) Diff
FROM Test),
CTE1 AS (SELECT *,
MAX(Diff) OVER (PARTITION BY 1) Max_Diff,
MIN(Diff) OVER (PARTITION BY 1) Min_Diff
FROM CTE)
SELECT ItemNumber,RequestedDeliveryDate,ActualDeliveryDate,
ROUND((1-(Diff*1.0/CASE WHEN Diff < 0 THEN Min_Diff ELSE Max_Diff END))*100,0) Close_Far
FROM CTE1
http://sqlfiddle.com/#!3/72114a/12
ASKER
is there a way to do this in an already existing query? I am not sure how to add your example to my stored procedure query
ASKER
SELECT
[s].[ChainName]
, [s].[OrderNumber]
, [s].[ItemNumber]
, [s].[ItemDescription]
, [s].[OrderDate]
, [s].[RequestedDeliveryDate]
, [s].[PromisedDeliveryDate]
, [s].[PostingDate]
, [s].[ActualDeliveryDate]
, (DATEDIFF(DAY, [ActualDeliveryDate], [RequestedDeliveryDate]) / CAST(365 AS FLOAT))
AS [ReqDeliveryDateMet%]
, [s].[QuantityOrdered]
, [s].[QuantityDelivered]
, DATEDIFF(d, s.OrderDate, s.ActualDeliveryDate) AS [LeadTime_NumberOfDays]
FROM
(
SELECT
so.[Bill-to Name] AS [ChainName]
, so.No_ AS [OrderNumber]
, so.[Version No_]
, sol.[No_] AS [ItemNumber]
, sol.[Description] AS [ItemDescription]
, sol.[Order Date] AS [OrderDate]
, sol.[Requested Delivery Date] AS [RequestedDeliveryDate]
, sol.[Promised Delivery Date] AS [PromisedDeliveryDate]
, so.[Posting Date] AS [PostingDate]
, DATEADD(DAY, CONVERT(INT, REPLACE(so.[Shipping Time], CHAR(2), '')), so.[Posting Date])
AS [ActualDeliveryDate]
, sol.Quantity AS [QuantityOrdered]
, sol.[Quantity Shipped] AS [QuantityDelivered]
FROM
SalesOrders so LEFT OUTER JOIN
SalesOrderLine sol ON
so.[No_] = sol.[Document No_] AND
so.[Version No_] = sol.[Version No_]
WHERE
CONVERT(DATE, so.[Order Date]) >= @StartDate AND CONVERT(DATE, so.[Order Date]) <= @EndDate AND
so.[Posting Date] IS NOT NULL AND
so.[Posting Date] <> '1753-01-01 00:00:00.000' AND
so.[Requested Delivery Date] <> '1753-01-01 00:00:00.000' AND
sol.[Type] = 2 AND
sol.[Version No_] = (SELECT MAX([Version No_]) FROM SalesOrder WHERE No_ = so.No_)
) AS s
check this.
WITH Original_CTE
AS (SELECT [s].[ChainName],
[s].[OrderNumber],
[s].[ItemNumber],
[s].[ItemDescription],
[s].[OrderDate],
[s].[RequestedDeliveryDate],
[s].[PromisedDeliveryDate],
[s].[PostingDate],
[s].[ActualDeliveryDate],
( DATEDIFF(DAY, [ActualDeliveryDate], [RequestedDeliveryDate]) / CAST(365 AS FLOAT) ) AS [ReqDeliveryDateMet%],
[s].[QuantityOrdered],
[s].[QuantityDelivered],
DATEDIFF(d, s.OrderDate, s.ActualDeliveryDate) AS [LeadTime_NumberOfDays]
FROM (SELECT so.[Bill-to Name] AS [ChainName],
so.No_ AS [OrderNumber],
so.[Version No_],
sol.[No_] AS [ItemNumber],
sol.[Description] AS [ItemDescription],
sol.[Order Date] AS [OrderDate],
sol.[Requested Delivery Date] AS [RequestedDeliveryDate],
sol.[Promised Delivery Date] AS [PromisedDeliveryDate],
so.[Posting Date] AS [PostingDate],
DATEADD(DAY, CONVERT(INT, REPLACE(so.[Shipping Time], CHAR(2), '')), so.[Posting Date]) AS [ActualDeliveryDate],
sol.Quantity AS [QuantityOrdered],
sol.[Quantity Shipped] AS [QuantityDelivered]
FROM SalesOrders so
LEFT OUTER JOIN SalesOrderLine sol
ON so.[No_] = sol.[Document No_]
AND so.[Version No_] = sol.[Version No_]
WHERE CONVERT(DATE, so.[Order Date]) >= @StartDate
AND CONVERT(DATE, so.[Order Date]) <= @EndDate
AND so.[Posting Date] IS NOT NULL
AND so.[Posting Date] <> '1753-01-01 00:00:00.000'
AND so.[Requested Delivery Date] <> '1753-01-01 00:00:00.000'
AND sol.[Type] = 2
AND sol.[Version No_] = (SELECT MAX([Version No_])
FROM SalesOrder
WHERE No_ = so.No_)) AS s),
CTE
AS (SELECT *,
DATEDIFF(DD, RequestedDeliveryDate, ActualDeliveryDate) Diff
FROM Original_CTE),
CTE1
AS (SELECT *,
MAX(Diff)
OVER (
PARTITION BY 1) Max_Diff,
MIN(Diff)
OVER (
PARTITION BY 1) Min_Diff
FROM CTE)
SELECT *,
ROUND(( 1 - ( Diff * 1.0 / CASE
WHEN Diff < 0 THEN Min_Diff
ELSE Max_Diff
END ) ) * 100, 0) Close_Far
FROM CTE1
ASKER
It is not working for me because that select is the select of an insert into to populate a variable table in my stored procedure.
so the part:
INSERT INTO @SalesOrderLeadTime
(
[ChainName]
, [OrderNumber]
, [ItemNumber]
, [ItemDescription]
, [OrderDate]
, [RequestedDeliveryDate]
, [PromisedDeliveryDate]
, [PostingDate]
, [ActualDeliveryDate]
, [ReqDeliveryDateMet%]
, [QuantityOrdered]
, [QuantityDelivered]
, [LeadTime_NumberOfDays]
)
WITH Original_CTE <<< this does not work
so the part:
INSERT INTO @SalesOrderLeadTime
(
[ChainName]
, [OrderNumber]
, [ItemNumber]
, [ItemDescription]
, [OrderDate]
, [RequestedDeliveryDate]
, [PromisedDeliveryDate]
, [PostingDate]
, [ActualDeliveryDate]
, [ReqDeliveryDateMet%]
, [QuantityOrdered]
, [QuantityDelivered]
, [LeadTime_NumberOfDays]
)
WITH Original_CTE <<< this does not work
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much folks!