Posted by jhofmeyr on 11/20/07 13:16

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

[Back to original message]