SQL Group By DateTime (MS SQL Server 2000) [Resolved]

Hello, i am trying to write an SQL query to group by the date an item was published on a daily basis ie: desired result

Name Count Date

mark 1 30/10/2006
bob 3 1/11/2006
chris 1 1/11/2006

so on and so on, i just need the group by to ignore the time portion of the datetime.

Below is my SQL snippet (SQL Server 2000 by the way)

I read somewhere by converting the date to a varchar it would ignore the date portion but the query below...

Code:

---

Declare @start DateTime
Declare @end  DateTime
Declare @days  int

set @start = convert(datetime, '14/11/2006', 103)
set @days = 6
set @end = DATEADD(d, @days, @start)



select Convert(char(11), document.published_Date) as PublishDate, count(distinct  document.document_series_id) as NumPublished, contact_summary.fullname
  from document inner join contact_summary on
contact_summary.contact_id = document.author_id
where document.published_Date > @start and document.published_date < @end
group by Convert(char(11), document.published_Date), contact_summary.fullname
order by Convert(char(11), document.published_Date) asc, count(distinct  document.document_series_id) desc

---

if i write a query to get the count of the documents published within the date span
the values dont match (sum of all days) != count of documents published on all days

By the way ALL documents have a valid author_id

Thank you!

Sam Lad

Select and Group by...

Convert(varchar(10),document.published_Date,101)

Style 101 is mm/dd/yyyy

Pick any style you want - just make sure to not have a time part on it.

Ace, nearly there but any ideas why the 2 following queries would not return the same values (add up the middle column of the 1st query and compare that)

Code:

---

Declare @start DateTime
Declare @end  DateTime
Declare @days  int

set @start = convert(datetime, '14/11/2006', 103)
set @days = 6
set @end = DATEADD(d, @days, @start)

select Convert(varchar(11), document.published_Date, 103) as PublishDate, count(distinct  document.document_series_id) as NumPublished, contact_summary.fullname
  from document inner join contact_summary on
contact_summary.contact_id = document.author_id
where document.published_Date > @start and document.published_date < @end
group by Convert(varchar(11), document.published_Date, 103), contact_summary.fullname
order by Convert(varchar(11), document.published_Date, 103) asc, count(distinct  document.document_series_id) desc

---

Query 2:

Code:

---

select count(document_series_id) from document where document.published_Date > @start and document.published_date < @end

---

Query 1 Result (Added together) 2046 documents
Query 2 Result 2162 documents

please forgive me if i'm being dumb

Is contact_summmary in a one-to-one relationship - or are they a possiblity of several rows in that table with the same contact_id?

no, contact_id is the primary key
document_series_id can be a duplicate within document whether this could affect it?

Sorry My fault, i forgot to put distinct count on the 2nd query. works like a dream thank u!