SAP SQL Query

[Former Member]

Good Afternoon,

I am trying to populate a UDF on a SAP service call automatically with a return despatch date.

Basically when an instrument is received in the creation date of the document I want to add 10 working days to this and put this in the UDF.

At the moment I can add 10 days but this also includes weekends and bank holidays.

Here is what I am using as my linked query at the moment:

SELECT min (T0.[U_Date]) FROM [dbo].[@VALIDDELDATES] T0 WHERE T0.[U_Date] >= dateadd(dd, 10, $[$71.0.date])

The above query is setup to add from the OSCL.createDate but does not work correctly returning an error:

Incorrect syntax near $71.0.date

Whereby VALIDDELDATES is a UDT with the following fields, Code, Name and U_Date.

I have added a number of dates eg that do not include bank holidays/weekends below is the format for the table

Code Name U_Date

10     10          13.08.15

11     11          14.08.15

12     12          17.08.15

SAP did seem to modify the code at one point to the following:

SELECT min (T0.[U_Date]) FROM [dbo].[@VALIDDELDATES] T0 WHERE T0.[U_Date] = dateadd(dd, 10, CONVERT(DATETIME, '20150817', 112))

Not sure why?

Regardless I was wondering where I had gone wrong.

Alternatively I though it might be easier to look up todays date against the relating code and then add 10 to the code and return the correspond U_Date but I am unsure on if this is possible/can be achieved.

Any help would be greatly appreciated.

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT min (T0.[U_Date]) FROM [dbo].[@VALIDDELDATES] T0 WHERE T0.[U_Date] >= dateadd(dd, 10, $[$71.0.0])

Thanks.

[Former Member]

Hi Gents,

I could not get this to work on either updating the SELECT part or Bharathiraja's suggestion.

When you try a SELECT statement on its own nothing is returned however (as this is linked to OSCL) so perhaps a logic error here.

[Former Member]

It kind of sounds like you're trying to do this the hard way, by using a huge pool of valid dates, and trying to figure out whether or not your date is within that pool, and accounting for that.

You might have better luck by making your table a table of EXCLUSIONARY dates, dates that you don't want the dates to fall into, and then looping through and totaling the number of actual days that you need to add based on that. Here is some pseudo-code to think about as an example. Keep in mind that this is PSEUDO-code, just to get the wheels churning, you'll have to adopt this to your own project using your own code:

--Declare and initialize variables
DECLARE @InputDate DATETIME
DECLARE @iCount INTEGER
DECLARE @iLoop INTEGER

SET @InputDate = '08/31/2015'

--Loop through until we have added at least 10 days
WHILE iCount < 10
BEGIN
  --Increment our date counter
  SET @iLoop = @iLoop + 1

--Check to see if this date is in our "exclusionary" list
  SELECT T0.[U_Date]
  FROM [dbo].[@INVALIDDATES] T0
  WHERE T0.[U_Date] = DATEADD(day, @iLoop, @InputDate)

--If we've not found a match in the "exclusionary" list, then add another day to our counter
  IF @@ROWCOUNT <> 0 THEN
    SET @iCount = @iCount + 1

--If we've gotten to the point where our count is finally greater than 10, check the date it falls on, and add in extra days as needed to get it to the next business day.
  IF @iCount > 10 THEN

      CASE SELECT datename(dw, @InputDate + @iLoop)
        WHEN 'Saturday'
            THEN RETURN DATEADD(day, 2 + @iLoop, @InputDate)
        WHEN 'Sunday'
            THEN RETURN DATEADD(day, 1 + @iLoop, @InputDate)
        ELSE THEN RETURN DATEADD(day, @iLoop, @InputDate)

END

Just my two cents, but I hope this helps, this is how I would do it

[Former Member]

Hi Franklin,

Use this query

DECLARE @Validcount int

SET @Validcount =(select count(d.u_date) from [@VALIDDELDATES] d where d.u_date>=$[OSCL.createdate.date]

and d.u_date<= dateadd(d,10,$[OSCL.createdate.date]) and datename (dw,d.u_date) not in ('Saturday','Sunday'))

SELECT dateadd(d,11+(DATEDIFF(wk, $[OSCL.createdate.date], DATEADD (d,10,$[OSCL.createdate.date] )) * 2)+@Validcount  ,$[OSCL.createdate.date])

[Former Member]

Hi Frank,

Thank you for trying to answer my query : -).

I tried both of the above I get the following error message still sadly:

Incorrect syntax near '$%71.0.0'  and also:

Statement " (SWEI) (s) could not be prepared.

[frank_wang6]

OK, it is better u use "SELECT $[$71.0.0]" and see what you get, and then CONVERT function to convert to a proper date variable, like CONVERT(DATE, $[$71.0.0], 4), obviously, 4 is not the correct format here.

Frank

[frank_wang6]

Is that because the  $71.0.date has to be  $71.0.DATE

[frank_wang6]

Try this.

SELECT min (T0.[U_Date]) FROM [dbo].[@VALIDDELDATES] T0 WHERE T0.[U_Date] >= dateadd(dd, 10,    CONVERT(DATE, $[$71.0.0], 4))