Grouping by defined number of days.

This is a question about custom grouping by a defined number of days. I
would like to have a start date, then group the data in 3 day blocks. Is
this possible ' Ideally, I would like to have a start date, end date, and
all the little intervals in-between, even if no data is in those intervals.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
I was screwing around with the datepart() function, but that did not work.
This does a grouping by hour.
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
In this example, say the start date was 2/10/2004 and the interval was 3
days, Ithink the output would be something like
interval # ev count
1 2 2
2 1 1
3 null 0
For the third result record listed above, if this cannot be done easily,
that is . I can work around it in code. I am really concerned with
getting the interval number and the count of EV's
Thanks for your time.SELECT DATEDIFF(dd, '20040210', dt)/3 + 1 AS Interval, ev, COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(dd, '20040210', dt)/3 + 1, ev
ORDER BY Interval
You can get the missing intervals with a numbers table:
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
SELECT n.Number, yt.ev, COUNT(*)
FROM Numbers n
LEFT OUTER JOIN YourTable yt
ON n.Number = DATEDIFF(dd, '20040210', yt.dt)/3 +1
GROUP BY n.Number, yt.ev
HAVING n.Number <= (SELECT MAX( DATEDIFF(dd, '20040210', dt)/3 + 1) FROM
YourTable)
ORDER BY n.Number
Jacco Schalkwijk
SQL Server MVP
"Jack" <jack@.jack.net> wrote in message
news:g21qe.7805$R21.1536@.lakeread06...
> This is a question about custom grouping by a defined number of days. I
> would like to have a start date, then group the data in 3 day blocks. Is
> this possible ' Ideally, I would like to have a start date, end date,
> and all the little intervals in-between, even if no data is in those
> intervals.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
>
> I was screwing around with the datepart() function, but that did not work.
> This does a grouping by hour.
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> In this example, say the start date was 2/10/2004 and the interval was 3
> days, Ithink the output would be something like
> interval # ev count
> 1 2 2
> 2 1 1
> 3 null 0
> For the third result record listed above, if this cannot be done easily,
> that is . I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
>|||On Thu, 9 Jun 2005 15:05:12 -0400, Jack wrote:

> For the third result record listed above, if this cannot be done easily,
> that is . I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
I'm not sure I understand where you get your interval numbers... Here's
what I got from your sample data:
declare @.startdate datetime
set @.startdate='2004-02-10'
Select Floor(Datediff(d,@.startdate,dt)/3) "Interval #", ev,count(*) "Count"
From YourTable
GROUP BY ev, Floor(Datediff(d,@.startdate,dt)/3)
ORDER BY ev, Floor(Datediff(d,@.startdate,dt)/3)
Interval # ev Count
-- -- --
1 1 1
4 1 1
5 1 1
0 2 2