SQL Query

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

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!
************************************************
 
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
 
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 said:
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
Click to expand...

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
Click to expand...

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
 
Back
Top