quote:

---

Originally posted by dinakar

SELECT <columns>
FROM Log
where employee_id = @user
and AssignedRemoved = 1
and employee_id <> Client_Id


will give you both ClientA and ClientD. Now I dont understand what is the logic for you to choose ClientD over ClientA when date is 1/13/07.



************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

---

quote:

---

Originally posted by khtan

select *
from   Log
where  AssignedRemoved = 1 
and    Employee_ID =  @UserID
and    LogDate     <= @Date

---

KH

---

select client_ID
from Log
where user_ID = @UserID and logDate <= @EndDate
group by client_ID
having max(case when AssignedRemoved = 1 then logDate else '1/1/1900' end) > 
       max(case when AssignedRemoved = 0 then logDate else '1/1/1900' end)

select *
from   Log l
where  AssignedRemoved = 1 
and    Employee_ID     = @UserID
and    LogDate        <= @Date
and    not exists 
       (
           select *
           from   Log x
           where  x.Employee_ID = l.Employee_ID
           and    x.Client_ID   = l.Client_ID
           and    x.AssignedRemoved = 0
           and    x.LogDate    <= @Date
       )

quote:

---

Originally posted by jsmith8858

mine might look funny but it will work, and it should be quite efficient (no self join needed).

- Jeff
http://weblogs.sqlteam.com/JeffS

---

quote:

---

Originally posted by Vinnie881

Declare @InputDate datetime
Declare @EmployeeID varchar(20)
Select @InputDate = '04/08/2007', @EmployeeID = 'UserA'

SELECT Client_ID
FROM Table1 b
where employee_id = @Employee_ID
and AssignedRemoved = 1
and b.LogDate = ( select max(a.logdate) as logdate
from table1 a
where a.logDate <= @InputDate
and a.Employee_ID = @EmployeeID
and a.Client_ID = b.ClientID
and a.AssignedRemoved = 1
and (
( select max(a.logdate) as logdate
from table1 c
where c.logDate <= @InputDate
and a.Employee_ID = @EmployeeID
and a.Client_ID = b.ClientID
and AssignedRemoved = 0
) is null
or
( select max(a.logdate) as logdate
from table1 c
where c.logDate <= @InputDate
and a.Employee_ID = @EmployeeID
and a.Client_ID = b.ClientID
and AssignedRemoved = 0) < (select max(a.logdate) as logdate
from table1 a
where a.logDate <= @InputDate
and a.Employee_ID = @EmployeeID
and a.Client_ID = b.ClientID
and AssignedRemoved = 1
)

)
)

---

quote:

---

Originally posted by jsmith8858

I *think* this will do the trick, simple and quick but kind of unusual:

select client_ID
from Log
where user_ID = @UserID and logDate <= @EndDate
group by client_ID
having max(case when AssignedRemoved = 1 then logDate else '1/1/1900' end) > 
       max(case when AssignedRemoved = 0 then logDate else '1/1/1900' end)

- Jeff
http://weblogs.sqlteam.com/JeffS

---

Declare @InputDate datetime
Declare @EmployeeID varchar(20)
Select @InputDate = '04/08/2007', @EmployeeID = 'UserA'

SELECT Client_ID
FROM Table1 b
where employee_id = @EmployeeID
and AssignedRemoved = 1
and b.LogDate = (	select max(a.logdate) as logdate
					from table1  a 
					where	a.logDate		<=	@InputDate
					and		a.Employee_ID	=	@EmployeeID 
					and		a.Client_ID		=	b.ClientID
					and		a.AssignedRemoved = 1
				    and	(
							(
								(	select max(a.logdate) as logdate 
								from	table1 c  
								where	c.logDate		<=	@InputDate 
								and		a.Employee_ID	=	@EmployeeID 
								and		a.Client_ID		=	b.ClientID 
								and		AssignedRemoved = 0
							)	is null)
							or 
							(		
								select max(a.logdate) as logdate 
								from table1 c  
								where  c.logDate <= @InputDate 
								and a.Employee_ID =	@EmployeeID 
								and a.Client_ID = b.ClientID 
								and AssignedRemoved = 0) < (select max(a.logdate) as logdate 
															from table1  a 
															where  a.logDate <= @InputDate 
															and		a.Employee_ID =	@EmployeeID 
															and		a.Client_ID = b.ClientID 
															and		AssignedRemoved = 1
															)
															
						)
					)						)

-- Prepare sample data
DECLARE @Sample TABLE ([User] VARCHAR(10), Client VARCHAR(10), Assigned BIT, dt DATETIME)

INSERT	@Sample
SELECT	'UserA', 'ClientA', 1, '1/6/07' UNION ALL
SELECT	'UserB', 'ClientA', 1, '1/8/07' UNION ALL
SELECT	'UserC', 'ClientB', 1, '2/5/07' UNION ALL
SELECT	'UserA', 'ClientA', 0, '1/12/07' UNION ALL
SELECT	'UserA', 'ClientD', 1, '1/7/07' UNION ALL
SELECT	'UserC', 'ClientA', 1, '1/14/07'

-- Initialize a common table expression
;WITH	cteLog ([User], Client)
AS 
(
	SELECT	y.[User],
		y.Client
	FROM	(
			SELECT	[User],
				Client,
				Assigned,
				dt,
				ROW_NUMBER() OVER (PARTITION BY [User], Client ORDER BY dt DESC, Assigned) AS RecID
			FROM	@Sample 
			WHERE	dt <= '1/8/07'
		) AS y
	WHERE	y.Assigned = 1
		AND y.RecID = 1
) -- change assigned [asc] to assigned desc if order of assigned is important

-- Show the expected output
SELECT DISTINCT	c1.[User],
		STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + c2.Client FROM cteLog AS c2 WHERE c2.[User] = c1.[User] ORDER BY ', ' + c2.Client FOR XML PATH('')), 1, 2, '') AS Clients
FROM		cteLog AS c1
ORDER BY	c1.[User]