Help with a Query - s

Hi,

SELECT     convert(varchar(10),dateAdded,112), COUNT(1) AS counter
FROM         BOBS_DATA
WHERE dateAdded is not null
GROUP BY convert(varchar(10),dateAdded,112)
ORDER BY dateAdded

Try this, Hope this helps...

SP

Help with a Query - Aaron Bertrand [SQL Server MVP]

DateAdded has time information too, why do you think SQL Server will
automatically roll it up to a date for you?  You need to do that yourself...
There are various methods for this, but the most efficient is to perform
date arithmetic (see below).

Also, you don't really want to have a filter in the HAVING clause like that,
unless the filter actually applies to aggregations (e.g. HAVING
COUNT(dateAdded) > 1).  Filters that remove data you don't even want
calculations on in the first place should be in the WHERE clause.

Finally, the order by is not working as expected because, it looks like, you
have stored what looks like date time data (but cannot be validated!) in a
varchar column of some kind.  Why aren't you using a DATETIME or
SMALLDATETIME column for date/time data?


SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateAdded)),
COUNT(dateAdded)
FROM
BobsData
WHERE
COALESCE(dateAdded, 'NULL is not a date') = 1
GROUP BY
DATEADD(DAY, 0, DATEDIFF(DAY, 0, dateAdded))
ORDER BY
1;




On 5/7/08 10:31 PM, in article
8fc27192-eb8e-4bd3-9ca5-c37cf5ae10a0@27g2000hsf.googlegroups.com, "robboll"

Help with a Query - s

Hi,

small change with order by clause.

SELECT     convert(varchar(10),dateAdded,112), COUNT(1) AS counter
FROM         BOBS_DATA
WHERE dateAdded is not null
GROUP BY convert(varchar(10),dateAdded,112)
ORDER BY 1

Help with a Query - Plamen Ratchev

The DATETIME and SMALLDATETIME data types have time portion, and to get
grouping by date only you have to reset the time portion. Here is one way to
do that:

SELECT DATEADD(day, DATEDIFF(day, 0, dateAdded), 0) AS date_added,
COUNT(dateAdded) AS counter
FROM BOBS_DATA
WHERE dateAdded IS NOT NULL
GROUP BY DATEADD(day, DATEDIFF(day, 0, dateAdded), 0)
ORDER BY date_added;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Help with a Query - Aaron Bertrand [SQL Server MVP]

Arrgh!  That should have been

ISDATE(COALESCE(dateAdded, 'NULL is not a date')) = 1

Help with a Query - robboll

When I run the following query:

SELECT     dateAdded, COUNT(dateAdded) AS counter
FROM         BOBS_DATA
GROUP BY dateAdded
HAVING      (NOT (dateAdded IS NULL))
ORDER BY dateAdded

The results I am getting are like:

dateAdded	counter
Apr  1 2008  6:36AM	564
Apr  1 2008  7:56AM	129
Apr  1 2008  8:21AM	983
Apr  1 2008  9:46AM	799
Apr  2 2007  2:04PM	927
Apr  2 2007 10:42AM	1234
Apr  2 2007 12:29PM	992
Apr  2 2008  6:37AM	608
Apr  2 2008  7:57AM	594
Apr  2 2008  9:31AM	804
Apr  2 2008  9:59AM	756
Apr  3 2006  4:22PM	2088
Apr  3 2006  5:08PM	1013
Apr  3 2006  7:25AM	3
Apr  3 2007  7:36AM	764
Apr  3 2007  9:34AM	5
Apr  3 2007 10:28AM	1
Apr  3 2007 10:49AM	146
Apr  3 2008  6:41AM	755
Apr  3 2008  8:24AM	866
Apr  4 2006  8:21AM	68
Apr  4 2006 11:23AM	1017
Apr  4 2007  7:29AM	1367
Apr  4 2008  6:34AM	608
Apr  4 2008  8:34AM	1080

Both the group by and the order isn't working.  There should be only
one instance of (for example) April 3 2007 but there are 4.  And the
Count for April 3 2007 should be 916

My guess is that it is reading it as text rather than a data-time
stamp.

Any help appreciated!

RBollinger

Help with a Query - robboll

It chops the year off, but a good start.  Thank you.

Help with a Query - robboll

to

Thank you very much.  This is exactly what I needed.