Converting date format in a Query Element

Sometimes the embedded SQL code options in parts of Tessitura are more limited in syntax options than TSQL itself: I'm often stymied in my attempts to do something clever (most recently setting up disable clauses in report parameters).  So I would then go with a view, as you have.  Now, the output set elements are displaying properly within Tessitura, right?  The first thing I'd think of with Wordfly rejecting an element is that Wordfly will not accept elements that may result in multiple rows.  Is that the case here, or is the element configured in such a way that it looks like it might return multiple rows?  Is the whole output set failing to be picked up in Wordfly or only that column?

The original view I created is rendering properly in Tessitura -- the data fields are pulling in -- but they aren't showing up in WordFly.

The fields from the new view I created -- to try to convert the date field without breaking the view that was working -- aren't showing up in Tessitura, so it makes me feel like there is another level of permission that I'm missing.

I'm only seeing one line per constituent in the output of my list, but I'm also using a filter -- is that the issue? I'm starting to think that we may not be able to do this until v16. I need to pull in the most recent membership expiration date for lapsed members. This should be easy in v16, but maybe it isn't possible in v15 (although I feel like someone else must be doing this).

Anne M Robichaux said:

but I'm also using a filter -- is that the issue?

Yes, I'm pretty sure that's the problem.  If the field is capable of returning more than one row Wordfly will reject it.

Anne M Robichaux said:

I need to pull in the most recent membership expiration date for lapsed members.

Not trivial but not impossible.  I have a view that does this.  If you have multiple membership organizations, you might need to build views/output sets for each.

We only have one membership organization. Would you be willing to share your code for the view? 

We had to create a custom "Membership - Most Recent" element for v15. We have several membership orgs with the same start date and that caused issues with the standard Membership - Most Recent view for us. What is the function of the filter  you mentioned?

Going to look for it as we speak!

My query element is looking at all past memberships, so the filter is on the expiration date. But a Most Recent membership element would be just what we need. Would you be willing to share your code?

My query element is looking at all past memberships, so the filter is on the expiration date. But a Most Recent membership element would be just what we need. Would you be willing to share your code?

Our view adds a ranking field to each member org based on expiration date. Since you have only one org, it probably won't work. But you could use RANK and PARTITION BY on whatever fields in the view you wanted to filter into the element and select only the fields with the ranking of 1. 

Here is our view;:

CREATE VIEW [dbo].[LVS_ELEMENTS_MEMBERSHIP_MOST_RECENT]
AS
WITH recent_membership AS
(SELECT
customer_no,
memb_org_no,
cust_memb_no,
RANK() OVER (PARTITION BY customer_no, memb_org_no ORDER BY expr_dt DESC, init_dt DESC, create_dt DESC, current_status desc, cust_memb_no desc) AS ranking
FROM dbo.TX_CUST_MEMBERSHIP)

SELECT
rm.ranking, cm.*
FROM dbo.VS_ELEMENTS_MEMBERSHIP CM
INNER JOIN recent_membership RM ON CM.customer_no = RM.customer_no AND CM.memb_org_no = RM.memb_org_no AND CM.cust_memb_no = RM.cust_memb_no AND ranking = 1

GO

Like I said, this won't help if you only have one org. But you could do something similar using the field you are filtering on, maybe?

Hmmm. I got it working, but it is pulling dates other than the particular month I'm looking for. I built a filter for the expr_dt, but it isn't showing up in List Manager on my output set. If I can get that working, would that do it? I need to just pull people who expired 3 months ago.

How are you partitioning the data? Maybe it needs to be partitioned differently? Not sure.

I used Gawain's code above. We only have one membership org.

Warning, in case it's relevant for you, RANK can return two rows with the same number (within the partition) if the order values match, while ROW_NUMBER will arbitrarily choose an order in those circumstances, and assign a different number.  That's caught me out a number of times.

I am a ROW_NUMBER() with PARTITION BY person.  Almost every time it ends up working better for me.

To be sure, in this case having a unique id (cust_memb_no) as part of the order by ensures that it wouldn't happen, but I like that ROW_NUMBER spares me from having to think about it.