Then it appears we are still at the 'understanding your problem' part of the process. Defining a problem incompletely or ambiguously often puts people off helping, and is probably why...

Hi Howard,

Maybe something like this?

--data

declare @t table (id int identity(1, 1), Control_Code char(1))

insert @t (Control_Code)

          select '1'

union all select...

I don't think you'll find a faster method to solve the problem you've defined. Try analysing the execution plan, and seeing if you can add indexes to speed up the...

You don't need any looping. This query does all you need (well, all you've asked for) on its own.

Have you not run it to see? That always helps!

See changes in red below. Note that (both above and here) 't' is a query which gets all the rows we want - and that we use outer joins just in...

<Sigh>

I always try to use temporary objects so anyone (including me) can run the code without it messing up their database.

And all I did was correct syntax errors (e.g. "Cust_CompanyAdress,

HAVING"). I'm sure...

Interesting. I ask for data. You give me code.

Well, never mind. I've corrected the syntax errors in your query, and added my sample data, and it all works as expected...

BTW, the above assumes there are no more than 3 ins or 3 outs for any employee on any day. I assumed this was okay since your Report table has...

This is what I got from your question...

--data

if Object_ID(N'TempDB.[dbo].[#AccessLog]') is not null drop table #AccessLog

CREATE TABLE [dbo].[#AccessLog] (

 [RCDID] [int]...

Well, that's as clear as mud to me!

My solution works against the example data I provided (at least I think it does - do...

Here's one method (which doesn't use a UDF) - just run it to see...

--data

declare @t1 table (Col1 varchar(20), col2 varchar(20), col3...

John - No, I was concerned about the whole design . (I'm with you on the solution suggestion if that's going to be possible...

Hi Dan

A word of warning. This is not a design you should take lightly. It could become a nightmare to work with.

You should only...

Hi jonas,

Here's a simple implementation of sushila's suggestion...

--data

declare @t table (a varchar(1), b varchar(1), c varchar(1), i int)

insert @t (a, b, c)

          select 'x', 'x', 'x'

union...

Here's one way...

--data

declare @StartDate datetime

declare @EndDate datetime

set dateformat mdy

set @StartDate='1/1/06'

set @EndDate='1/4/06'