SQL tricks rather than a coldfusion loop

Posted by [Justice] in Coldfusion on June 4, 2008

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!

<span class='cc_comment'>/* Declare start and ending dates */</span>
DECLARE @startDate as datetime
SET @startDate = <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_date"</span> value=<span class='cc_value'>"#startDate#"</span> />

DECLARE @endDate as datetime
SET @endDate = <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_date"</span> value=<span class='cc_value'>"#endDate#"</span> />

<span class='cc_comment'>/* Build temp table of days in my range */</span>
;WITH DayList (DayDate) AS

(
   SELECT @startDate

   UNION ALL

   SELECT DATEADD(d, 1, DayDate)
   FROM DayList
   WHERE (DayDate < @endDate)
)

<span class='cc_comment'>/* Grab the days and sub-query for auction count */</span>
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
      <span class='cc_comment'>/* necessary to do this so that the times dont mess up the comparison */</span>
      AND CAST(FLOOR(CAST( startDate AS float)) AS datetime) = A.dayDate
   ) as AuctionCount
FROM
   dayList A, Company B

ORDER BY AuctionDate ASC

Digg StumbleUpon Facebook Technorati Fav newsvine reddit FARK Google Bookmarks
  1. Gus

    #1 by Gus - June 5, 2008 at 7:20 AM

    You should be able to do this without a temp table. I haven't tested the tsql below, but it should be pretty close. \r\n\r\nDECLARE @startDate as datetime\r\nSET @startDate =\r\nDECLARE @endDate as datetime\r\nSET @endDate = \r\n\r\nSELECT\r\n CONVERT(char(10),A.startDate, 101) AS AuctionDate,\r\n B.Name AS Company,\r\n count(*) AS AuctionCount\r\nFrom Auction A\r\nINNER JOIN Company B on B.ID = A.companyID\r\nWHERE CONVERT(char(10),A.startDate, 101) BETWEEN @startDate AND @endDate\r\nGROUP BY CONVERT(char(10),A.startDate, 101),B.Name
  2. Justice

    #2 by Justice - June 5, 2008 at 9:58 AM

    But that query does not return a record for every day in the requested range, if it has a matching auction date or not. ;) That was the whole point of the SQL trick, was getting something like this:\r\n\r\nCompany1 - 6/1/2008 - 0\r\nCompany2 - 6/1/2008 - 0\r\n\r\nCompany1 - 6/2/2008 - 2\r\nCompany2 - 6/2/2008 - 1 \r\n\r\nCompany1 - 6/3/2008 - 0\r\nCompany2 - 6/3/2008 - 1 etc.

Comments are closed.