dcsimg
Results 1 to 3 of 3

Thread: Grouping by days??

  1. #1

    Thread Starter
    Fanatic Member venerable bede's Avatar
    Join Date
    Sep 2002
    Location
    The mystic land of Geordies
    Posts
    1,018

    Grouping by days??

    Hi

    I have the following query :
    Code:
    select count(*) as Produced from prodTable where DateCreated between @startdate and @enddate) Produced,
    which returns a count of records created between 2 dates. These dates are @startdate = 1 week ago @enddate = today

    What I now need is a result set which gives me a count by day and returns the days name

    e.g
    Code:
    Tuesday, 24
    Wednesday, 12,
    Thursday, 34
    Friday, 2 .....
    Any ideas ?

    Parksie

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,463

    Re: Grouping by days??

    Is DateCreate a Date or a DateTime? I'm going to assume it's a date.

    Your basic query is
    Code:
    Select DateCreated, Count(*)
    from prodTable 
    where DateCreated between @startdate and @enddate
    group by DateCreated
    You can then use the DateName function to return the date in a freindlier format.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,405

    Re: Grouping by days??

    Here is a basic example to show you what you need to do. MS SQL Server 2005

    Code:
    Create table ##VBForums(MyDate datetime)
    
    insert into ##VBForums(MyDate) values(GetDate())
    insert into ##VBForums(MyDate) values(GetDate())
    
    insert into ##VBForums(MyDate) values(GetDate() + 1)
    insert into ##VBForums(MyDate) values(GetDate() + 1)
    
    insert into ##VBForums(MyDate) values(GetDate() + 2)
    insert into ##VBForums(MyDate) values(GetDate() + 2)
    
    insert into ##VBForums(MyDate) values(GetDate() + 3)
    insert into ##VBForums(MyDate) values(GetDate() + 3)
    
    select datename(dw, MyDate), count(MyDate)
    from ##VBForums
    group by datename(dw, MyDate)
    
    drop table ##VBForums
    FunkyDexter beat me to it. Between the two examples you should have what you need.
    Please remember next time...elections matter!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width