Re: [Q] date-time substraction for hours

You are presumably going to need to specify the start and end times of the date range you are interested in. You are going to need to sort the data by lname, fname, vendor, date_time_in. You are going to need to use a report, grouped by the first three fields. Within the group, if the first row is an OUT record, then the person in question was already on site at the start of the period; presumably, you count the elapsed time between the start of the period and the out time. Otherwise, you need to process alternating IN and OUT times; successive IN or OUT records indicates a hole in your security system and needs to be flagged as an error. If the last record is an IN, then the person was on site at the end of the period, and you presumably count the elapsed time between the IN time and the end of the period. Handling all that in pure SQL is essentially out of the question. I'd not go so far as to say it couldn't all be handled in SQL, but it would not be easy. If you really did need all SQL, then you'd create a temp table of some sort, and you'd insert IN records for those people whose earliest entry is an OUT record (using the period start as the IN time), and you'd insert OUT records for those people whose latest entry is an IN record (using the period end as the OUT time). As a minor technical issue, this would require a second temp table, as you'd want to do: INSERT INTO TempTable SELECT stuff WHERE (compound condition referencing TempTable); so you'd actually have to do: INSERT INTO SpareTempTable SELECT stuff WHERE (compound condition referencing TempTable); INSERT INTO TempTable SELECT * FROM SpareTempTable; The compound condition would probably involve a correlated sub-query, so you'd have to do something like the two queries which follow. The sub-query identifies the earliest/latest row in the period for a given person, and the outer query generates a suitable dummy data row if that row is an OUT/IN entry when it shouldn't be... INSERT INTO SpareTempTable SELECT T1.lname, T1.fname, T1.vendor, <period-start>, "IN" FROM TempTable T1 WHERE T1.date_time_in = (SELECT MIN(date_time_in) FROM TempTable T2 WHERE T2.lname = T1.lname AND T2.fname = T1.fname AND T2.vendor = T1.vendor) AND T1.Status = "OUT" INSERT INTO SpareTempTable SELECT T1.lname, T1.fname, T1.vendor, <period-end>, "OUT" FROM TempTable T1 WHERE T1.date_time_in = (SELECT MAX(date_time_in) FROM TempTable T2 WHERE T2.lname = T1.lname AND T2.fname = T1.fname AND T2.vendor = T1.vendor) AND T1.Status = "IN" You'd probably skip validating for sequential IN or OUT records in the SQL. An ordered select on the temp table will give you a sequence of alternating IN and OUT records for each person, which can be processed automatically by a report. Since pairing up the records in SQL is non-trivial, it is easiest to do that processing in the report body (and you'd detect double IN or double OUT records there too). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: Saqib Mausoof <ssmaus@ccmail.monsanto.com> >Date: Thu, 04 Apr 1996 16:23:57 -0600 >X-Informix-List-Id: <news.22818> > >I need to know if there is a way to do the following in SQL. > >I have a historical database for visitors. Every time an entry is made to the system, >the following fields are inserted into my Hist_visitors table, >lname, fname, vendor, date_time_in, status >Lennon, John, Beatles, 1996-03-01 10:41 PM, IN >and on exiting the site >Lennon, John, Beatles, 1996-03-02 2:30 AM, OUT > >My question is for finding out the number of man hours spend by visitors inside our >site - for every day, allowing for repeat visitors. I have a couple of ideas but >nothing concrete. If someone has done anything like this before I would love to hear >their suggestions.