Wednesday, June 13, 2007


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, trade_date, ...,
DATEDIFF(n,GETDATE(),CONVERT(CHAR(10),trade_date,110) + ' ' + cutoff_p) <= 30 AND d.amount_p > 0
AND email_date IS NULL
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 = 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.

No comments:

Post a Comment