|
Looks kinda Oracle-ish -- it could be Oracle ported to SQL Server... badly.
I worked for a company that had always used Oracle (on OpenVMS), but one customer wanted SQL Server (6) (on WinNT) so a bunch of PowerBuilder idiots were brought in to write some of the worst code I've ever seen. Data access in the UI?! 
|
|
|
|
|
The wonderful thing about standards is that there are so many of them. If I recall, I create a variable (in vb.net) with the name of the SQL get-date function, and then I use that. I forget how I set it, but it was something pretty clunky like "If database type looks like SQL Server, set it to GETDATE() otherwise set to NOW()".
|
|
|
|
|
Hello,
How to get miliseconds form MS SQL Server using ADO and C++ ?
COleDateTime doesn't support miliseconds ...
Best Regards,
Irek
|
|
|
|
|
You can do it sql by using 'DatePart(ms,<dbColumnTime>)' function
|
|
|
|
|
--You can use 'set' instead of 'select'
declare @t as DateTime
set @t = getUtcDate()
-- Testing--
print @t
print DatePart(hh,@t)
print DatePart(mm,@t)
print DatePart(ss,@t)
print DatePart(ms,@t)
|
|
|
|
|
If I remember right, I think Oracle programmers had to do something like SELECT "Hello World" FROM DUAL because it wouldn't allow a SELECT without a FROM.
Could be a recent convert.
|
|
|
|
|
You do remember right, and I was trying to forget.
|
|
|
|
|
Was just asked to convert some VB6 code to C#.
The purpose of the code is to find anagrams. There is a database with a list of words. The input is a string of letters and the code is supposed to spit out any matching anagrams found in the word list.
The idea is to have a 'crossword puzzle' helper tool that can be downloaded from an ad-financed puzzle enthusiasts website, but the client felt it was too slow to use and wondered if C# might be faster.
This is what I found: All possible orders of letters in the input string are derived in a loop, then each one is searched against the database to see if it exists. As you might imagine, with anything except a very short input this yields gazzilions of combinations and takes forever (almost literally) to run.
The first thing I did was a run-once bit of code to sort the letters in each word in the database into alphabetical order, and stick that in as a key field.
To find an anagram, the letters in the input are also sorted into alphabetical order. We then match that to the indexed key field and pull out matching words in the blink of an eye.
The end result runs in milliseconds instead of centuries. Or as the client put it "That C#'s a bit faster than VB6, isn't it?"
Should I now point out that a downloadable C# app is not a good idea? Their user base (presumably little old ladies) would need the net framework on their Win98 PCs, and having downloaded once, they don't need to go back to click on the knitting pattern banner ads. It should run in a browser.
But what do I know? I'm just a stupid girl.
JustAStupidGurl
|
|
|
|
|
Very amuzing 
The old algorithm does not hold into account possible typo's. Maybe it can be adapted to also check for every possible combination of 3 wrong characters in each word
GSoC 2009 student for SMW!
---
My little forums: http://code.bn2vs.com
---
70 72 6F 67 72 61 6D 6D 69 6E 67 20 34 20 6C 69 66 65!
|
|
|
|
|
Or make the input several MB.
It could then produce every possible C# source code file, not to mention every possible poem of that size.
Bound to be a few good ones 
JustAStupidGurl
|
|
|
|
|
lol!
Mind if I put that in my collection of epic quotes? 
GSoC 2009 student for SMW!
---
My little forums: http://code.bn2vs.com
---
70 72 6F 67 72 61 6D 6D 69 6E 67 20 34 20 6C 69 66 65!
|
|
|
|
|
|
? You seem reasonably intelligent to me... or was it tongue-in-cheek?
|
|
|
|
|
Please adjust your sarcasm detector
|
|
|
|
|
What do you think?
But then I'm JustAStupidGirl, so you never know.
JustAStupidGurl
|
|
|
|
|
The impressiveness of (for example) a 87,178,291,200-element search space for 14-letter anagrams should have gotten the original author to use less permissiveness in choosing an algorithm. If he could miss such an obvious rectification for the problem, he doesn't deserve to earn any kind of certification in software development.
|
|
|
|
|
JustAStupidGurl
|
|
|
|
|
*Stab*
|
|
|
|
|
Do point out that a downloadable C# app isn't the best idea. Why not just run it in asp.net? Even if the existing website is something else, like php, why not set up an asp.net server to run the anagram logic if nothing else? The site can then retrieve the anagrams (or even pre-rendered html) through ajax or some ajax-like mechanism and nobody has to download anything. Even people who have the framework installed may be somewhat sceptical to downloading and running executables directly from some small-name website. Though I guess if we look at how many people happily use things like TPB as their source of software my concern might seem exaggerated.
I've always been fascinated by the fact that somewhere in the digits of pi is a blue-ray encoded video with DTS surround sound of myself falling into a supermassive black hole, seeing past and future as I pass the event horizon, and then arriving at a concert where Mozart plays drums, Britney Spears plays a cello, Bono is a backing vocalist and the pope is making out with George Michael.
|
|
|
|
|
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 --
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
