SQL query to calculate delivery dates for the Frame Agreements (Sale Blanket Agreement)

[rutu8831]

Dear Community, I am trying to find out delivery date for each Frame Agreements (Sale Blanket Agreement). Following is the sql query that I created but it does not give correct results. Please help.

WITH DeliveryDates AS (

   SELECT

       OOAT."StartDate",

       OOAT."EndDate",

       OOAT."BpName",

   OOAT."Project",

       OITM."ItemCode",

       OITM."ItemName",

       OAT2."DatePeriod",

       OAT2."Quantity",

       OAT1."PlanQty",

       OAT1."CumQty",

   (Select Sum(RDR1.OpenQty) from RDR1 Where RDR1.AgrNo = OOAT.[AbsID] and RDR1.ItemCode = OAT1.ItemCode) [SO_QTY],

    OAT1.[CumQty]+(Select Sum(RDR1.OpenQty) from RDR1 Where RDR1.AgrNo = OOAT.[AbsID] and RDR1.ItemCode = OAT1.ItemCode)[CumQty_Includ_SO],

       CASE

           WHEN OAT2."DatePeriod" = 'M' THEN DATEADD(MONTH, 1 / DATEDIFF(DAY, OOAT."StartDate", OOAT."EndDate"), OAT2."FromDate")

           WHEN OAT2."DatePeriod" = 'A' THEN DATEADD(YEAR, 1 / DATEDIFF(DAY, OOAT."StartDate", OOAT."EndDate"), OAT2."FromDate")

           WHEN OAT2."DatePeriod" = 'O' THEN OAT2."FromDate"

           WHEN OAT2."DatePeriod" = 'W' THEN DATEADD(WEEK, 1 / DATEDIFF(DAY, OOAT."StartDate", OOAT."EndDate"), OAT2."FromDate")

           WHEN OAT2."DatePeriod" = 'Q' THEN DATEADD(QUARTER, 3 / DATEDIFF(DAY, OOAT."StartDate", OOAT."EndDate"), OAT2."FromDate")

           WHEN OAT2."DatePeriod" = 'S' THEN DATEADD(MONTH, 6 / DATEDIFF(DAY, OOAT."StartDate", OOAT."EndDate"), OAT2."FromDate")

       END AS "DeliveryDate"

   FROM

       OOAT

   INNER JOIN OAT1 ON OOAT."AbsID" = OAT1."AgrNo"

   INNER JOIN OAT2 ON OAT1."AgrNo" = OAT2."AgrNo" AND OAT2."AgrLnNum" = OAT1."AgrLineNum"

   INNER JOIN OITM ON OAT1."ItemCode" = OITM."ItemCode"

   WHERE

       OAT2."FromDate" BETWEEN OOAT."StartDate" AND OOAT."EndDate"

)

SELECT *

FROM DeliveryDates;

[Johan_H]

In what way are the results incorrect?

[rutu8831]

Hi Johan, The delivery dates calculated from the query output does not seem to be correct.

[Johan_H]

Your query contains StartDate and EndDate. Which of these dates is the "delivery date"? Could you please post a screenshot of the query result, and a table of with the expected result?

[rutu8831]

hi Joihan, Hi Johan, If you see the query posted, the Delivery Date is calculated. Please find the attached screenshot. I wish to get correct Delivery dates based the Frequency - Monthly, Annual, Once, Weekly etc. In SAP, except Frquency "Occuring Once", for other like Monthly, Annual, the dates are not specified, Hence I need to get all delivery dates for specified frequencies for Frame Agreements.

[rutu8831]

I also tried running another version with different logic. CASE WHEN OAT2."DatePeriod" = 'M' THEN COALESCE(OAT2."FromDate", OOAT."StartDate") WHEN OAT2."DatePeriod" = 'A' THEN COALESCE(OAT2."FromDate", OOAT."StartDate") WHEN OAT2."DatePeriod" = 'O' THEN COALESCE(OAT2."FromDate", OOAT."StartDate") WHEN OAT2."DatePeriod" = 'W' THEN COALESCE(OAT2."FromDate", OOAT."StartDate") WHEN OAT2."DatePeriod" = 'Q' THEN COALESCE(OAT2."FromDate", OOAT."StartDate") WHEN OAT2."DatePeriod" = 'S' THEN COALESCE(OAT2."FromDate", OOAT."StartDate") END AS "DeliveryDate"

[Johan_H]

Hi, the second query will not work. Can you please describe what you are trying to achieve? You want to return a delivery date based on the value of OAT2."DatePeriod". What do the values (M, A, O ,Q ,S) in that field stand for, and what date do you want to show in each of these cases?

[rutu8831]

Screenshot of the output

[rutu8831]

Hi Johan, If you see the query posted, the Delivery Date is calculated. Please find the attached screenshot. I wish to get correct Delivery dates based the Frequency - Monthly, Annual, Once, Weekly etc. In SAP, except Frquency "Occuring Once", for other like Monthly, Annual, the dates are not specified, Hence I need to get all delivery dates for specified frequencies for Frame Agreements.

[Johan_H]

Hi,

01 DATEADD(MONTH
02       , 1 / DATEDIFF(DAY, OOAT."StartDate", OOAT."EndDate")
03       , OAT2."FromDate")

On the second line you are dividing 1 by the number of days between the start and end days. That means that a fraction of a month will be added to OAT2."FromDate". Is this correct?

Regards,

Johan

[rutu8831]

Yes that is correct but I don't know if the logic is correct. I tried another version of the query

CASE WHEN OAT2."DatePeriod" = 'M' THEN COALESCE(OAT2."FromDate", OOAT."StartDate")
WHEN OAT2."DatePeriod" = 'A' THEN COALESCE(OAT2."FromDate", OOAT."StartDate")
WHEN OAT2."DatePeriod" = 'O' THEN COALESCE(OAT2."FromDate", OOAT."StartDate")
WHEN OAT2."DatePeriod" = 'W' THEN COALESCE(OAT2."FromDate", OOAT."StartDate")
WHEN OAT2."DatePeriod" = 'Q' THEN COALESCE(OAT2."FromDate", OOAT."StartDate")
WHEN OAT2."DatePeriod" = 'S' THEN COALESCE(OAT2."FromDate", OOAT."StartDate")
END AS "DeliveryDate"