selecting a effective date from a table

Question

Post reply

selecting a effective date from a table

naveed ahmad

SSC Veteran

Points: 299
More actions
December 25, 2005 at 11:49 pm

#90367

Hi all techies
suppose a table contains these set of values
employeeId          EffectiveDate
1                         2004-12-01
2                         2004-12-01
2                         2005-11-15
3                         2004-12-01
so
1. when i want to select data for the date ranges
(2005-12-01) to (2005-12-31), it should be
1                        2004-12-01
2                        2005-11-15
3                        2004-12-01
2. when i want to select data for the date ranges
(2005-11-01) to (2005-11-30), it should be
1                        2004-12-01
2                        2004-12-01
2                        2005-11-15
3                        2004-12-01
3. when i want to select data for the date ranges
(2005-10-01) to (2005-10-31), it should be
1                        2004-12-01
2                        2004-12-01
3                        2004-12-01

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Is there a question here

Does a WHERE clause not handle this for you with one of the effective dates? Seems like you are just limiting dates.

naveed ahmad SSC Veteran Points: 299 More actions · Answer 2

Question is hard hitting (for me )

yes i m limiting dates but that date may not fall in that limit but record should be return for maximum last date for a employee

just analyse the results, you will find the problem point

thanks for ur reply

--==========

Hi all techies

suppose a table contains these set of values

employeeId EffectiveDate

1 2004-12-01

2 2004-12-01

2 2005-11-15

3 2004-12-01

so

1. when i want to select data for the date ranges

(2005-12-01) to (2005-12-31), return records should be

1 2004-12-01

2 2005-11-15

3 2004-12-01

2. when i want to select data for the date ranges

(2005-11-01) to (2005-11-30), return records should be

1 2004-12-01

2 2004-12-01

2 2005-11-15

3 2004-12-01

3. when i want to select data for the date ranges

(2005-10-01) to (2005-10-31), return records should be

1 2004-12-01

2 2004-12-01

3 2004-12-01

-===========================

naveed ahmad SSC Veteran Points: 299 More actions · Answer 3

naveed ahmad

SSC Veteran

Points: 299

December 27, 2005 at 4:43 am

#611503

Please Any Body There.......

n_z Valued Member Points: 74 More actions · Answer 4

what about

select Employeeid, effectivedate

where effectivedt between ....

union all

select employeeid , max(effectivedate)

from table

group by employeeid

having max(effectivedate) < fromdate

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

The question still remains to be asked.. What problem are you trying to solve? I don't think you'll get much help if we have to guess what you want.

The examples makes absolutely no sense at all by themselves. You give a daterange, why should there then be dates returned that are not within that range? And even for the one with two dates, you sometimes want both dates and for other ranges only the latest date?

I can't for the life of me figure out your business logic here - you must explain a little more.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Actually I guess the 2. example contains a typo and that you're after the last effectivedate for each entry in employeeID. If so, this might help

USE Northwind

SELECT t1.* FROM [order details] t1

WHERE t1.Quantity=

(SELECT MAX(Quantity) FROM [order details] t2

WHERE t1.orderid=t2.orderid)

ORDER BY t1.orderid

SELECT t1.* FROM [order details] t1 INNER JOIN

(SELECT orderid, MAX(Quantity) AS maxdate FROM [order details] GROUP BY orderid) t2

ON t1.orderid = t2.orderid

AND t1.Quantity = t2.maxdate

ORDER BY t1.orderid

If not, you should provide the information stated here http://www.aspfaq.com/etiquette.asp?id=5006

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

I thought about the typo thesis for the ranges as well, but that broke with the wanted results of example 2..

/Kenneth

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Naveed Ahmad,

You aren't going to be able to do it.......

In Case 1, you want values between "(2005-12-01) to (2005-12-31)" But your results have values from 2004 (which is not part of 2005) and a value from month 11 (which is not part of month 12).

In Case 2, you want values between "(2005-11-01) to (2005-11-30)". But your results have values from 2004 and from month 12.

In Case 3, you want values between "(2005-10-01) to (2005-10-31)". But your results are from year 2004 and month 12.

So....as everyone else has said......What IS your question/problem? We can't read your mind and your examples make no sense.

-SQLBill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Kenneth, I see what you mean, but I think the OP made a copy and paste error as the results of 2. is exactly the base data.

Last try, just for fun until you provide clearer specs

CREATE TABLE #t

(

employeeID INT

, effectivedate DATETIME

)

INSERT INTO #t SELECT 1,'2004-12-01'

UNION ALL SELECT 2, '2004-12-01'

UNION ALL SELECT 2, '2005-11-15'

UNION ALL SELECT 3, '2004-12-01'

DECLARE @End DATETIME

SELECT @End = '20051130'

SELECT t1.* FROM #t t1

WHERE t1.effectiveDate=

(SELECT MAX(effectiveDate) FROM #t t2

WHERE t1.employeeID=t2.employeeID

AND t2.effectiveDate < @End)

ORDER BY t1.employeeID

DROP TABLE #t

employeeID effectivedate

----------- ------------------------------------------------------

1 2004-12-01 00:00:00.000

2 2005-11-15 00:00:00.000

3 2004-12-01 00:00:00.000

(3 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

naveed ahmad SSC Veteran Points: 299 More actions · Answer 10

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 to pick up last updated salary for an employee.

e.g.

employeeId SalaryAmt EffectiveDate

1 2000 01-01-2005

2 2000 01-01-2005

3 2000 01-01-2005

Now again suppose, at Nov 15, am employee got raise, now the table look likes

this

employeeId SalaryAmt EffectiveDate

1 2000 01-01-2005

2 2000 01-01-2005

3 2000 01-01-2005

2 2200 15-11-2005

===> So, when i make salary for 'Octber' i will pick salaries like this

employeeId SalaryAmt EffectiveDate

1 2000 01-01-2005

2 2000 01-01-2005

3 2000 01-01-2005

===> when i make salary for 'November' i will pick salaries like this

employeeId SalaryAmt EffectiveDate

1 2000 01-01-2005

2 2000 01-01-2005

2 2200 15-11-2005

3 2000 01-01-2005

**** for employeeId '2' early 15 days salary will be paid for previous rate (2000) and

for later 15 days salry will be paid for latest rate (2200)

===> and when i make salary for 'December' i will pick salaries like this

employeeId SalaryAmt EffectiveDate

1 2000 01-01-2005

2 2200 15-11-2005

3 2000 01-01-2005

=======================

And i need a sql query that fetch the reqired records.

i should be clear now, if anybody have again confusion, then pls write me at naveedilm@hotmail.com , but please try out to slove this problem

looking Farword

Naveed Ahamd

RyanRandall SSChampion Points: 13623 More actions · Answer 11

Hi Naveed,

I think the code below does the trick. It is safe to run the whole thing.

I usually find it easier to manage this kind of historical data if an 'end date' is maintained in the underlying data (as well as a 'start date'). In the example below, I've added an 'effective end date' to the underlying data, and have then used that to help with each of your 3 examples.

--create table

CREATE TABLE #t (employeeId INT, effectiveDate DATETIME)

--insert data

INSERT INTO #t SELECT 1,'2004-12-01'

UNION ALL SELECT 2, '2004-12-01'

UNION ALL SELECT 2, '2005-11-15'

UNION ALL SELECT 3, '2004-12-01'

--add new column to hold 'effective end date'

alter table #t add effectiveEndDate DATETIME

go

--update new column

update #t set effectiveEndDate = minEffectiveDate from

#t inner join

(select t1.employeeId, t1.effectiveDate, minEffectiveDate = min(t2.effectiveDate) from #t t1 left outer join #t t2 on t1.employeeId = t2.employeeId and t1.effectiveDate < t2.effectiveDate group by t1.employeeId, t1.effectiveDate) t3

on #t.employeeId = t3.employeeId and #t.effectiveDate = t3.effectiveDate

--show new table

select * from #t

/*

employeeId effectiveDate effectiveEndDate

----------- ------------- ----------------

1 2004-12-01 NULL

2 2004-12-01 2005-11-15

2 2005-11-15 NULL

3 2004-12-01 NULL

*/

--examples - declarations

declare @fromDate datetime

declare @toDate datetime

--example 1

set @fromDate = '2005-12-01'

set @toDate = '2005-12-31'

select employeeId, effectiveDate from #t where @fromDate < isnull(effectiveEndDate, '31 Dec 9999') and effectiveDate < @toDate

/*

employeeId effectiveDate

----------- -------------

1 2004-12-01

2 2005-11-15

3 2004-12-01

*/

--example 2

set @fromDate = '2005-11-01'

set @toDate = '2005-11-30'

select employeeId, effectiveDate from #t where @fromDate < isnull(effectiveEndDate, '31 Dec 9999') and effectiveDate < @toDate

/*

employeeId effectiveDate

----------- -------------

1 2004-12-01

2 2004-12-01

2 2005-11-15

3 2004-12-01

*/

--example 3

set @fromDate = '2005-10-01'

set @toDate = '2005-10-31'

select employeeId, effectiveDate from #t where @fromDate < isnull(effectiveEndDate, '31 Dec 9999') and effectiveDate < @toDate

/*

employeeId effectiveDate

----------- -------------

1 2004-12-01

2 2004-12-01

3 2004-12-01

*/

--tidy up

drop table #t

Regards,

Ryan

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

naveed ahmad SSC Veteran Points: 299 More actions · Answer 12

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 data

INSERT INTO #t SELECT 1,'2004-12-01'

UNION ALL SELECT 2, '2004-12-01'

UNION ALL SELECT 2, '2005-11-15'

UNION ALL SELECT 3, '2004-12-01'

--examples - declarations

declare @fromDate datetime

declare @toDate datetime

--example 1

set @fromDate = '2005-12-01'

set @toDate = '2005-12-31'

select distinct A.EmployeeId,A.EffectiveDate

from #t A

Where effectiveDate=(

select Max(EffectiveDate)

from #t

Where EffectiveDate<=fromDate

and EmployeeId=A.EmployeeId

)

or effectiveDate=(

select Max(EffectiveDate)

from #t

Where EffectiveDate<=@toDate

and EffectiveDate>=@fromDate

and EmployeeId=A.EmployeeId

)

this works all the three situations.

Anyway, good responce from forum members

Thanks a lot