Happy Business Starts Here

Data Query to get the latest charge lines for a Subscription

vkannan
Valued Scholar

Data Query to get the latest charge lines for a Subscription

Hello,

I have a subscription that has been amended (Update Product) 2 times. I wanted to write a query to get the latest Rate plan charge details for the given subscription. But my query keeps returning me 3 lines, 1 for every version of the subscription, how can i correct this?

Here's the query that I am using,

SELECT
Subscription.contractEffectiveDate AS EffectiveDate,
Subscription.serviceActivationDate AS ActivationDate,
Subscription.status AS Status,
Subscription.subscriptionstartdate AS StartDate,
Subscription.subscriptionenddate AS EndDate,
Subscription.name AS Name,
rateplan.name AS RatePlanName,
rateplan.id AS RatePlanId,
rateplancharge.name AS ChargeName,
rateplancharge.id AS ChargeId,
FROM
Subscription
JOIN
RatePlan
ON Subscription.id = rateplan.subscriptionid
JOIN
RatePlanCharge
ON RatePlan.id = rateplancharge.rateplanid
WHERE
Subscription.Id = <current subscription ID>

 

Thanks,
Vimal

Tags (2)
1 ACCEPTED SOLUTION

Accepted Solutions
Karina
Savvy Scholar

Re: Data Query to get the latest charge lines for a Subscription

Hi

 

I guess there are multiple ways of doing this but I think you should add some additional conditions to your query. If you follow this link, you see a section called Segmented rate plan charges. There you have suggestions on how to retrieve the current version of a charge.

 

Example of something I have used:
WHERE RatePlanCharge.IslastSegment = TRUE AND AmendmentType != 'RemoveProduct'.

 

We usually do not want the output to contain the charge that has been removed. When a charge has been removed the charge IsLastSegment = TRUE. That is why I added the clause about the AmendmentType. But of course using RatePlanCharge.EffectiveEndDate is a good option as well.

 

Hope this was helpful 🙂

 

Karina

View solution in original post

1 REPLY 1
Karina
Savvy Scholar

Re: Data Query to get the latest charge lines for a Subscription

Hi

 

I guess there are multiple ways of doing this but I think you should add some additional conditions to your query. If you follow this link, you see a section called Segmented rate plan charges. There you have suggestions on how to retrieve the current version of a charge.

 

Example of something I have used:
WHERE RatePlanCharge.IslastSegment = TRUE AND AmendmentType != 'RemoveProduct'.

 

We usually do not want the output to contain the charge that has been removed. When a charge has been removed the charge IsLastSegment = TRUE. That is why I added the clause about the AmendmentType. But of course using RatePlanCharge.EffectiveEndDate is a good option as well.

 

Hope this was helpful 🙂

 

Karina

View solution in original post