Solved

calculate percentage based on dates met

Posted on 2014-04-08
11
180 Views
Last Modified: 2014-04-14
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
Comment
Question by:metropia
11 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39986624
Give us some sample data + expected output of what you're trying to pull off here.
0
 

Author Comment

by:metropia
ID: 39986675
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 Comment

by:metropia
ID: 39986711
I tried this:

(DATEDIFF(DAY, [RequestedDeliveryDate], [ActualDeliveryDate]) / CAST(365 AS FLOAT)) * 100

Open in new window


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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 40

Expert Comment

by:Sharath
ID: 39986926
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

Open in new window

http://sqlfiddle.com/#!3/72114a/12
0
 

Author Comment

by:metropia
ID: 39986980
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 Comment

by:metropia
ID: 39986986
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

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 39987011
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 

Open in new window

0
 

Author Comment

by:metropia
ID: 39987065
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
0
 
LVL 40

Accepted Solution

by:
Sharath earned 470 total points
ID: 39987159
INSERT INTO @SalesOrderLeadTime 
            ([ChainName], 
             [OrderNumber], 
             [ItemNumber], 
             [ItemDescription], 
             [OrderDate], 
             [RequestedDeliveryDate], 
             [PromisedDeliveryDate], 
             [PostingDate], 
             [ActualDeliveryDate], 
             [ReqDeliveryDateMet%], 
             [QuantityOrdered], 
             [QuantityDelivered], 
             [LeadTime_NumberOfDays]) 
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
;WITH CTE 
     AS (SELECT *, 
                DATEDIFF(DD, RequestedDeliveryDate, ActualDeliveryDate) Diff 
           FROM @SalesOrderLeadTime), 
     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 

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 30 total points
ID: 39988989
In pseudo code, I think you would want something like -
(requested_delivery_date - order_date) / (actual_delivery_date - order_date) * 100
0
 

Author Closing Comment

by:metropia
ID: 39999596
Thank you so much folks!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 30
TSQL - How to declare table name 26 30
T-SQL: "HAVING CASE" Clause 1 24
2 IIF's in Access query 25 23
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question