Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Addicted Member señorbadger's Avatar
    Join Date
    Oct 2003
    Location
    Mud pools of wellingborough
    Posts
    193

    Resolved 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

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Addicted Member señorbadger's Avatar
    Join Date
    Oct 2003
    Location
    Mud pools of wellingborough
    Posts
    193

    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

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Addicted Member señorbadger's Avatar
    Join Date
    Oct 2003
    Location
    Mud pools of wellingborough
    Posts
    193

    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?

  6. #6

    Thread Starter
    Addicted Member señorbadger's Avatar
    Join Date
    Oct 2003
    Location
    Mud pools of wellingborough
    Posts
    193

    Resolved 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
  •  



Click Here to Expand Forum to Full Width