|
I've just found a another report query by my favourite genii, which gives call volumns per hour. You see, datetime columns are once again frowned upon, so we store the call time formatted as 'hh:mm:ss', then we 'group' by the call time as follows:
, CASE WHEN Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) END AS T1_2
, CASE WHEN Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) END AS T2_3
, CASE WHEN Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) END AS T3_4
, CASE WHEN Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 END) END AS T4_5
, CASE WHEN Sum(CASE WHEN TimeText>='05:00:00' And TimeText<='05:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='05:00:00' And TimeText<='05:59:59' THEN Cost ELSE 0 END) END AS T5_6
, CASE WHEN Sum(CASE WHEN TimeText>='06:00:00' And TimeText<='06:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='06:00:00' And TimeText<='06:59:59' THEN Cost ELSE 0 END) END AS T6_7
For each of all 24 freaking hours. And, BTW, we don't need no steenkin' isnull() function either.
JUST IN: There are 48 of these CASE lines, 24 for incoming calls, and 24 for outgoing calls. 
Last modified: 10mins after originally posted --
|
|
|
|
|
Just as a matter of interest, assuming you are not in control of the database (i.e. you are constrained by the fact that the time is stored as a text string and you cannot change that fact), how would you implement this requirement? I've been scratching my head trying to think of the easiest, most elegant way to achieve it. I would probably try to take a substring of the TimeText to get the hour and then group by that. Does that work?
select time_hour as substring(TimeText, 1, 2), sum(Cost)
from MyTable
group by time_hour
I haven't tried that, it's just off the top of my head. Does it achieve the same end result?
|
|
|
|
|
Something very close works like a dream:
select
isnull(sum(case when Call = 1 then Cost else 0 end), 0) as Incoming
, isnull(sum(case when Call = 2 then Cost else 0 end), 0) as Outgoing
, left(TimeText, 2) as Hour
FROM
CallRecords crd
group by
left(TimeText, 2)
|
|
|
|
|
You don't need to check for nulls, because SUM will automatically exclude them.
Don't know why they wouldn't use a TIME column, unless they were using SQLite, but even there you can just do:
SELECT strftime('%H', TimeText) AS Hour, SUM(Cost) FROM MyTable GROUP BY Hour;
|
|
|
|
|
select count(PK) Count, DatePart(hh,ColumnTime) Hour
from dbTable
group by DatePart(hh,dbColumnTime)
|
|
|
|
|
First, you use a varchar(20) default '' to store a six char date in the form yymmdd. Users have the luxury of an extra fourteen chars to add willy nilly romantic annotations to the date. Savvy data entry clerks may even squeeze a well formed XHTML tag in there.
Then, when the huns appear over the next control-break horizon, and you need the real DateTime you promised them, you divide and conquer. A bunch of little two character numbers quickly evaporate into two character numbers. Except the year pair, '09'. In the shocking assault on decency everywhere, teh geinus grugru of date parsing expertly recognised that the '09' needed '20'. Why waste two innocent zeros and add '2000', when you can add '20', as in string conctratingation, but hey, strings are for physicists and yoyos, so lets just add 20.
In the following horrific, true life example of unprecedented savagery, our valiant knight takes a string value for detailLastDate; of '090722' and with a wave of a hanky and quickly stuffing a rabbit into a hat for distraction, presents The Date Parsing canon (emphasis mine):
DateTime time2 = new DateTime(int.Parse(20 + detailLastDate.Substring(0, 2)), int.Parse(detailLastDate.Substring(2, 2)), int.Parse(detailLastDate.Substring(4, 2)));
Isn't it wonderful how he can just add the integer 20 to the substring '09', and get a year value of 2009? I am forever in awe.
Last modified: 9hrs 10mins after originally posted --
|
|
|
|
|
I suddenly feel lucky (see below)
|
|
|
|
|
Isn't it wonderful how he can just add the integer 20 to the substring '09', and get a year value of 2009? I am forever in awe.
Whether or not it was reasonable for programmers in the 80's and 90's to figure their code would be obsolete before the year 2000, I see no reason to believe that any code written today will not be obsolete before the year 2100. At least the programmer deserves credit for not trying to add punctuation to convert the string to mm-dd-yy form and doing an unadorned parse on that (which would succeed or fail depending upon locale).
|
|
|
|
|
supercat9 wrote: I see no reason to believe that any code written today will not be obsolete before the year 2100.
I strongly disagree. IF y2k and the last decade have taught us anything it's that crufty old code doesn't die, it just gets wrapped in interface translation layers to feed newer systems.
The European Way of War: Blow your own continent up.
The American Way of War: Go over and help them.
|
|
|
|
|
I strongly disagree. IF y2k and the last decade have taught us anything it's that crufty old code doesn't die, it just gets wrapped in interface translation layers to feed newer systems.
Code can be around for awhile, certainly, but from a user I/O perspective I don't see much point to using four-digit years for most purposes (from an internal-storage perspective, storing a year as two digits is generally silly with or without century-wrap issues). If an application is only going to deal with dates in the near future or recent past, there's no reason to require users to waste keystrokes typing a leading "20", nor is there any reason to waste space printing or displaying a "20" in front of years on paper or screen. If, seventy years from now, a printed document bears a date of 01-01-76, it will almost certainly be obvious whether the document was produced in the 1970's or 2070's. Even if there were no further improvements in printing technologies, the earliest possible ambiguities for documents printed on quality paper wouldn't occur until 2090 or so; even then, it should be obvious which documents were printed in 1990 and which ones were printed in 2090.
|
|
|
|
|
So, was this a replacement for an older program that used "19 +"?
|
|
|
|
|
varchar(20), you are lucky...
somebody who's name i will not mention, there it's my only "programming" colleague, is coming from VB6 and now in .Net all data types are still variant(in her head). Adding numbers to strings is standard... Type Casting? What is that? Turning on option strict results in hundreds of errors , in one project with one source file, of 3000 lines...
and tables, don't ask ... today my heart stopped a moment with this...
CREATE TABLE `tkolkinfo` (
`tkolkIK` int(11) NOT NULL auto_increment,
`tKolkClientId` varchar(100) NOT NULL default '',
`tKolkTimestampBerichtOpmaak` varchar(100) NOT NULL default '',
`tKolkTimestampDateKolkDone` varchar(100) NOT NULL default '',
`tKolkTimestampHourKolkDone` varchar(100) NOT NULL default '',
`tKolkTimestampDateTimeKolkDone` varchar(100) NOT NULL default '',
But at least in the company where i work next to .Net there is windev, helping me to forget all the IT horror and dream away on a beach with a beautifull girl: http://www.pcsoft-windev-webdev.com/WD14brochure.pdf[^] (this was sarcasm for the ones who didn't notice...)
It feels good to learn and achieve
modified on Monday, August 24, 2009 10:47 PM
|
|
|
|
|
Here's the link : A simple cross-platform program[^]
As the title says, I think that practically that entire article qualifies as a coding horror. Here is just one example : the article explains how to calculate the size of a bitmap file then that size value is literally sprinkled throughout including even sizing arrays based on it.
It is just astonishing to see that and what is worse is somehow it was approved for publication. 
|
|
|
|
|
I've inherited one of the worst code-bases I've ever seen in 9 years of .netting.
I've spotted this little gem scattered like gingerbread-crumbs thoughout the code:
DateTime date = DateTime.Parse(DateTime.Now.Date.ToString("dd/MM/yyyy"));
To make it worse this code is repeated not just in different classes but in the same class
To add piquancy, the variable being set is just called date, not today or todaysDate or something bit more sensible, so I had to work out what it did. I still can't work out why, I only know it makes my eyes bleed....
|
|
|
|
|
|
// Try this:
DateTime now = DateTime.Now;
DateTime date = new DateTime(now.Year, now.Month, now.Day);
|
|
|
|
|
One thing it will do is break if you run it in America.
If the date is 5th March 2009, the ToString conversion will give you 05/03/2009. But because no format is specified on the Parse method, it will assume the default date format, which in America is MM/dd/yyyy. So 05/03/2009 will get converted to 3rd May 2009.
My guess would be this is someone from a Java background because java.util.Date doesn't have an equivalent to .NET's DateTime.Date property, and it's not so easy to strip off the time part. The correct way to do it in Java is with java.util.Calendar, but lots of people use this sort of clumsy format/parse approach.
|
|
|
|
|
Yup, a java-based programmer was involved, I did suspect something like that (there is lots of other Java-inspired "Goodness" in the code). I would have excused him, but he did manage to use DateTime.Now.Date as part of this coding beauty. Ho hum.
|
|
|
|
|
Tell him about properties; people unfamiliar with Windows are propably not familiar with properties either. Java didn't have them when I used it.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Java still does not have properties. There was a big argument about whether they should be added to Java 7 or not (the next version due next year) and in the end they were not included.
Personally, I don't miss properties in Java, possibly because I write mostly server-side Java apps, and properties are more useful for simple binding to UI controls. You can do that with Java beans and Swing but it's a bit messy.
|
|
|
|
|
It happens both ways round. Because the syntax for Java and C# is very similar, it's easy to switch between the two languages and end up coding things inappropriately.
|
|
|
|
|
Actually you're dead wrong in so many ways. 
1) Since ToString() and Parse() both use the *same* culture, it doesn't matter what that culture is.
2) The culture used is not necessarily the system default. It is the current culture, which can be set programmatically to whatever we'd like it to be.
3) The default culture doesn't actually depend on where the machine is located.
I once wrote a disposable class called CultureBubble in order to easily run portions of code with a specific culture, like this:
using (new CultureBubble("FR"))
{
foo();
bar();
}
This technique is quite useful, as it requires minimal coding compared to writing logic for dealing with different cultures everywhere data is parsed or presented in culture-dependent ways.
|
|
|
|
|
Ha,
I come from a Java background so my first thought was "What's wrong with it"
I switched over to .NET about 7 years ago. so now I would write it like this:
DateTime.Now.Date.ToShortDateString()
|
|
|
|
|
7 years of .NET experience and you don't see what's wrong with it? Or is there a joke in there that I'm missing? (Monday morning, brain not fully engaged yet)
|
|
|
|
|
Drink some coffee, it was a joke
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
