Thursday, June 28, 2007

Pony ride

On Sunday we went to the annual street fair on avenue M. The primary reason for going was to put Shlomik on a pony. We bought some things for ourselves. Bought Shlomik some toys, including maracas from a Native American stand.

Native American stands are my favorite at the fair. They always have cool music, musical instruments and clothes. We bought a sweater there for Shlomik. Last year I bouht t-shirts with wolves. This year, unfortunately, they didn't have anything with animals.

Shlomik got to ride on a pony for half a block and back and got his picture taken. He somewhat enjoyed the ride, would've enjoyed it more if he wasn't tired. Unfortunately, I didn't bring a camcorder or camera. The pictures were taken by the ride owners using a polaroid and that's why they look so bad. I'm amazed that they still use that outdated crap. The film is pretty expensive, the pictures are bad and very small. For the same money or less they can either buy very small capacity flash memory in china or have a photo printer in their car. Another solution would be to burn CDs which cost only $.10 and take less than a minute to burn. Even if customer does not have a computer, something unforgivable in this day and age, local and regional photo development shops accept any kind of media and because of the high resolution of digital cameras the resulting images can be printed as small or as big as you want.

Tuesday, June 26, 2007

Levitansky Family Tree

My dad's relatives made a five generation family tree for him for his birthday.
The tree starts with Nokhim Levitansky and Chaya Pevsner. The tree is primarily of the Levitansky family, but also includes Levit, Leviton, Shapera, Sorkin, Swidler, and my dad's branch, starting from his father, Ruzin. I used Family Tree Maker to enter all the data into a file and came up with over 200 people. Google and Facebook were very useful for finding contact information for my distant cousins, some of whom replied to my emails. I created a group on Facebook, "Levitansky Family," where we can all meet. The group also has a link to a PDF of the family tree, which I'm hosting on my site. I found family in NJ, TX, WI and OH.
I asked my dad to look for any old photos, hopefully going all the way back to Nokhim, so that I can scan them in and post my group. A lot of information is missing from the family tree and some of the names are probably misspelled or even wrong. I'm hoping my extended family will help me correct any mistakes and to continue expanding the family tree as the our family expands.

Wednesday, June 20, 2007

SQL - Adding columns to a table

I'm a great proponent of using Enterprise Manager for creating and altering tables. For years I used the GUI exclusively for database management and only used the query analyzer for writing queries and doing quick selects.
In my last projects, I had to create and alter tables using scripts. Since the site was live and I was working on the staging site, the database schema on the live site would have to be altered instantaneously. I looked up how to alter tables using scripts and found something that I could have used on many occasions when altering table in Enterprise Manager.
Enterprise Manager is a great tool, but it has one shortcoming when dealing with tables. If you need to add a column with a default value, you would have to run an update query on the table because most of the time, the column would get added with nulls instead of the default value. If you need to add a column with a not null constraint, that's when it gets fun. You need to the column with a default and not null checked off, save the table, update the table, check not null on and save the table one more time. Annoying, time consuming, not fun. Now lets tackle the same problem using a script:
ALTER TABLE table-name ADD
column-name column-type NOT NULL DEFAULT default-value WITH VALUES
Short and beautiful. DEFAULT specifies a default value and WITH VALUES instructs SQL Server to populate all rows with the default value.

Wednesday, June 13, 2007

T-SQL - Using CASE in SELECT

Today I had a task of modifying my code on an investment site I made. The task was to change the color of a row if a ticket is near closing time, 30 minutes, or passed it and an email hasn't been sent to the clearing house yet.
I had two choices, either do it inside the query or in ColdFusion. I decided to do it inside the query.

SELECT t.id, trade_date, ...,
CASE WHEN
(
DATEDIFF(n,GETDATE(),CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_p) <= 30 AND d.amount_p > 0
AND email_date IS NULL
)
OR
(
DATEDIFF(n,GETDATE(),CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_r) <= 30 AND d.amount_r > 0
AND email_date IS NULL
)
THEN 1 ELSE 0 END AS alert
FROM ticket t JOIN ticket_detail d ON t.id = d.ticketid
LEFT OUTER JOIN fund f ON d.fundcode = f.fundcode
ORDER BY trade_date, account_name
CASE is a very useful statement in SQL. It can be used either as CASE expression WHEN or CASE WHEN expression. Here, I'm using the later form.
I have 2 columns,
cutoff_r and cutoff_p. They hold the cutoff times for purchases and redemptions. These colums are not DATETIME, rather they are of type VARCHAR. Since the cutoff values are VARCHAR, they have to be converted to DATETIME.
First, lets add the cutoff times to the date the fund is supposed to be traded:
CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_p
The trading date is converted into USA standard, or mm-dd-yyyy, a CHAR 10 characters long. Then the cutoff time, which is in the h:mm(A/PM) form, is concatenated at the end. Notice the space in the middle. Without it, the hour will immediately follow year and confuse SQL Server.
The first condition is comparing current time with the new trade date and checking that there's less than 30 minutes until cutoff or that the time has passed, in which case the result will be negative. The second condition checks if the trade is a purchase and the third condition checks if an email was sent. If all are true, then the expression is true.
Next, I add a duplicate expression that checks if the trade is a redemption. I could have put the email sent check outside, but then I would have to enclose whole thing in another pair of parenthesis since this is an OR comparison.
The last part,
THEN 1 ELSE 0 END AS alert
states that if the expression is true, then return 1, else, return 0 and name the column alert.

Monday, June 4, 2007

Jaws

Today we went to see the doctor for Shlomik's checkup. He grew 1.5cm smarter and 1.25" taller.
He was behaving somewhat better than last time, though that could be either because his grandpa was there or because of the time past since his fun visit for a blood test. Shlomik also got a shot. Next visit is in another three months.

Shlomik hasn't had any new teeth in months. This shabbat, we found a new tooth in the front. Originally, he had four on top and three on the bottom and this was the tooth he was missing from the bottom. I was somewhat concerned about his tooth not coming out for such a long time. I asked the doctor if it was a problem. In answer, lo and behold, the doctor showed me that Shlomik actually has two back teeth that already broke through and I think he mentioned another two. Now he'll finally be able to chew his food better instead of swallowing it half chewed, if he's not too lazy.