need urgent help

[Bhishm]

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

[jhofmeyr]

Hi Bhishm,

I'm afraid you will need to supply a lot more information than this.
What table(s) exist for storing these details? What technology are you
using to design the report? What does the "Time Range" value in the
report represent (looks like hours?) Do you simply need a SQL
Statement to prepare data in the "report" format you specified?

If I simply assume that everything un-said is as I imagine it, I guess
the solution might be something like:
/* Initialise data table */
CREATE TABLE tblLog (LogName nvarchar(50), LogInTime datetime,
LogOutTime datetime)

INSERT INTO tblLog (LogName, LogInTime, LogOutTime)
SELECT 'personA', '2007-11-10T12:00:00', '2007-11-10T16:00:00'
UNION ALL
SELECT 'personB', '2007-11-10T15:00:00', '2007-11-10T18:00:00'
UNION ALL
SELECT 'personC', '2007-11-10T11:00:00', '2007-11-10T14:00:00'

/* Create supporting table */
CREATE TABLE HrInDay (HrMin INT, HrMax INT, TimeRange VARCHAR(10))

DECLARE @i INT, @Date VARCHAR(8)

SET @i = 0
SET @Date = '20071110' -- Date parameter for "Report"

WHILE @i < 24
BEGIN
INSERT INTO HrInDay (HrMin, HrMax, TimeRange)
VALUES (@i, @i + 2, CAST(@i AS VARCHAR) + ' - ' + CAST(@i + 2 AS
VARCHAR))
SET @i = @i + 2
END

/* Select from a derived table so it's sorted - there is probably a
better way to do this but I'm too lazy to find it :) */
SELECT LogDate, TimeRange, NoPplLogged
FROM (
SELECT @Date AS LogDate,
MAX(TimeRange) AS TimeRange,
HrMin,
COUNT(DISTINCT LogName) AS NoPplLogged
FROM tblLog
RIGHT JOIN HrInDay
ON DATEPART(hh, LogInTime) < HrMax
AND DATEPART(hh, LogOutTime) HrMin
AND CONVERT(VARCHAR (8), LogInTime, 112) = @Date
GROUP BY HrMin
) AS Report

DROP TABLE HrInDay
DROP TABLE tblLog

Good luck!
J

[Bhishm]

Thanks a lot :) JhofM for showing the way.

I got a lot of help from it in solving it.

Now I am looking into possilities and will let you know my results.

Thanks a lot again, it's of great help