LINQ-to-SQL Grouping By Week
I came across the problem of requiring to group items from my SQL database by week yesterday. After a quick Google search, I found a few other people were having the same problem. Although I must admit this was not hard, it might not seem immediately obvious to everyone.
The following code example will group items from a database by week, and is supported by SQL Server:
var byWeek = from t in ctx where t.Date >= startWeek && t.Date < endWeek group t by t.DateFieldInDb.Date.AddDays(-(int)t.DateDateFieldInDb.DayOfWeek) into tw orderby tw.Key select new { Date = tw.Key, Count = tw.Count() };
I should note that this groups weeks starting with Sunday. To start with Monday, you could try the following code (untested, but should work):
var byWeek = from t in ctx where t.Date >= startWeek && t.Date < endWeek group t by t.DateFieldInDb.Date.AddDays(-((int)t.DateFieldInDb.DayOfWeek == 0 ? 6 : (int)t.DateFieldInDb.DayOfWeek - 1)) into tw orderby tw.Key select new { Date = tw.Key, Count = tw.Count() };
And that’s it, simple as that. Let me know if you find this useful, or find a better way!
Don’t use Internet Solutions Fraud
Last week I had a terrible time dealing with a director from a company called Internet Solutions, based in Northern Queensland. These guys run the following companies:
- Yourhub
- Internetlounge
- Yourdomains
- Yourhosts
- Yourcity
I had this server colocated in a Brisbane data-centre with these guys, in exchange for offering my services to them at ungodly hours of the morning. I did this - and everything was fine for 15 months. Then, out of the blue, my server gets turned off. I panic trying to get this guy’s phone number, and when I finally do, he tells me he never knew about the arrangement (lie!) and will work out an arrangement for me to pay him monthly. This I could live with. What happens next should disturb you.
As you might know, the cost of colocation in Brisbane is about $150/month for a decent deal. This guy sends me a quote, saying he wants to charge me $1700/month PLUS backcharge me for 15 months at this rate. He ended up wanting to charge me $15000. That’s how much he would pay to run his whole ISP there!
I told him I couldn’t do that, and he held my server equipment at RANSOM. Meanwhile my clients were absolutely freaking out about how they couldn’t access their data and company records, and one threatened to sue the guy $125,000 a week that he held it at RANSOM.
After 2 days lawyers were involved, and he eventually surrendered the hardware. I had to quickly organise more colocation, which ended up being OK.
I hear he’s doing this to other people too.
Point of the story is, I recommend you think twice before using:
- Internet Solutions
- Yourhub
- Internetlounge
- Yourdomains
- Yourhosts
- Yourcity
~Shane
Close Call
In the morning as the sun escaped through the clouds everything seemed fine. Of course, there is a warm sense of comfort and familiarity that you get when standing on solid ground. I was about to go flying. I was not worrying about the people constantly telling me, “Shane, I don’t like it when you fly, what if you don’t come back?”, because I knew better. I knew, and confidently assured them that statistically flying was far safer than driving, and neither they nor myself had anything to worry about.
On my preflight (where I check the aircraft), I found my instructor double-checking everything I did, even after I assured him it was OK. He had every right to though, after all, the week before he piloted the same type of aircraft (Cessna 172N) into a paddock after the engine cut out on him at low altitude. He was just being cautious, and he continued being so after we took-off and headed out into the training area (Area 40, Southern Training Area).
At 1000 feet we encountered an amount of moderate turbulence. It was nothing alarming and it certainly wasn’t the worst I had experienced. As we bounced up and down my instructor looked at me with a pondering face and asked, “was that rough running?” Rough running is when the aircraft’s engine runs less than ideally, perhaps jerking the plane around, backfiring and losing power. I didn’t notice any loss in power and I thought it was merely the turbulence jerking the plane around. “He must be paranoid,” I thought and told him that I didn’t hear or suspect anything by reading the instruments. This was the truth.
As we continued into the training area my instructor asked me to do a practice forced landing. This is practice for the situation where the engine cuts out and you need to find somewhere to land quickly. “Alright, power off, attitude, carby on, check mixture, check fuel.” Everything was nominal and we continued in our circuit pattern for the practice forced landing. I recycled the engine after 1000 feet of descent to prevent engine fouling, and progressed down. I was not expecting what would unfold next.
Unfortunately I missed the approach due to my overly precautious early base turn, so the practice would not have been successful. We were basically too high to land and would have overshot the field. I took the carby off and put in maximum power to do a go-around and climb back to normal altitude.
Suddenly the plane shook back and forth and the power dropped considerably – so much so that with full power selected we were achieving only about 40% power. This was not enough power to keep the plane in the air and we were forced into a descent – a real descent – at the low altitude of 1200 feet.
“Taking over,” my instructor said as he rushed to perform the troubleshooting checks. As he adjusted the levers and knobs at 100-mile-an-hour, I was left staring in front of the aircraft at the approaching scenery.
“Paddock, paddock, trees, house,” I thought as I scanned the possible landing areas (maybe not trees and house). Was I about to die or will be fortunate enough to land safely on the ground? What if this happened when I was flying solo without an instructor? Why is the engine failing? Wait – what can I do to help?
I stared at my instructor with my eyes wide open like a rabbit staring into headlights and yelled “Mayday mayday!” In hindsight, I’m not sure what help this would have been, but it felt right at the time.
By this point of descent we were at 200 feet above ground level (dangerously low), the plane was shaking all over the place, the engine sounded very angry, and we were aimed directly into a grassy paddock. I looked around the cockpit and had a sudden urge to tighten my seatbelt – and I mean really tighten. Then the unexpected happened.
The power started recovering – yes, climbing back up, after about a minute and a half of rough running! It was not perfect, but my instructor took this opportunity to gain as much altitude as possible while transmitting his ‘Pan pan’ (distress) call to Brisbane Radar. He cooperated with air traffic control and headed straight back in the direction of Archerfield. I should note by this point that we expected the engine to fail again at any time. Our stroke of good luck with the power increasing was expected to be purely temporary, and landing in a paddock still seemed like the logical ending.
On the way back to Archerfield we flew as high as possible to give us the option of gliding to a landing strip if anything bad were to happen. Luckily, after a very wary approach, we were on the ground safely – in one piece each. We shut the engine down and prepared for the 4 hours of bureaucratics that were to come.
We met with the Chief Executive Officer of the aero club, the Chief Flying Instructor, and the Chief Safety Investigator and were interviewed separately. By the end, the probable cause of the partial engine failure seemed to be engine fouling, where deposits from the fuel collect around the spark plugs and render them useless. Fantastic! And how did I feel about all this?
It was damn scary when it happened (thoughts of impending death, etc), I was a bit nervous on the return approach to Archerfield (thoughts of impending engine failure leading to thoughts of impending death, etc), and generally OK on the ground (thoughts of hunger and McDonald’s, etc). The Chief XYZs thought I wasn’t fine. In their ‘duty of care,’ they had someone drive me home in my car, just in case I freaked out and crashed. I didn’t freak out, and now I feel good. If anything, this experience has made me want to learn more about aviation so I can master the art and, potentially, avoid death when it impends on me.
This all happened this morning, and my next flight, which I booked after this incident, is tomorrow. You know, statistically, this shouldn’t happen to me again…
Complex Math for Office
If you were ever wanting to use Complex Math in Microsoft Office, don’t fear, it’s actually supported!
To enable complex math functions, enable the Analysis ToolPak add-in. To do this, go to:
- For Office 2007: (Big office button, top left), Options, Add-ins, at the bottom press “Go” next to Manage (Add-ins), and check “ANALYSIS TOOLPAK”.
- For earlier versions: Tools, Add-ins, enable “ANALYSIS TOOLPAK”
Great! Now where’s your function reference? Turns out you boot up Help, and type “Engineering Functions.” There will be an item called “Engineering Functions (Reference)” which contains all your new functions. Most complex functions are prefixed with IM.
For some Excel examples:
- Add two numbers together:
=IMSUM(”2 + 3i”, “5 + 7i”) - Divide a real number by the conjugate of something else:
=IMDIV(5,IMCONJUGATE(A1)) - Subtract a number from a complex number defined by two fields (F1 ~ real component, G1 ~ imaginary component)
=IMSUB(COMPLEX(F1,G1), “2 - 4i”)
The possibilities are endless!
Have fun!
~
I Can Speak English
If you didn’t know already, I’m studying (cough) to get my Private Pilots’ License. This involves many exams, flying hours and bucks. Basically, I go in a few times a week and fly a light aircraft doing all sorts of manevours and navigations — I even learn to fly the plane without ANY engines and get to land without an engine :D.
A few months ago, I was ready to go for my first solo flight, when the lovely folks at CASA decided to mandate an English speaking test — even for native speakers! I went to book my test, but it was booked up for months on end! Basically, I had to stop flying for a few months to wait for this stupid exam. When the time came to do it, I walked into the room, sat down, and spoke about what I liked for 10 minutes. Grade: 6/6. Well done Shane.
Hopefully, I should get the damn license before the end of the year to go flying over Christmas :).
Here are some shots from the sky.
Cessna 172 Controls
View from the left
Morning Pilot Shane
That’s all!
Have fun!
Shane.
Columbia STS-107 - Cover up?
After some extensive reading (procrastinating doing assignments), I bumped into a rather interesting website about Columbia (space shuttle that recently was destroyed upon re-entry). Rather than the official story of heat tile damage causing the space shuttle to be destroyed, this site attempts to establish that it was actually complete failure of the onboard guidance computer and all four backups. It then links the failure to the HAARP (High Frequency Active Auroral Research Program) research institute in Alaska. Read for yourself:
- Site: http://www.columbiassacrifice.com/
- Youtube video of the re-entry: http://au.youtube.com/watch?v=JHJYJJhyG4M
Due to the enourmous amount of content on the site, you might just want to skip to “Final Conclusions.”
Peace out.
Hello Weblog-World
I had two options today: perform much needed studying; or create a blog. I don’t think I need to go further into this.
In this blog, I plan to write about things of interest. Perhaps I will post some software, write about orbital mechanics or let you know about some cool projects that I (half) finish. Or, perhaps this will be the only post.
Time will tell.