Spurious Logic

Take that! unnecessary table variable! Removed by the power of common table expressions!

I have a table in the form:

But what I really need is a table with an extra column with a sales id for each seller, like this;

In a past life I would have written the alter script to add in the column, with an empty field (null or -1); created a temporary table to iterate over every ‘Alice’ sale and set those values, followed by those for ‘Bob’. And this is what you get when you’re thinking procedurally. Using Common Table Expressions and a Partition makes this far more efficient (at the cost of more complicated code).

WITH cte (ID, seller, sales_id)
AS
   (
      SELECT
         ID,
         seller,
         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)
      FROM sales WITH (NOLOCK)
   )
UPDATE S
SET S.sales_id = C.Pattern_ID
FROM sales S
INNER JOIN cte C
   ON S.ID = C.ID
AND S.seller = C.seller

And hey! a legitimate reason to actually use a <table> on a webpage without designers shaking their goate-ed heads at my foolishness.

Following on from my gearshift analogy yesterday, my personal programming mental block is the syntax of the switch statement. I know how it works, I know what it does, I just can’t get the syntax right without looking it up. Regardless of the programming language. Java, C# or TSQL. I get as far as CASE … before I have to research it. It’s a pain in the ass but I just can’t get it to stick and I don’t use it often enough to memorise it properly. Ah well.

Just for reference, in TSQL this is what you want to be doing:

SELECT
   CASE tab1.col_name
      WHEN 1 THEN 'one'
      WHEN 2 THEN 'two'
END
FROM table1 tab1

Yes it’s trivial, but it just won’t stick. Maybe it will now.

And this is what happens if you’re doing SQL and then get shunted up into procedural code and then back down again.

In C#:

• you have variable names like local_Variable_Name instead of localVariableName.
• The fact that you can call methods without prefixing the name with EXEC seems… just, wrong.

In TSQL

• you keep on forgetting to use SET before variable assignment.
• the fundamental set based versus procedural programming approaches which means that fast running procedures become very, very slow when you get them wrong.

oy vey. The trials of a generalist.

<meta> Having great big images and cleverly formatted text is great for show and all but it does mean that I’m not posting as much as I’d like, so, I’m going to try and set up a separate Articles section for longer/better posts and have a the usual blog style on the front page. </meta>

image from wikimedia commons

Well, 3rd week of January resolution. I’m going to track my time and the task I work on in the office as accurately and honestly as I can.

Currently we’ve got a very rough cut timetable system where everyone fills in their time after the fact using very broad work categories. 99% of the time this works absolutely fine. Last week we had a request to give a total amount of time for a particular project. Of course no-one on the team was tracking this one project distinct from the others so the best we could do was dead reckoning. It’s definitely wrong but it should at least be in the right ball park.

A co-worker turned me on to Grindstone which is a fine granularity recording mechanism for windows. You set up tasks, hit the start button and away you go. At the end of the day/week you can get a bunch of nice reports on the time you spent on the different task.

The only difficult bit is to be honest with it. That time you spend on youtube? You’ll be tempted to mark it as “working on defect” but you’re only tricking yourself.

I would hesitate to recommend it for project managers to enforce it on your employees because it rely’s entirely on the honesty of your staff (which shouldn’t really be a problem) but more than that, it gives the developers a very big brother feeling. It’s not enjoyable working if there’s no wiggle room and if developers can’t even pretend they enjoy their jobs, then they won’t be very productive.

Image is taken from commons.wikimedia.org (resized and resampled)