The concept is based on our monthly subscription model product. The reasoning is the subscription model app is not indicating a non-renewed customer. So I need to look at the past 2 months (from GetDate()) to see the $ value to figure out if I need to treat the recipient as a customer or lost customer(non-renewed).
I need to pull back the most recent purchase month and log the transaction total for each recipient in my workflow.
For example:
Previous month 1 transaction total (1 month ago (0-31days ago))
Previous month 2 transaction total (2 months ago (32-60 days ago))
Basic Rules:
If Previous month 1 > 0 && Previous month 2 >= 0 that recipient is current customer.
if Previous month 1 == 0 && Previous month 2 > 0 that recipient is a non-renewed customer.
I've got most of this done BUT, im having trouble inserting the date var into the XML queryDef. And I'm also not sure if I am constructing the data correctly. The logInfo() seems to be returning the correct dates but Im not sure if the XML is reading it correctly. It does run but Im not returning any transactions when I should have something.
this is the line in my queryDef. datetSum1 is my variable from my date constructor (below, its toward the bottom).
<condition expr={"[@transactionDate] >= '" + dateSum1 + "'"}/>
//Get Previous Purchaee Month 1
var previousPurchaserMonth1 = xtk.queryDef.create (
<queryDef schema="lw:saleDetail" operation="select" >
<select>
<node expr="Sum(@transactionLineTotal)" alias="@sumPreviousPurchaserMonth1" />
<node expr="@LID" groupBy="1" />
</select>
<where>
<condition expr={"[@LID] = '" + r.@LID + "'"}/>
<condition expr={"[@productGroupSegment] = '" + '141' + "'"}/>
<condition expr={"[productMaster/@productSubcatDesc] = '" + 'Ongoing-smallgroup.com' + "'"}/>
<condition expr={"[@transactionDate] >= '" + dateSum1 + "'"}/> //this is the line in question
</where>
</queryDef>
).ExecuteQuery();
