Group by Months/Weeks

Beautiful bit of code for counting valid fields and grouping the results in month/week chunks – simply replace the %%INTERVAL%% with either WEEK or MONTH (or any other valid DATEADD identifier, for that matter) and specify the table and %%DateField%% you are interested in:

    ;WITH d(d) AS 
    (
      SELECT DATEADD(%%INTERVAL%%, n, DATEADD(%%INTERVAL%%, 
                     DATEDIFF(%%INTERVAL%%, 0, @minDate), 0))
      FROM ( SELECT TOP (DATEDIFF(%%INTERVAL%%, @minDate, @maxDate) + 1) 
        n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
        FROM sys.all_objects ORDER BY [object_id] ) AS n
    )
        SELECT     
            --COALESCE(op.Id, @ID) AS ID,
            d.d AS Date,
            count(op.%%DateField%%) AS CountOfItems
        FROM  
            d LEFT OUTER JOIN
            (select * FROM Ops o WHERE 
               o.Operation_Project = @Project_ID or @Project_ID = 1) op 
               ON op.%%DateField%% >= d.d AND 
               op.%%DateField%% < DATEADD(%%INTERVAL%%, 1, d.d)  
        GROUP BY 
            --op.Operation_Project, 
            d.d 
        HAVING 
            d.d < (SELECT MAX(o.%%DateField%%) FROM Ops o 
                   WHERE o.Id = @Project_ID or @Project_ID = 1)
        ORDER BY 
            d.d

Squiddling Around Salisbury

Squiddling around Salisbury tomorrow I found that I had missed something quite astonishing about the town in the subsequent 15 visits over the next so-many-years.

It’s an easy thought to conjure – but when I looked about at my life, in those years to come, I couldn’t help but think: ‘Goodness gracious me – where is the Lotus garage that once I held in such high esteem?’

Clearly, it’s still there – but, I find, it remains far less interesting than it ever used to be. Not too dissimilar to the rest of my life – a problem I have been battling with since my return from India; an endless chain of tainted love, boredom and waiting.

I wonder, then, lonely as a luxo lamp, that jumps on high and squashes small colourful balls at the start of films. Or was it Luxor – a distant land somehow reminiscent of Egypt's greatest Kings and Queens – or, perhaps, gamblers? …as I was saying: I wonder, not wander, WONDER at the sheer size of the world these days – not that geographically it’s extended itself that much – but, more importantly, how small it has got; I mean, I can write this unfeasibly meaningless pseudo-article and some idiot makes the electricity to transmit it with the assumption that it might just be as important as a piece of news; what’s more, I look out from the computer screen and wonder why you are actually reading it…  Hmmmmmm…

Have you truly not got anything better to do with your life than consume other people’s writings? Perhaps it is time to start again and leave the house, leave the phone where it is, visit a neighbour/friend/associate and talk about doing something different – something you have yet to do; something that is outside the general pattern of your life – avoid the news, avoid the TV, avoid the settee… 

Or: you could go to the pub. I hear it’s quite nice there. 

Here – something new – a new word:

bliesfelt [ˈblys-felt] Brit Slang

adj

1. weary; tired out

2. weary of food; overly full, esp after an interview and too much pasta the previous night

This is how I will be mostly feeling tomorrow. See if you can remember it and use it…  If you can’t what went wrong with your brain?

Windows 8 and The Beach

OK, let’s get one thing straight from the off – I am not referring to ‘The Beach’, the well known book-to-film creation by Alex Garland. Incidentally, it was directed by Danny Boyle, the same guy that designed the opening to the London Olympic games – just after completing a film where someone gnaws their arm off in disappointment. I’m sure I’ve heard that somewhere before.

Anyway, not that beach. But I digress, you’re far more interested in the whole Windows 8 thing, eh? Aren’t you? I mean that’s what you searched for when you got here, right? Good – then I’ll begin:

It’s installed. I partitioned away a little side alley of hard disk space to allow me to start up in Windows 7 or Windows 8 – a neat little trick which gives me a fancy blue menu with icons (none of this text based rubbish we’ve been used to in the past). Nothing particularly clever required – simply bought Windows 8 online, downloaded it, burned it to a CD (all of which it did without me having to intervene – Windows 7 will burn DVDs; although, I was told that Windows XP and Vista may require additional ISO to DVD burning software for this bit).

Once burned a reboot caused my PC to start from the DVD and after about half an hour I had a dual boot hard disk with Windows 8 unable to connect to the internet. Obviously, had I not bothered to create this state of dualistic preposterousness, the upgrade of my existing Windows 7 would have been a breeze and my network connection intact.

So, having got over feeling somewhat preposterated, I thought I’d try using the thing (Windows 8 that is). First things first, I connected my Windows login to a Hotmail account which made everything fantastic – I won’t tell you why, I just wrote it down and it was the most tedious sentence I have ever written – so find out for yourself.

I’m playing music, writing software, writing a blog post, browsing Wiki (honestly, I actually ended up reading about Azure Damsel Flies and then the colour Azure blue – the page was so enticingly rendered before me); Skype’s running in the background and from time to time I have asked Google (with my microphone) to search for ‘Penn Treebank tags’. The latter was a pain in the backside – and not just because of my seated position, but, more importantly, because I had to put on an American accent for it to understand me properly.

A fantastic old book ‘I Seem To Be A Verb’ caught my eye during my web travels within Windows 8. I tried to search for more information with Google – but Google insisted I was looking for a book called ‘I seem to be a…’: ‘phone’, ‘fun’, ‘above’, ‘B of A’, ‘availab’ or a myriad of other quite reasonably abstract things (given the content of the book). Apparently, it’s going around that ‘I seem to be an idiot for not typing the request in the first place’. Which, I have to say, I tend to agree with.

It’s by this chap who seemed to be interested in ‘the sustainable planet’ long before it caught on – if you can’t be bothered to do anything else go to Amazon and request it on Kindle – just to annoy them, who knows, they may do it. Remember, if every trim-tab does it the Queen Mary will change direction – or something like that; although I think it’s just wishful thinking.

Anyway – Windows 8, seems pretty good; and this whole ‘mobile technology’ thing that they have going on with their apps is saving memory and processor time something extraordinary (when apps are not directly in use they pretty much drop down to no CPU usage – so, I guess I’ll use less electricity and I won’t memory-fault – or something) – it’s also seemingly much easier to understand what’s going on without having to resize windows (everything is full screen by default).

Right – must get on as ‘Time is a Jailer’ and with the music on this loud the ‘Sound of the Suburbs’ will be disturbed despite my double glazing (‘Right Through You’). Finally, in Total Contrast to all that: ‘Takes a little time to get it right, Don’t you believe in love at first sight…’; reminds me of a weird pub in Oslo – or does it?  And on that note…

I’ll be utterly bedgasmed in just a few minutes – and no, that has nothing to do with living in Essex – thank goodness; oh and damn, I was planning to go to the beach today and stand on a groin... Maybe next week.