Query, Ranges time group by concept

[joffer]

Hi:
I´ll try to explain my problem:

Table: Parts
IdPart, MyDate, IdCategory, Hours

Making:
SELECT Parts.MyDate, Parts.IdCategory, Parts.Hours
FROM Parts
ORDER BY Parts.MyDate;
(Original Table)
MyDate IdCategory Hours
01/01/2001 27 7
01/01/2001 27 5
02/01/2001 27 2
03/01/2001 15 8
04/01/2001 27 5
05/01/2001 27 6

If I want to get the hours sum group by category:

SELECT Parts.IdCategory, Sum(Partes.Hours) as SHours
FROM Parts
GROUP BY Parts.IdCategory;

IdCategory SHours
15 8
27 25

if I want to get the hours sum group by date:

SELECT Parts.MyDate, Sum(Parts.Hours) as SHours
FROM Parts
GROUP BY Parts.MyDate;

MyDate SHours
01/01/2001 12
02/01/2001 2
03/01/2001 8
04/01/2001 5
05/01/2001 6

The problem and the question:
I need to show for every category changed into a interval, a new line
in this way:

From To Category Hours
01/01/01 02/01/01 27 14
03/01/01 03/01/01 15 8
04/01/01 05/01/01 27 11

and in this order.

ex:
01/01/01 02/01/01 27 14:
From:01/01/2001
27 7

01/01/2001 27 + 5
To:
02/01/2001 27 +2

--------

14
03/01/01 03/01/01 15 8
From/To: 03/01/2001
15 +8

--------

8
04/01/01 05/01/01 27 11
From:04/01/2001
27 5
To: 05/01/2001
27 + 6

--------

11
How can I do that?

Thanks all.

 

[George Nicholson]

One way would be to have a separate table to provide grouping based on
dates:

MyDate DateGroup
01/01/2001 Group1
02/01/2001 Group1
03/01/2001 Group2
04/01/2001 Group3
05/01/2001 Group3

Join that table to Parts on MyDate and use DateGroup for GROUP BY and ORDER
BY.


--
HTH,
George


Hi:
I´ll try to explain my problem:

Table: Parts
IdPart, MyDate, IdCategory, Hours

Making:
SELECT Parts.MyDate, Parts.IdCategory, Parts.Hours
FROM Parts
ORDER BY Parts.MyDate;
(Original Table)
MyDate IdCategory Hours
01/01/2001 27 7
01/01/2001 27 5
02/01/2001 27 2
03/01/2001 15 8
04/01/2001 27 5
05/01/2001 27 6

If I want to get the hours sum group by category:

SELECT Parts.IdCategory, Sum(Partes.Hours) as SHours
FROM Parts
GROUP BY Parts.IdCategory;

IdCategory SHours
15 8
27 25

if I want to get the hours sum group by date:

SELECT Parts.MyDate, Sum(Parts.Hours) as SHours
FROM Parts
GROUP BY Parts.MyDate;

MyDate SHours
01/01/2001 12
02/01/2001 2
03/01/2001 8
04/01/2001 5
05/01/2001 6

The problem and the question:
I need to show for every category changed into a interval, a new line
in this way:

From To Category Hours
01/01/01 02/01/01 27 14
03/01/01 03/01/01 15 8
04/01/01 05/01/01 27 11

and in this order.

ex:
01/01/01 02/01/01 27 14:
From:01/01/2001
27 7

01/01/2001 27 + 5
To:
02/01/2001 27 +2

--------

14
03/01/01 03/01/01 15 8
From/To: 03/01/2001
15 +8

--------

8
04/01/01 05/01/01 27 11
From:04/01/2001
27 5
To: 05/01/2001
27 + 6

--------

11
How can I do that?

Thanks all.