Join with conditions and aggregate functions?

When designing databases for temporal events that have duration, it's better practice to put the "IN" time and the "OUT" time on the same row All the queries you need to do are so much easier that way See Joe Celko's SQL Programming Style where he talks about temporal cohesion on pages 48 and 154.

When designing databases for temporal events that have duration, it's better practice to put the "IN" time and the "OUT" time on the same row. All the queries you need to do are so much easier that way. See "Joe Celko's SQL Programming Style" where he talks about temporal cohesion on pages 48 and 154.

The problem is that I didn't design this table and I cannot change its structure. However, in this case is not good solution, because every row in the table hold the data from one finger-reader (sorry, for my english, but I think you know what I mean). Some doors have two (inner and outer) readers, some only one (outer).

But anyway thanks for link to the book. – Lukasz Lysik Sep 21 '09 at 22:43.

To improve performance on the structure level: I suggest that you rename your accessDate column to accessDateTime then you create a PERSISTENT computed column based on your accessDateTime (shown below). Then the index you need would include only the accessDate column which you will use for exact comparison together with the user make sure you have proper indices on the table (from the code below you would probably need one on "user", "accessDate" and including "accessType" accessDate column definition: accessDate AS CONVERT(SMALLDATETIME, CONVERT(CHAR(8), accessDateTime, 112), 112) PERSISTED Now, given that you have done it and you have SQL-2005+, this terribly long query should do the job: WITH MatchIN (in_id, out_id) AS (SELECT s. Id, CASE WHEN COALESCE(y.Id, s.

Id) = s. Id THEN x.Id ELSE NULL END FROM @doorStatistics s LEFT JOIN @doorStatistics x ON x. Id = (SELECT TOP 1 z.

Id FROM @doorStatistics z WHERE z."user" = s. "user" AND z. AccessType = 'OUT' AND z.

AccessDate = s. AccessDate AND z. AccessDateTime >= s.

AccessDateTime ORDER BY z. AccessDateTime ASC ) LEFT JOIN @doorStatistics y ON y. Id = (SELECT TOP 1 z.Id FROM @doorStatistics z WHERE z.

"user" = s. "user" AND z. AccessType = 'IN' AND z.

AccessDate = s. AccessDate AND z. AccessDateTime >= s.

AccessDateTime AND z. AccessDateTime = x. AccessDateTime ORDER BY z.

AccessDateTime ASC ) WHERE s. AccessType = 'OUT' ) SELECT COALESCE(i."user", o. "user") AS "user", COALESCE(i.

AccessDate, o. AccessDate) AS "date", CONVERT(CHAR(10), i. AccessDateTime, 108) AS "inHour", CONVERT(CHAR(10), o.

AccessDateTime, 108) AS "outHour" FROM (SELECT in_id, out_id FROM MatchIN UNION -- this will eliminate duplicates as the same time SELECT in_id, out_id FROM MatchOUT ) x LEFT JOIN @doorStatistics I ON i.Id = x. In_id LEFT JOIN @doorStatistics o ON o. Id = x.

Out_id ORDER BY "user", "date", "inHour" To test for handling of missing rows, just comment out some of your INSERT statements of test data.

Change: fixed the query, removed time-only computed column, tested - works – van Sep 21 '09 at 23:41.

When designing databases for temporal events that have duration, it's better practice to put the "IN" time and the "OUT" time on the same row.

You need to select the minimal OUT record for each IN record for a given user, after ensuring there is no intervening IN record (which would correspond to someone getting IN twice without ever leaving the building). That requires some modestly tricky SQL (a NOT EXISTS clause, for example). So, you are going to have a self-join on the table, plus a NOT EXISTS sub-query on the same table.

Just make sure you alias all references to the table sensibly.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.