I'm trying to find all my March birthdates and Anniversaries, as entered in attributes in the format of mm/dd/yyyy.
How would I set up the search to find "03" in the two leftmost characters?
I'd like to avoid getting anyone that was born/married on the 3rd day of the month.
It seems this is kind of like a reverse wild-card. Rather than finding within a string of characters, I'm trying to limit it to only specific characters within the field.
Is this even possible?
Chris,
Something like this should work:
select distinct customer_no
from tx_cust_keyword
where
keyword_no = 1
and substring(key_value, 1,2) = '03'
A word of warning, if some of your birthdates were from conversion data they may be stored in different formats - for example YYYY-MM-DD or something else crazy. Values in tx_cust_keyword are stored as text so there could be anything in there. I would check before you run any restrictive criteria like the one above.Dale
Unfortunately I didn't get any results when I did this.
Is there a place to enter the Attribute value/name during the search?
In list manager I used the following and received back just September birthdays.
Select Distinct a.customer_no
From t_customer a (NOLOCK)
JOIN TX_CUST_KEYWORD e (NOLOCK) ON a.customer_no = e.customer_no
Where IsNull(a.inactive, 1) = 1
AND e.key_value like '%09%' AND e.keyword_no = 1
Thanks,
Bobby Moseley
Computer Services Manager
1921 W, Bell
Houston, TX 77019
(713) 535-3253
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Christopher Zello Sent: Monday, June 29, 2009 10:25 AM To: Moseley, Bobby Subject: [Tessitura Development Forum] Finding the month in a birthdate
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
I thought you were working in SQL Server Mgt. If you are in list manager in Tessitura you can still do the following query just do it as a manual edit. Create a new list, click on "Manual Edit" and paste into the bottom of the window:
where keyword_no = 1
If you are getting nothing, I'm guessing you might have issues with the way the data is stored in the table.
Dale
I'm going to have our IT guy look the two replies over, since I'm still not getting anything. It likely is the data. Thank you!
I used this in the manual edit in list maker but unfortunately, with "01" as the parameter, it pulled birthdays in January AND birthdays on the 1st of any month.
Thoughts?
Sheila,
Because the date is stored in the birth date as [1989-02-15] try adding a DASH on each side the month.
Select Distinct a.customer_no, e.*
AND e.key_value like '%-01-%' AND e.keyword_no = 1
From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Sheila Kearney Sent: Thursday, July 16, 2009 1:02 PM To: Moseley, Bobby Subject: Re: [Tessitura Development Forum] RE: Finding the month in a birthdate
From: Bobby Moseley <bounce-bobbymoseley3262@tessituranetwork.com> Sent: 6/29/2009 11:02:13 AM
Wonderful, thanks Bobby!