Hi All,
Me again with a conundrum regarding custom Flex Header Elements.
I've been trying to get a calculation of Age in years and months based on birthdate_1 attribute. I finally got my SQL to work in SSMS, but devastatingly when I put it into Tessitura the header is still giving me an error.
Anyone have this working in their database?
The forum is not letting me post my SQL code in this post. I'll try to follow up with it as a comment.
Thanks,
Kanani
STill not letting me... here's a screen shot of it
Is this what you are using in TR_FLEX_HEADER_ELEMENT? I've never seen variables declared there. Try creating a local view in SSMS (customer_no and age as your columns) and write a simple select statement from that, instead.
Hi Kevin,
Thanks for your response. After some research, I learned that you can't declare variables in views, but functions are the way to go. I've never created either, but I was able to stumble through creating a function with some help from SQL for dummies and Google. However, it's only returning age in years, whereas the query I wrote before was giving me what I want which is "x years x months".
Are you familiar with functions, or know what might be going wrong for me? I appreciate your help.
I think you need to define a character length for your varchar age (i.e. @age varchar(255)): otherwise it defaults to 1.
Also, I think this may be a bit easier to maintain:
Woohoo! Thanks, Gawain! adding (255) did the trick.
Arg! I spoke too soon. I don't know how to write the select statement in TR_Flex_header_element so that it will give me the age. Putting @customer_no where I had a hard constituent id for testing is giving me error.
What does your syntax look like?
Also might want to add an "ISNULL" statement to display some text in case the birthdate isn't available.
I tried a few different things
I'm just going off of other examples...I don't actually know how this works.
I would expect the latter to work...but maybe drop the "as 'age'" part?
All of the examples in my Flex Headers of using a function use the former format, but that is because they are table functions (so returning a value for every customer and then you pull out the value you want with the comparison to @customer_no).
Hi Kanani,
our age flex header element syntax is
Thanks, Heath. I think I'll keep this in my pocket as my plan B if I can't get it to display years and months. I'll have to ask our drama school how important that is to them. We're also showing birthdate in the header, so I'm assuming between the two they'll be able to make it work.
This is the SQL we use in one of our Flex Headers:
select convert(varchar(05),cast(datediff(DAY, cast(key_value as date), getDate() -1) / (365.23076923074) as int)) + 'y ' +convert(varchar(05),cast(datediff(MONTH, cast(key_value as date), getDate() -1) % (12) as int)) + 'm'from TX_CUST_KEYWORDwhere customer_no = @customer_no and keyword_no = 1
This returns the age as "19y 10m" for someone born on 25 Dec 2000.
Martin
Oh, Martin!
Thank you thank you thank you! You don't know how happy this makes me. Such a headache trying to figure this out last week. Happy Monday!
Can someone from the Network weigh in on why Kanani's function call wasn't working?