Saturday 31 October 2009

I live here


Halloween, originally uploaded by jonathanallen69.

Sunday 18 October 2009

Pretty things


DSC_2477_cropped, originally uploaded by jonathanallen69.

Wednesday 14 October 2009

Who pays the piper, calls the tune...

I have just watched an episode of Robert Llewelyn's Car Pool - here. It was the show where he takes Sharon Corr to Heathrow Airport and along the way they discussed the topic of music sales and the effect of the internet. This has been something that has rattled around in my head for a while now and I must admit to a certain amount of wonderment and confusion.

I may be wrong but the music industry (and entertainment in general I guess) must have its roots in the distant past as some sort of hybrid between a court jester and a wandering minstrel. These people travelled from place to place doing what they do and getting a few coins payment for it. Their show was new to each town and they paid their way from the money they made. If the show was no good they ended up being a blacksmith/archer/maid/etc as a way of living. Presumably at some point the good ones got a name and when they came to town people travelled to see the show and they made more money. It was still live performance, new to each location. They learned/wrote more songs and played those to locations on the second visit, and so on ...

Now, again - I presume, someone must have spotted the role of MR 10% and gotten into the "Stick with me, I'll make you a millionaire" groove of promoting these popular acts. They run ahead to the next location, do some marketing/promo work, create some excitement and build the interest for the act arriving on the horse/carriage next week. then once the act arrives Mr 10% moves to the next location. Everyone is happy, the act is getting more popular (and more $£$£$), Mr 10% is getting £$£$ for little effort and no creative talent and the crowds are seeing a good act with new material.

Fast forward and Mr 10% takes on a few more acts, maybe some not so good ones too. He's getting a cut from all of them but not doubling ot tripling his work. Still everyone is happy, especially Mr 10%.

Then some clever boffin invents "recording" - whatever media wax,tape,vinyl. Now the act and Mr 10% dont need to travel. They record the music and flog it. Much money is made by all. Maybe the audience is getting short changed? That's opinion. Anyway, its not really practical to copy the recordings and distribute them so the act is still fresh when it tours. That boosts media sales. Acts and Mr 10%s get ridiculous incomes.

Then some genius invents computers the internet and then Billy Jones can copy CDs, DVDs and share them with anyone else who has an internet from the comfort of his own home. Suddenly the sales start to drop. Live music is then the best way to gain income as people still want new live music, well played and the thrill of a concert atmosphere.

Dont get me wrong, I want as many people to succeed in as many ways as they can but it strikes me that Music (and the entertainment industry as a whole) is not a service. As such there is no 'right' that those people have to an income, whether it is at a subsistence level of 100's of years ago, the phenomenal levels of the 60's,70's,80's or the lower but still a comfortable amount thank you very much of today. If we got a service - medical aid, farmed produce etc then they would be justified to argue it was paid at a reasonable level or not. I agree that copying the entertainers work is wrong and in many cases illegal but that isnt my point. The industry needs to accept that its possible and react accordingly. If they are not happy that their sales are down and their incomes are lower then they are still at liberty to take a different career route - there is a shortage of nurses and teachers in many parts of the world. Their ability to do something creative and recreational can be passed on to people they encounter and may in time get them an income. If its not enough to satisfy them then they need to accept that 'fame' will not be knocking on their door.

Confused by slashes

Anon: "I always get confused over slashes, you know back slashes and forward slashes"

Me: "Oh?"

Anon: "Well, you'd think a forward slash would be on the right of a keyboard and a back slash on the left of a keyboard"

Me: "They are"

Anon: "..."

Anon: "..."

Anon: "..."


Additional:
Anon is a web developer.

Tuesday 13 October 2009

Just trying out ping.fm

Friday 9 October 2009

Shaving Review

"... I shave my legs with all of the grace and finesse of a raccoon pawing at a garbage pail ... "

Monday 5 October 2009

How to group time data by time periods

This solution builds on using a 'numbers' or 'tally' table as championed by Jeff Moden here http://www.sqlservercentral.com/articles/T-SQL/62867/. Read that article for the full description of how to use set based solutions for efficient SQL queries.

Here I am considering a large log table that has entries that are time based and how to aggregate data based on various time bandings. For example if you want to see how many visitors your website had in 10 minute bands through the day or how many application errors you get in the server log per hour and so on...

-- First things first we will create the Numbers table
------------------------------------------------------
-- Things to note:
-- We are using a temp table in this instance as it is a small one but it is often the case that DBAs add a numbers table to a production database or have one in their metadata database that they can use.
-- Select a sensible number of rows to insert - relevant to the task in hand. If you are dealing with data that you want to analyse over a year and its date based then 1000 rows will be more than ample, its excessive to use 1,000,000! Especially in this instance where its a temp table, remember how temp tables work and how this might affect your system.

--DROP TABLE #numbers
IF OBJECT_ID('tempdb..#Numbers') > 0
BEGIN
RAISERROR('There is already a table called Numbers, please use that or select a different name.',0,0,1) WITH NOWAIT
END
ELSE
BEGIN
CREATE TABLE #Numbers
(
Num INT NOT NULL ,
PRIMARY KEY CLUSTERED ( Num )
)
RAISERROR('Numbers table created.',0,0,1) WITH NOWAIT
-- Insert values to a given (sic Relevant) value
-------------------------------------------------
INSERT INTO #numbers
SELECT TOP ( 1500 )
ROW_NUMBER() OVER ( ORDER BY c1.column_id )
FROM
master.sys.all_columns c1
CROSS JOIN master.sys.all_columns c2

RAISERROR('Numbers table filled.',0,0,1) WITH NOWAIT
END



-- Right the numbers table is ready for us, lets set some local values
DECLARE @Start DATETIME -- when we want to start anlysis
DECLARE @interval INT -- the grouping period we want to use


SET @interval = 60
--where 10=10 minutes;30=half hourly;60=hourly;240=4hourly;720=halfdaily;1440 = daily
SET @Start = '05-Oct-2009 00:00' ;
-- any date you want as the start date

-- Using the numbers table we can create a CTE that has the start and end times over which we want to analyse the data
WITH times
AS ( SELECT n.Num,
DATEADD(n, num - @interval, @Start) AS [LowerTime],
DATEADD(n, num, @Start) AS [UpperTime]
FROM [#Numbers] AS n
WHERE [n].[Num] % @interval = 0 -- use modulo to get required intervals

)
-- simply join our source data to the CTE using the date column in the join
SELECT [times].[LowerTime] AS [Interval Start],
COUNT(*) AS [Sessions]
FROM [dbo].[Log] AS dl
INNER JOIN [times] ON [StartTime] BETWEEN [times].[LowerTime]
AND [times].[UpperTime]
GROUP BY [LowerTime]
ORDER BY [LowerTime] desc


Your results will look something like this:









IntervalStartSessions
2009-10-0511:00:00.000320
2009-10-0510:00:00.0002892
2009-10-0509:00:00.0002490
2009-10-0508:00:00.0002264
2009-10-0507:00:00.0001249
2009-10-0506:00:00.000106
2009-10-0503:00:00.0001
2009-10-0502:00:00.0007


Simply adjusting the value for @interval will group your day in smaller or larger periods.

It is simple from here to adjust the script to calculate days or months rather than minutes and it could all be added into a stored procedure to provide a way for users to query their own data via some interface that allows them to supply a parameter for the interval they need - this may be Reporting Services, Excel or a bespoke interface.

AddThis

Bookmark and Share
 
Google Analytics Alternative