help with grouping and dateadd

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with grouping and dateadd

Author

Topic

partitur
Starting Member

12 Posts

Posted - 2009-06-23 : 10:11:46

Hey guys.

I have a table containing fields IDno, DATE and Amount.

Ex

IDno DATE Amount
1 2009-01-03 10
1 2009-01-06 100
2 2004-03-20 10
3 2007-02-03 20
3 2007-02-04 30
3 2007-02-08 100
3 2007-02-09 10
4 2007-12-01 10
4 2009-06-11 100
4 2009-06-12 80

I need to get all the records (by IDno) that have 3 or more observations, but only if the DATE fields are in a 7 day interval, say between 2009-06-01 until 2009-06-08, or whatever dates as longs as the time interval is 7 days (regardless of weekends etc).

From my example table above IDno=3 should be returned with IDno, min(DATE), max(DATE), sum(Amount), count(IDno).

I'm guessing that I need to group and use dateadd but I'm not really sure how to do it. Am somehwat new to sql.

Any ideas ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 10:21:16

SELECT IdNo, SUM(Amount), COUNT(*)
FROM Table1
GROUP BY IdNo
HAVING DATEDIFF(DAY, MIN(Date), MAX(Date)) < 7

E 12°55'05.63"
N 56°04'39.26"

partitur
Starting Member

12 Posts

Posted - 2009-06-24 : 05:26:51

quote:
Originally posted by Peso

SELECT IdNo, SUM(Amount), COUNT(*)
FROM Table1
GROUP BY IdNo
HAVING DATEDIFF(DAY, MIN(Date), MAX(Date)) < 7

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso. However it doesn't really do what I want to, I guess my explanation was confusing.

*The 7 day interval doesn't have to be between field DATE's absolute min and max values, it can be inbetween and that's the tricky part.
*Every IDno can have one or more rows per Date.
* I added a HAVING...COUNT(*)>=3

From my new example table below, the result should look like:

Result:
IDno FromDate ToDate Count Sum(Amount)
3 2007-02-04 2007-02-09 4 320

Example table:
IDno Date Amount
1 2009-06-02 10
3 2001-06-01 100
3 2007-02-04 10
3 2007-02-08 100
3 2007-02-08 200
3 2007-02-11 10
3 2009-06-02 100
4 2005-05-06 10
4 2005-05-07 10
4 2006-01-08 10

Note that IDno=3 has 4 observations (2 even on same day) between the 7 day interval 2007-02-04 and 2007-02-11. Idno=3 also have observations before and after those dates.

Hope this makes sense

partitur
Starting Member

12 Posts

Posted - 2009-06-30 : 03:59:47

Hey guys.

Anyone's holding the solution to this query ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-30 : 05:15:52

[code]
declare @i table(IDno int,Date datetime,Amount int)
insert into @i select
1,'2009-06-02',10 union all select
3,'2001-06-01',100 union all select
3,'2007-02-04',10 union all select
3,'2007-02-08',100 union all select
3,'2007-02-08',200 union all select
3,'2007-02-11',10 union all select
3,'2009-06-02',100 union all select
4,'2005-05-06',10 union all select
4,'2005-05-07',10 union all select
4,'2005-05-11',10 union all select
4,'2005-05-13',10 union all select
4,'2006-01-08',10

select distinct i.idno,t.date,t.mindate,sum(i.amount)over (partition by i.idno)as sumamount from @i i
inner join (select idno,max(date) as date,min(date) as mindate from @i
group by idno,year(date),month(date)
having DATEDIFF(DAY, min(date), max(date)) >=6) t on i.idno = t.idno
AND i.date BETWEEN t.mindate and t.date
where i.idno = 3
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources