thank Lowell , u r right.

but i m afraid of, that the might be picked up by a developer or software house, so they copy my data model and logic...

but what about if we deploy a solution to a third party, and that company do so. ( copy the db and sold on next)

Thanks All, esp. Ryan

but i have sold the problem with out adding an EndDate columns.

i.e.

--create table

CREATE TABLE #t (employeeId INT, effectiveDate DATETIME)

--insert...

Hello All

thanks u all for active participation

here the real scenrio goes...........

I am working on a payroll management system.

A table name 'PrEmpSalaryHistory' contains an employees salary history.

'Effected Date' will be used...

Please Any Body There.......

Select Name,TranCount,ItemCount,Sum(Pass) as Pass,Sum(Fail) as Fail

from (

         Select  B.Name,

         count(distinct T.Id) as 'TranCount',

         count(I.Id) as 'ItemCount',                    case when I.P=1 then count(I.P) else 0 end  'Pass',

case when I.P=0 then count(I.P) else...

Question is hard hitting (for me )

yes i m limiting dates but that date may not fall in that limit but record should...