Hello,
I'm trying to change the date format in a query element to make it user friendly for WordFly and I can't seem to get it work. Looking at the documentation, I should be able to use the CONVERT function in the system table to do so, but I'm getting an error. Alternatively, I tried setting up a new view and did the conversion there and that seems to work, but I can't get the elements in the new view to pull into my output set. I've granted SELECT permissions to ImpUsers and Tessitura_App and WordFly in SSMS.
And also related -- even when I have the output set working (with the dates in the wrong format, but the data pulling in from another view), WordFly can't seem to find those fields -- can it not use custom output elements? They aren't showing up as fields to be mapped.
Anne
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.
I realized that I have the date format working for current members who are expiring at the end of the month. I never did get it working for lapsed members. I may just hold off until V16 at this point.