need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
-----
| name | login time | logout
time |
-----
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----
My requirement:-
I want to generate an hourly report like this:-
date time range total people logged
in
-----
2007-11-10 0 -2 0
2007-12-10 2-4 0
..
..
-----
2007-11-10 12-14 1
-----
2007-11-10 14-16 2
2007-11-10 16-18 1
-----
..
..
-----
2007-11-10 22-24 0
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm
Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> -----
> | name | login time | logout
> time |
> -----
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> -----
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> -----
> My requirement:-
> I want to generate an hourly report like this:-
> ----
> date time range total people logged
> in
> -----
> 2007-11-10 0 -2 0
> ----
> 2007-12-10 2-4 0
> ----
> .
> .
> -----
> 2007-11-10 12-14 1
> -----
> 2007-11-10 14-16 2
> ----
> 2007-11-10 16-18 1
> -----
> .
> .
> -----
> 2007-11-10 22-24 0
> ----
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm