Date range query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date range query

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-26 : 11:49:51

Hi Folks,
I have a date related query that I need help with

I have the following table,
ref, salary, saldate
10, 12000, 1-Jan-08
10, 12500,1-Sep-08
10, 13000, 1-Jan-09
12, 11000, 1-jan-08
13, 27000, 1-oct-08
13, 28000, 1-jan-09

What I want is to use a parameter such as start date and end date (based on the saldate field) to show all salary changes in this period.

ie,

 select salary from salarytable where saldate >= '1-Aug-08' and saldate <= '1-Jan-09'

This will return 2 rows.. However, this does not show that ref 10 had a salary of 12000 on the 1-aug-08 (the criteria).

IS this possible?

thanks
Craig.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 13:15:45

Here is one way to show the salary for a given Ref As-Of a certain date:


declare @t table (ref int, salary int, saldate datetime)
insert @t 
select 10, 12000, '1-Jan-08' union all
select 10, 12500, '1-Sep-08' union all
select 10, 13000, '1-Jan-09'

declare @SalareAsOfDate datetime
set @SalareAsOfDate = '2008-08-01'
;with cte
as
(
select ref
       ,salary
       ,saldate
       ,row_number() over (partition by ref order by saldate) rn
from @t
)
select curr.ref
       ,[SalaryAsOf] = @SalareAsOfDate
       ,curr.salary
from   cte curr
left outer join cte nxt
       on nxt.ref = curr.ref 
       and nxt.rn-1 = curr.rn
where  curr.salDate <= @SalareAsOfDate
and    isNull(nxt.salDate, '2900-01-01') > @SalareAsOfDate


OUTPUT:
ref         SalaryAsOf              salary
----------- ----------------------- -----------
10          2008-08-01 00:00:00.000 12000

EDIT:
corrected

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 13:38:38

or maybe this is a little easier:


select top 1 *
from   @t
where  saldate < @SalareAsOfDate
order by saldate

Be One with the Optimizer
TG

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-27 : 04:30:05

Thanks for the replys guys, but I dont think you are picking me up correctly.. I need it to show all 3 fields as the startdate criteria is 1-Aug-08, which means that the employee would be on 12000 at that time.. I need the query to show this.

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 04:39:48

[code]DECLARE @Sample TABLE
(
ref INT,
salary MONEY,
saldate DATETIME
)

INSERT @Sample
SELECT 10, 12000, '1-Jan-08' UNION ALL
SELECT 10, 12500, '1-Sep-08' UNION ALL
SELECT 10, 13000, '1-Jan-09' UNION ALL
SELECT 12, 11000, '1-jan-08' UNION ALL
SELECT 13, 27000, '1-oct-08' UNION ALL
SELECT 13, 28000, '1-jan-09'

declare @SalaryAsOfDate datetime
set @SalaryAsOfDate = '2008-08-01'

SELECT ref,
salary,
saldate
FROM (
SELECT ref,
salary,
saldate,
row_number() over (partition by ref order by saldate desc) as recid
FROM @Sample
WHERE saldate <= @SalaryAsOfDate
) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-27 : 05:14:15

I've just realised that the db is sql 2000.

This function does not work...

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-27 : 10:43:13

Its ok guys, Ive figured it out and got it working. thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources