Day: April 9, 2012


-- I awoke from a dream this morning muttering,
-- "Well, I better to get to work
-- on that data warehouse."
--
-- The dream was one of those SQL coding dreams I used
-- to have a lot, when I was working as an SQL coder.
-- Screens filled with half-written SQL queries written
-- against the infamous ARAMARK datawarehouse (or my
-- surreptitious 2 terabyte copy of it that was running
-- on the "National Accounts Stealth Server" that I'd
-- constructed under my desk), in which I'd denormalized
-- the database to speed up pivot table queries of
-- various kinds. Dreams filled with feelings of anxiety
-- and urgency and frustration. I almost never have
-- those dreams, anymore - I haven't done a single
-- line of programming in almost 5 years, now. I'm a
-- happier and more balanced person, because of
-- it (though not perfect, oh no, I know).
--
-- But sometimes dreams do weird things, and this
-- early dawn, as my cold medicine wore off (I'm combatting
-- an unpleasant flu currently), I was plunged
-- into a vivid relapse of my database-hacking days. And I
-- awoke with a sense that I was behind on some ill-defined
-- but very important project, some report due
-- that day and the queries were running too slow, some
-- effort to find some ineluctable fragment of
-- information or some anomalous, dangerous data point
-- that the sales people insisted shouldn't exist and
-- would embarrass us in front of the customer, but
-- lo and behold, there it was glaring up from the
-- spreadsheet.
--
-- I made some of my Brazilian instant coffee, and
-- ate toast and an apple for breakfast.
--
-- Below is a dummy query from a SQL educational
-- website. Just to give a flavor or my dreaming.
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + [MonthName] + ']',
'[' + [MonthName] + ']'
)
FROM dummy.dbo.ListMonthNames()
ORDER BY monthid
--
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
SELECT
YEAR(OrderDate) [Year],
DATENAME(MONTH, OrderDate) as [Month],
SubTotal
FROM Sales.SalesOrderHeader
) TableDate
PIVOT (
SUM(SubTotal)
FOR [Month] IN (
' + @PivotColumnHeaders + '
)
) PivotTable
'
EXECUTE(@PivotTableSQL)
-- What I'm listening to, right now.
-- Kray Van Kirk, "You to me." There's no youtube or other
-- online video for this song. So... find your own copy - his
-- music is free from his website (I wonder... I should make my
-- own youtube. I wonder if he would object?