Group By problem

[raydenl]

Hi,

Im trying to write an sql statement that takes a start date and an end date and groups by 7 days groups showing the start date of each 7 day group.

example:

select sum(blah), ???
from mytable
where (datex between '2005-07-05' and '2005-07-18')
group by ???

which would give:

2005-07-05 456.87
2005-07-12 567.78

Cheers,
R

 

[DotNetGnat]

Try this:

SELECT datex, SUM(blah) FROM mytable WHERE (datex between '2005-07-05' and '2005-07-18')
GROUP BY datex

-DNG

 

[raydenl]

Ummm. nope thats not what i want, that will not group them in groups of 7 days.

 

[cmmrfrds]

How about something like this from the Orders table in the Northwind database.

select inview.thegroup,
count(*) as cnt,
min(orderdate) as mindate,
max(orderdate) as maxdate,
sum(freight)
from dbo.orders outview
Inner Join
(
select orderid,
(convert(int,orderdate))/7 as thegroup
from dbo.orders
) inview
On inview.orderid = outview.orderid
Group by inview.thegroup
Order by thegroup

 

[raydenl]

Nope. If you read my post you will the the result set I want.

 

[maswien]

try following:

create table #duration(start_date smalldatetime, end_date smalldatetime)

declare @start_date as smalldatetime
declare @end_date as smalldatetime
set @start_date = '2005-07-05'
set @end_date = '2005-07-18'

while @start_date < @end_date
begin
 insert into #duration select @start_date, dateadd(dd, 7, @start_date)
 set @start_date = dateadd(dd, 7, @start_date)
end


select  #duration.start_date, sum(myTable.blah)
from mytable inner join #duration
 on datex >= #duration.start_date and datex <= #duration.end_date 
group by #duration.start_date

 

[amrita418]

Try this:

create table MyTable (Id int identity, Datefield datetime)
insert into MyTable values ('07-10-2005')
insert into MyTable values ('08-12-2005')
insert into MyTable values ('07-21-2005')
DECLARE
@v_start 	DATETIME, 
@v_end 		DATETIME,
@v_date 	DATETIME

SET 	@v_start = '07-05-2005'
SET 	@v_end   = '09-25-2005'

select 		Id, @v_start + datediff(day, @v_start, datefield) - (datediff(day, @v_start, datefield) % 7)
from 		mytable
where 		datefield between @v_start and @v_end

Regards,
AA

 

[maswien]

or no need temp table:

declare @start_date as smalldatetime
declare @end_date as smalldatetime
set @start_date = '...'
set @end_date = '...'

select dateadd(day,
               -(datediff(day, @start_date, datex) % 7, 
               datex),
       sum(myTable.blah)
from mytable
where datefield between @v_start and @v_end
group by dateadd(day,
               -(datediff(day, @v_start, datex) % 7, 
               datex)