Stored Procedure help

• ScottPletcher

  SSC Guru

  Points: 101200

  More actions

  September 17, 2012 at 1:06 pm

  #1538787

  GSquared (9/17/2012)

  FROM (

  SELECT CAST(CAST(@year * 10000 + 0101 AS char(8)) AS datetime) AS month_start UNION ALL

  SELECT CAST(@year * 10000 + 0201 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0301 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0401 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0501 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0601 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0701 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0801 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 0901 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 1001 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 1101 AS char(8)) UNION ALL

  SELECT CAST(@year * 10000 + 1201 AS char(8))

  ) AS months_of_the_year

  [/code]

  The readability issue for me isn't so much about the DateDiff functions. Though those were only readily readable because I use similar functions to do things like get 1st-day-of-month, or (pre-SQL 2008) to strip time from DateTime values.

  What I had to stop my eyes and parse more slowly was the bits around the DateDiff, and this part "SELECT CAST(@year * 10000 + 0201 AS char(8))". The use of Union All data-type precendences to implicitly convert those strings to dates also took a moment of "what is that doing?" for me.

  As per my original post, I'd probably really use a persisted Calendar table for this. After all, this is a simpler query than any of these:

  SELECT MonthName, count(*)

  FROM Common.dbo.Calendar

  WHERE Year = 2012

  AND WeekDay IN (7, 1)

  ORDER BY MonthNumeric;

  I have a table that already has all those columns pre-calculated. Isn't dependent on server settings, etc. Could modify the query with one more line in the Where clause and get holidays, too, if that's what this is for. Or invert it and get workdays per month with a simple Not In. And so on. That's backwards compatible and cross-database-engine compatible, with anything that'll run any reasonable semblance of ANSI SQL. Does have a dependency on having table dbo.Calendar in database Common, but I have that on every server I manage currently.

  Interesting. I figured naming the derived table "months_of_the_year" and showing lines of input values with 01, 02, 03, ..., 12 in them would make it reasonably self-evident that this was generating, hmm, the months of a year :-).

  I only CAST() the first value to a datetime to attempt to have the code self-document what it was doing. You can remove it you prefer, and make the first month the same as the others:

  SELECT CAST(@year * 10000 + 0101 AS char(8)) AS month_start UNION ALL

  I'd avoid a calendar tables for the q as stated because it's way more overhead than necessary; i.e., I don't need any I/O to calculate the # of Saturdays in the months of a year.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".