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).

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?

I'm going to play with this in test and see if I can get it sorted. Thank you! 

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.

I'm circling back on this one -- I know I had this working, but now it isn't and I'm thinking a service pack may have done something. Your code works for pulling the view together, but I'm stuck on trying to get the last column to format the date. I used CONVERT(varchar, expr_dt, 107) as expr_dt_format FROM TX_CUST_MEMBERSHIP  in my view and that worked formatting wise, but I can't seem to get it to generate in your view. Any ideas?

Could the where clause be returning move than 1 result? As in it is pulling in active as well as pending or inactive.

Could the where clause be returning move than 1 result? As in it is pulling in active as well as pending or inactive.