Sorting and counting in MSSQL

Hi,

I have a MSSQL 2005 database with ~400.000 lines in a table. One column
has a date format like this: "27.05.2007 13:44:00". I need to count all
lines with the same date for a statistik, so that afterwards I have
something like

27.05.2007 350 rows
26.05.2007 256 rows
25.05.2007 180 rows

I couldn't create a view that works good (sorting was the biggest
problem there), so I thought I write a perl script to does it. I'm not
quite sure, though, what the best way to it would be. Should I read all
lines into a hash, then count and sort? Since the database grows, this
could be to slow someday...

any ideas?

Lars
_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: Sorting and counting in MSSQL

> I have a MSSQL 2005 database with ~400.000 lines in a table.
> One column
> has a date format like this: "27.05.2007 13:44:00". I need to
> count all
> lines with the same date for a statistik, so that afterwards I have
> something like
>
> 27.05.2007 350 rows
> 26.05.2007 256 rows
> 25.05.2007 180 rows
>
> I couldn't create a view that works good (sorting was the biggest
> problem there), so I thought I write a perl script to does it. I'm not
> quite sure, though, what the best way to it would be. Should
> I read all
> lines into a hash, then count and sort? Since the database grows, this
> could be to slow someday...

hm, I found the solution... after fiddling with this for 2 days, I found
that what doesn't work within the MSSQL Enterprise Console, works from a
perl script. This:

SELECT TOP (100) PERCENT COUNT(*)
AS AnzahlDokumente, MAX(FLD34) AS Datum,
CONVERT(varchar(10), FLD34, 112) AS DatumSortierung
FROM dbo.DOC8 GROUP BY CONVERT(varchar(10), FLD34, 112)
ORDER BY DatumSortierung ASC

gave me missing entries, and wrong sorting when I opened the view within
the console. However when I use the same statement from a perl script
over ODBC connection, I get the expected results...

Strange....

Lars
_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs