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> This is what I want to creat , but I don't know how can I generate
> such kind of report.
An idea is to create a temp table, and filling it op with one row at a time.
The rows can be foud by declaring a datetime variable, and assigning it the
lowest datetime you need to have in your report.
Then you kan do something like this:
create table #temptable (...)
declare startdate datetime
set @.startdate '2007-11-20 00:00:00.000'
while @.startdate < getdate()
begin
insert into #temptable
select @.startdate as time, count(*)
from loggingtable
where login_time between @.startdate and dateadd(hh, 2, @.startdate)
group by login_time
set @.startdate = dateadd(hh,2,@.startdate)
end
The above is not tested, but my idea should shine through, so you can
continue your own work.
/Sjang|||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