How to compare dates nearest to present time in order to get applicable data.

[samuel_1991]

Hi everyone, I have a problem in MS SQL.

Due to academic nature, I am supposed to use a query to find out the present applicable tax rate (GST, Good & Sales Tax) without altering the table.

The table has 3 tax rates,

1: WEF (With Effect From) 2004 Jan 1 --- 5%
2: WEF 2007 Jul 1 --- 7%
3: WEF 2011 Jan 1 --- 8%

As of writing in 2010, I would need to use a query to return tax rate as 7% (1 year later, this code should return 8% instead)

This was what I found:

Select Min(datediff(day,EffectiveDate,getdate())) From GST
where datediff(day,EffectiveDate,getdate())> 0;

This code can only detect the minimum past dates (I.e, pointing at 2007 one instead of the 2004 which was a past or 2011 which is future as of writing.)

How can I use the above code to return 7% instead (For now)?

Thank u in advance.

[samuel_1991]

Please help, after trying

Declare @EffectiveDate DateTime
Set @EffectiveDate = (Select EffectiveDate From GST)
Declare @MinWEF Int
Set @MinWEF = Min(DATEDIFF(DAY,@EffectiveDate,getdate()))
Select TaxRate From GST
Where @MinWEF > 0

also does not work.

I have a table as such:

CREATE TABLE dbo.GST 
(
  EffectiveDate datetime NOT NULL,
  TaxRate  float NOT NULL,
  CONSTRAINT PK_GST PRIMARY KEY NONCLUSTERED (EffectiveDate)
)
GO

[sknake]

IF OBJECT_ID('tempdb..#GST', 'U') IS NOT NULL DROP TABLE #GST
CREATE TABLE #GST 
(
  EffectiveDate datetime NOT NULL,
  TaxRate  float NOT NULL,
  CONSTRAINT PK_GST PRIMARY KEY NONCLUSTERED (EffectiveDate)
)
GO
Insert Into #GST (EffectiveDate, TaxRate) Values (Cast('2004-01-01' as datetime), 5.0)
Insert Into #GST (EffectiveDate, TaxRate) Values (Cast('2007-07-01' as datetime), 7.0)
Insert Into #GST (EffectiveDate, TaxRate) Values (Cast('2011-01-01' as datetime), 8.0)
GO
--Use a variable so you can change it for testing
Declare @dt DateTime
Set @dt = Cast('2010-12-31' As DateTime)
--Set @dt = GetDate()

Select Top 1 TaxRate
From #GST
Where EffectiveDate <= @dt
Order By EffectiveDate Desc

[samuel_1991]

IF OBJECT_ID('tempdb..#GST', 'U') IS NOT NULL DROP TABLE #GST
CREATE TABLE #GST 
(
  EffectiveDate datetime NOT NULL,
  TaxRate  float NOT NULL,
  CONSTRAINT PK_GST PRIMARY KEY NONCLUSTERED (EffectiveDate)
)
GO
Insert Into #GST (EffectiveDate, TaxRate) Values (Cast('2004-01-01' as datetime), 5.0)
Insert Into #GST (EffectiveDate, TaxRate) Values (Cast('2007-07-01' as datetime), 7.0)
Insert Into #GST (EffectiveDate, TaxRate) Values (Cast('2011-01-01' as datetime), 8.0)
GO
--Use a variable so you can change it for testing
Declare @dt DateTime
Set @dt = Cast('2010-12-31' As DateTime)
--Set @dt = GetDate()

Select Top 1 TaxRate
From #GST
Where EffectiveDate <= @dt
Order By EffectiveDate Desc

Thank you, the query

--Use a variable so you can change it for testing
Declare @dt DateTime

SET @dt = Cast('2002-12-31' AS DateTime)

--Set @dt = GetDate()

-- The first set can be used to test but if you want current date then must use second set. Alternatively, one can change the clock setting in PC to test.
 

SELECT Top 1 TaxRate

FROM GST

WHERE EffectiveDate <= @dt

ORDER BY EffectiveDate DESC

works well. Solved.