-
Nov 22nd, 2006, 10:25 AM
#1
Thread Starter
Addicted Member
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
Last edited by señorbadger; Nov 23rd, 2006 at 04:19 AM.
Reason: Resolved
-
Nov 22nd, 2006, 10:31 AM
#2
Re: SQL Group By DateTime (MS SQL Server 2000)
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.
-
Nov 22nd, 2006, 10:44 AM
#3
Thread Starter
Addicted Member
Re: SQL Group By DateTime (MS SQL Server 2000)
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
-
Nov 22nd, 2006, 11:33 AM
#4
Re: SQL Group By DateTime (MS SQL Server 2000)
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?
-
Nov 22nd, 2006, 11:58 AM
#5
Thread Starter
Addicted Member
Re: SQL Group By DateTime (MS SQL Server 2000)
no, contact_id is the primary key
document_series_id can be a duplicate within document whether this could affect it?
-
Nov 23rd, 2006, 04:18 AM
#6
Thread Starter
Addicted Member
Re: SQL Group By DateTime (MS SQL Server 2000)
Sorry My fault, i forgot to put distinct count on the 2nd query. works like a dream thank u!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|