Hi

I need to count the number of records on a Day in SQL Server

example data

Date_Created
2007-08-07
2007-08-07
2007-08-07
2007-08-08
2007-08-08
2007-08-09
2007-08-09

expected results
2007-08-07 = 3
2007-08-08 = 2
2007-08-09 = 2

What will the SQL Statement be?

I tried this

SELECT COUNT(Date_Created), Date_Created FROM ITEMS GROUP BY Date_Created 
ORDER BY Date_Created

without any success

TIA
Barry

Is Date_Created a varchar() or char() field?

SELECT Date_Created, Count(*) FROM Items
Group BY Date_Created
Order BY Date_Created

If a datetime field, you will have to set up the query to get the number of 
records in a count statement. This is due to the fact additional information 
is added to the date field for the time.

-- 
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************
"Barry"  wrote in message 
news:%23d%23jJM03HHA.4184@TK2MSFTNGP06.phx.gbl...

>
> Hi
>
> I need to count the number of records on a Day in SQL Server
>
> example data
>
> Date_Created
> 2007-08-07
> 2007-08-07
> 2007-08-07
> 2007-08-08
> 2007-08-08
> 2007-08-09
> 2007-08-09
>
> expected results
> 2007-08-07 = 3
> 2007-08-08 = 2
> 2007-08-09 = 2
>
> What will the SQL Statement be?
>
> I tried this
>
> SELECT COUNT(Date_Created), Date_Created FROM ITEMS GROUP BY Date_Created 
> ORDER BY Date_Created
>
> without any success
>
> TIA
> Barry
>
> 

Date_Created is a DateTime field, can you help, i am still not able to get 
it right


"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in 
message news:urUXmH13HHA.5184@TK2MSFTNGP02.phx.gbl...

> Is Date_Created a varchar() or char() field?
>
> SELECT Date_Created, Count(*) FROM Items
> Group BY Date_Created
> Order BY Date_Created
>
> If a datetime field, you will have to set up the query to get the number 
> of records in a count statement. This is due to the fact additional 
> information is added to the date field for the time.
>
> -- 
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> http://gregorybeamer.spaces.live.com
> Co-author: Microsoft Expression Web Bible (upcoming)
>
> ************************************************
> Think outside the box!
> ************************************************
> "Barry"  wrote in message 
> news:%23d%23jJM03HHA.4184@TK2MSFTNGP06.phx.gbl...
>>
>> Hi
>>
>> I need to count the number of records on a Day in SQL Server
>>
>> example data
>>
>> Date_Created
>> 2007-08-07
>> 2007-08-07
>> 2007-08-07
>> 2007-08-08
>> 2007-08-08
>> 2007-08-09
>> 2007-08-09
>>
>> expected results
>> 2007-08-07 = 3
>> 2007-08-08 = 2
>> 2007-08-09 = 2
>>
>> What will the SQL Statement be?
>>
>> I tried this
>>
>> SELECT COUNT(Date_Created), Date_Created FROM ITEMS GROUP BY Date_Created 
>> ORDER BY Date_Created
>>
>> without any success
>>
>> TIA
>> Barry
>>
>>
>
> 

Hi Barry,

The problem is probably that your dates have hour, minute, second, and 
possibly millisecond data in them, and that if you group them, dates 
differing by these small amounts will not be grouped together. 
Unfortunately, Transact-SQL does not include a function that easily rounds 
dates to the day. However, you can either use your own inside your query, or 
create a user-defined function that you can use in many queries. Here's a 
user-defined function, and an example of how to use it:

/*  Removes Hours, Minutes, Seconds, Milliseconds from DateTime */
CREATE FUNCTION DATEROUND(@FullDate DateTime)
Returns DateTime
AS
BEGIN
 RETURN DATEADD(Day, DATEDIFF(Day, 0, @FullDate), 0)
END

SELECT dbo.DATEROUND(Date_Created) As Date_Created, Count(*)
FROM Items
GROUP BY dbo.DATEROUND(Date_Created)
ORDER BY dbo.DATEROUND(Date_Created)

-- 
HTH,

Kevin Spencer
Microsoft MVP

DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

"Barry"  wrote in message 
news:%23nq3An$3HHA.4400@TK2MSFTNGP06.phx.gbl...

> Date_Created is a DateTime field, can you help, i am still not able to get 
> it right
>
>
> "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in 
> message news:urUXmH13HHA.5184@TK2MSFTNGP02.phx.gbl...
>> Is Date_Created a varchar() or char() field?
>>
>> SELECT Date_Created, Count(*) FROM Items
>> Group BY Date_Created
>> Order BY Date_Created
>>
>> If a datetime field, you will have to set up the query to get the number 
>> of records in a count statement. This is due to the fact additional 
>> information is added to the date field for the time.
>>
>> -- 
>> Gregory A. Beamer
>> MVP; MCP: +I, SE, SD, DBA
>> http://gregorybeamer.spaces.live.com
>> Co-author: Microsoft Expression Web Bible (upcoming)
>>
>> ************************************************
>> Think outside the box!
>> ************************************************
>> "Barry"  wrote in message 
>> news:%23d%23jJM03HHA.4184@TK2MSFTNGP06.phx.gbl...
>>>
>>> Hi
>>>
>>> I need to count the number of records on a Day in SQL Server
>>>
>>> example data
>>>
>>> Date_Created
>>> 2007-08-07
>>> 2007-08-07
>>> 2007-08-07
>>> 2007-08-08
>>> 2007-08-08
>>> 2007-08-09
>>> 2007-08-09
>>>
>>> expected results
>>> 2007-08-07 = 3
>>> 2007-08-08 = 2
>>> 2007-08-09 = 2
>>>
>>> What will the SQL Statement be?
>>>
>>> I tried this
>>>
>>> SELECT COUNT(Date_Created), Date_Created FROM ITEMS GROUP BY 
>>> Date_Created ORDER BY Date_Created
>>>
>>> without any success
>>>
>>> TIA
>>> Barry
>>>
>>>
>>
>>
>
> 

you have two options:

1. Set the date so all dates are midnight. This does not work on GETDATE() 
or GETUTCDATE(), so you will have to use a trigger if you want the date to 
be:

2007-08-16 00:00:00.000

instead of

2007-08-16 10:49:42.670

It is easier to just insert at the date, but you can do this (in efficient, 
but this is off the cuff):

CREATE TRIGGER trgItems
ON Items
FOR INSERT
AS

declare @date datetime, @workingdate datetime
set @workingdate = getdate()

-- Get Trimmed date
set @date = CAST((cast(year(@workingdate) as char(4)) + '-'
+ cast(month(@workingdate) as char(2))
+ '-' + cast(day(@workingdate) as char(2))
+ ' 00:00:00.000') as datetime)

UPDATE Items
SET Date_Created = @date
WHERE ItemID IN (SELECT ItemID FROM INSERTED)

Or, you can insert the trimmed date. There are other ways to trim, but I 
have been using CAST and CONVERT lately, so i am a bit cast heavy. :-)

The question here is whether to store the date like this, of course.

2. Use a range. But, this will not work in a query like you want.

Here is a solution (not optimized, however)

INSERT INTO #Temp
SELECT CAST(getdate() as varchar(11))  as Date_Created FROM Items

OR
SELECT CAST((cast(year(@workingdate) as char(4)) + '-'
+ cast(month(@workingdate) as char(2))
+ '-' + cast(day(@workingdate) as char(2))
+ ' 00:00:00.000') as datetime)  as Date_Created
FROM Items

SELECT Date_Created, COUNT(*) FROM #Temp

DROP TABLE #Temp

This has to be either semi-colon separeted (dangerous for newbies) or placed 
in a stored procedure (learning curve).

I would do a search for truncating a date, as I am certain there are more 
efficient ways. Note that you can also create this function in .NET if you 
are using SQL Server 2005, so you can make this highly efficient.

-- 
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!                           |
*************************************************
"Barry"  wrote in message 
news:%23nq3An$3HHA.4400@TK2MSFTNGP06.phx.gbl...

> Date_Created is a DateTime field, can you help, i am still not able to get 
> it right
>
>
> "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in 
> message news:urUXmH13HHA.5184@TK2MSFTNGP02.phx.gbl...
>> Is Date_Created a varchar() or char() field?
>>
>> SELECT Date_Created, Count(*) FROM Items
>> Group BY Date_Created
>> Order BY Date_Created
>>
>> If a datetime field, you will have to set up the query to get the number 
>> of records in a count statement. This is due to the fact additional 
>> information is added to the date field for the time.
>>
>> -- 
>> Gregory A. Beamer
>> MVP; MCP: +I, SE, SD, DBA
>> http://gregorybeamer.spaces.live.com
>> Co-author: Microsoft Expression Web Bible (upcoming)
>>
>> ************************************************
>> Think outside the box!
>> ************************************************
>> "Barry"  wrote in message 
>> news:%23d%23jJM03HHA.4184@TK2MSFTNGP06.phx.gbl...
>>>
>>> Hi
>>>
>>> I need to count the number of records on a Day in SQL Server
>>>
>>> example data
>>>
>>> Date_Created
>>> 2007-08-07
>>> 2007-08-07
>>> 2007-08-07
>>> 2007-08-08
>>> 2007-08-08
>>> 2007-08-09
>>> 2007-08-09
>>>
>>> expected results
>>> 2007-08-07 = 3
>>> 2007-08-08 = 2
>>> 2007-08-09 = 2
>>>
>>> What will the SQL Statement be?
>>>
>>> I tried this
>>>
>>> SELECT COUNT(Date_Created), Date_Created FROM ITEMS GROUP BY 
>>> Date_Created ORDER BY Date_Created
>>>
>>> without any success
>>>
>>> TIA
>>> Barry
>>>
>>>
>>
>>
>
> 

"Barry"  wrote in message 
news:#d#jJM03HHA.4184@TK2MSFTNGP06.phx.gbl...

>
> Hi
>
> I need to count the number of records on a Day in SQL Server
>
> example data
>
> Date_Created
> 2007-08-07
> 2007-08-07
> 2007-08-07
> 2007-08-08
> 2007-08-08
> 2007-08-09
> 2007-08-09
>
> expected results
> 2007-08-07 = 3
> 2007-08-08 = 2
> 2007-08-09 = 2
>
> What will the SQL Statement be?
>
> I tried this
>
> SELECT COUNT(Date_Created), Date_Created FROM ITEMS GROUP BY Date_Created 
> ORDER BY Date_Created
>
> without any success
>
> TIA
> Barry
>
>


Try:

The results are a little different (more columns) but it works for me:

select
    month(Date_Created) as Month,
    day(Date_Created) as Day,
    year(Date_Created) as Year,
    count(Date_Created) as Count
from
    Items
group by
    year(Date_Created),
    month(Date_Created),
    day(Date_Created)

HTH,
Mythran

> Try:
>
> The results are a little different (more columns) but it works for me:
>
> select
>    month(Date_Created) as Month,
>    day(Date_Created) as Day,
>    year(Date_Created) as Year,
>    count(Date_Created) as Count
> from
>    Items
> group by
>    year(Date_Created),
>    month(Date_Created),
>    day(Date_Created)
>
> HTH,
> Mythran
>
>


Was just thinking...if you used this as an inner query, you could get the 
exact results you want as well:

select
    Month + '/' + Day + '/' + Year as TheDate,
    Count
from (
    select
        month(Date_Created) as Month,
        day(Date_Created) as Day,
        year(Date_Created) as Year,
        count(Date_Created) as Count
    from
        Items
    group by
        year(Date_Created),
        month(Date_Created),
        day(Date_Created)
) tbl;

Now, it's untested, so it may not work ... but worth a shot :)

HTH,
Mythran