I have a new daughter! Welcome Serenity
Born at 8:53 AM on Saturday, June 21st
7 lbs 11 oz
19 and 1/2 inches long
Entries for month: June 2008
Some links have to speak for themselves
Some things really make me shake my head in wonder... reading this, I
think: someone saw an advertisement on TV: "Make money as an IT
professional!" and thought, thats for me! To quote one line from this
ridiculous view on programming: "Visual Studio knows how to code the silly
SQL statements so I don’t need to."
Read it for yourself. It is amusing. If you find you are
agreeing with him, find a profession other than IT. Why? You wouldn’t
get it...
New Camera - new hobby!
I picked up a DSLR last week (thanks for putting yourself in the red for me Uncle Sam!), a Nikon D60 kit. Takes amazing pictures (despite my best effort to the contrary). Check out a few, I think over the weekend I took about 300 pictures!
SQL tricks rather than a coldfusion loop
Im a speed freak. Thats why I try to structure my SQL statements in such a way that I can just have Coldfusion loop and output them, without any Query of Queries, swapping data to arrays, or other such nonsense. Today, I needed to return a query object that would find any auctions in a given date range for a calendar display. My problem was, I wanted each company (4 companies) to return 1 record per day that would show the number of auctions on that day, even if the day had no auctions at all. I know I could have done my auction query grouped by date, then looped a month and QoQ back to my auction result, but thats messy!
I could even have made a table containing every date for the next 20 years and joined too it, but that seems too manual to me.
Here is what I came up with. This uses recursion to create a recordset with each date in my requested range, then joins to the recordset in memory. How cool is that? Its zippy fast, and super easy to do a grouped output with. Here is the SQL, hope it helps someone!
/* Declare start and ending dates */
DECLARE @startDate as datetime
SET @startDate = <cfqueryparam cfsqltype="cf_sql_date" value="#startDate#" />
DECLARE @endDate as datetime
SET @endDate = <cfqueryparam cfsqltype="cf_sql_date" value="#endDate#" />
/* Build temp table of days in my range */
;WITH DayList (DayDate) AS
(
SELECT @startDate
UNION ALL
SELECT DATEADD(d, 1, DayDate)
FROM DayList
WHERE (DayDate < @endDate)
)
/* Grab the days and sub-query for auction count */
SELECT
A.DayDate as AuctionDate,
B.Name as Company,
B.Url as CompanyURL,
(SELECT
Count(*)
FROM
auction
WHERE
companyID = B.ID
AND active = 1
AND deleted = 0
/* necessary to do this so that the times dont mess up the comparison */
AND CAST(FLOOR(CAST( startDate AS float)) AS datetime) = A.dayDate
) as AuctionCount
FROM
dayList A, Company B
ORDER BY AuctionDate ASC