Anna Hadnagy

I stumbled upon this great Q+A on Stack Overflow when I was looking for options to cast datetime for a report. (The customer didn’t like our standard mm/dd/yyyy format.)

I actually like the third answer the most, because it gives you a chart on all the styles. A pretty helpful shortcut, if you ask me.

union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
output, style
Apr 28 2014 9:31AM, 0
04/28/14, 1
14.04.28, 2
28/04/14, 3
28.04.14, 4
28-04-14, 5

Helpful, if you ask me, if you want to depart from your usual convert(101) format. I don’t even know why the answer is not marked correct. Check out the article here, and bookmark it for future use.

And I realize I was the one who launched those procedures

And I pretend I totally understand the whole query plan

Tl;dr – SQL server 2008R2’s results to grid output truncates string values above 43,680.

Conclusion: if half of your script is missing, it might still be there.

When the server ran out of both memory and disk space. On both drives.

With a promise of another 32 GB of memory for the analytics server just to cancel the project in the last second:

I’m going to tell you a little secret. Come, sit closer.

You know your running chart for every other dashboard, showing

the SUM / Average / Top / Least / etc.

of Sales / New customers / Quotes  / etc.

for the last…

1 hour / 1 day / 1 week / 4 weeks / 12 months / etc…. ?

In SQL world I’ve been solving the problem with dateadd() getdate().

SELECT
sum(case when month(orderDate) = dateadd( mm, -1, getdate()) then orderAmount end) as [total order amount last month]
FROM
GoatOrders
GROUP BY
month(orderDate)

Of course if you want your chart to contain the last 12 months, you’d have to repeat the select 12 times. No brainer, redundant query.

Here comes this little trick I’ve been using:

SELECT
SUM(orderAmount), month(orderDate)
FROM
GoatOrders
WHERE
orderDate >= dateadd(yy, -1, getdate())
GROUP BY
month(orderDate),
year(orderDate)
ORDER BY –this is where the magic happens
year(orderDate) desc,
month(orderDate) desc

The ORDER BY has it all.

On a completely unrelated note: USA won 2-1 against Ghana!

Conclusion: Now you know how to write even shorter code for your running charts.