1

I am using AMPscript in Salesforce Marketing Cloud to parse serialized JSON data for hotel reservations. Each entry contains start and end dates along with a daily rate. I need to calculate and display rates for each day of the stay, excluding the rate for the checkout day.

Data example:

[{"start":"2024-08-22","end":"2024-08-24","amount":546.4}, {"start":"2024-08-24","end":"2024-08-25","amount":581.68}]

Desired Output:

•   August 22, 2024: $546.40
•   August 23, 2024: $546.40
•   August 24, 2024: $581.68

However, my current implementation incorrectly includes an entry for the checkout day and duplicates the rate for the transition day:

AMPscript Snippet:

%%[
Set @json = AttributeValue("serializedresdata")
Set @rows = BuildRowsetFromJSON(@json, "$[*]", true)
For @row = 1 TO RowCount(@rows) do
  Set @rowData = Row(@rows, @row)
  Set @start = Field(@rowData, "start")
  Set @end = Field(@rowData, "end")
  Set @amount = Field(@rowData, "amount")
  Set @startDate = DateParse(@start)
  Set @endDate = DateParse(@end)
  Set @dateDiff = DateDiff(@startDate, @endDate, "D")
  For @i = 0 TO @dateDiff - 1 do
    Set @currentDate = DateAdd(@startDate, @i, "D")
    Set @formattedDate = FormatDate(@currentDate, "MMMM dd, yyyy")
    Output(Concat("<p>", @formattedDate, ": $", FormatCurrency(@amount, "en-US"), "</p>"))
  next @i
next @row
]%%

Issue: The output incorrectly displays:

•   August 22, 2024: $546.40
•   August 23, 2024: $546.40
•   August 24, 2024: $546.40 (should not include as start of new reservation)
•   August 24, 2024: $581.68
•   August 25, 2024: $581.68 (checkout day, should not display)

How can I adjust the AMPscript to avoid duplicating the daily rate for the transition day and exclude the checkout day’s rate?

Any assistance would be greatly appreciated!

Regards,

Matt

1 Answer 1

0

I see you are very close to solve it. I've noticed the 14th line has this: For @i = 0 TO @dateDiff - 1 do.

You cannot subtract the date difference through the operator, you would need to use the Subtract function instead. Also DateParse function is not really need in this case.

I would modify your version like this:

%%[
    set @json = AttributeValue("serializedresdata")
    set @rows = BuildRowsetFromJSON(@json, "$[*]", true)

    for @row = 1 to RowCount(@rows) do
        set @rowData = Row(@rows, @row)
        set @start = Field(@rowData, "start")
        set @end = Field(@rowData, "end")
        set @amount = Field(@rowData, "amount")
        set @dateDiff = DateDiff(@start, @end, "D")

        for @i= 0 to Subtract(@dateDiff, 1) do
            set @currentDate = DateAdd(@start, @i, "D")
            set @formattedDate = FormatDate(@currentDate, "MMMM dd, yyyy")
            Output(Concat("<p>", @formattedDate, ": ", FormatCurrency(@amount, "en-US"), "</p>"))
        next @i
        
    next @row
]%%
1
  • Amazing! Thank you so much for your help! That solved it for me!!
    – Matt Black
    Commented May 11 at 13:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .