• Status: Solved
• Priority: Medium
• Security: Public
• Views: 191

# 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).
0
metropia
2 Solutions

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Give us some sample data + expected output of what you're trying to pull off here.
0

Author Commented:
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
0

Author Commented:
I tried this:

``````(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%
0

Data EngineerCommented:
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
0

Author Commented:
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
0

Author Commented:
``````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
``````
0

Data EngineerCommented:
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],
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
``````
0

Author Commented:
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:

(
[ChainName]
,      [OrderNumber]
,      [ItemNumber]
,      [ItemDescription]
,      [OrderDate]
,      [RequestedDeliveryDate]
,      [PromisedDeliveryDate]
,      [PostingDate]
,      [ActualDeliveryDate]
,      [ReqDeliveryDateMet%]
,      [QuantityOrdered]
,      [QuantityDelivered]
)
WITH Original_CTE              <<< this does not work
0

Data EngineerCommented:
``````INSERT INTO @SalesOrderLeadTime
([ChainName],
[OrderNumber],
[ItemNumber],
[ItemDescription],
[OrderDate],
[RequestedDeliveryDate],
[PromisedDeliveryDate],
[PostingDate],
[ActualDeliveryDate],
[ReqDeliveryDateMet%],
[QuantityOrdered],
[QuantityDelivered],
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],
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
;WITH CTE
AS (SELECT *,
DATEDIFF(DD, RequestedDeliveryDate, ActualDeliveryDate) Diff
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
``````
0

Commented:
In pseudo code, I think you would want something like -
(requested_delivery_date - order_date) / (actual_delivery_date - order_date) * 100
0

Author Commented:
Thank you so much folks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.