|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
need urgent helpI 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 An idea is to create a temp table, and filling it op with one row at a time. > such kind of report. 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 Show quote "Bhishm" <bhis***@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  |
|||||||||||||||||||||||
Other interesting topics