Member Avatar for 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.

Edited by samuel_1991 because: n/a
  • 2 Contributors
  • 3 Replies
  • 706 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by samuel_1991

Dani AI

Generated

Good: the right rule is “pick the most recent EffectiveDate that is on-or-before the target date” — which is exactly what ’s TOP 1 / ORDER BY DESC solution implements and what solved the original problem for . An alternative that expresses the same logic without TOP is to find the MAX(EffectiveDate) <= target-date and return the rate for that date:

-- find the most recent effective date on-or-before @dt and return its rate
DECLARE @dt datetime = GETDATE();  -- or set an explicit date for testing

SELECT TaxRate
FROM GST
WHERE EffectiveDate =
    (SELECT MAX(EffectiveDate) FROM GST WHERE EffectiveDate <= @dt);

Notes and edge cases worth calling out:

  • If there’s no row with EffectiveDate <= the target date the subquery yields NULL and no row is returned; decide whether a NULL, a default rate, or an error is the correct fallback and handle that (COALESCE/ISNULL or application logic).
  • Watch the time-of-day part of datetime: if EffectiveDate carries a time component and the intention is to compare by calendar date only, normalize both sides to date (or store EffectiveDate as a date type) so midnight/time differences don’t produce surprising results.
  • Prefer passing an explicit transaction or reporting date (like @dt) instead of always using GETDATE() when the query is used for historical lookups or testing.

Performance/production tips:

  • With an index on EffectiveDate the MAX(...) or TOP 1 approach is very cheap because the engine can seek the index; confirm this with an execution plan. For very high-volume systems consider a covering index or a clustered index on EffectiveDate, but that changes schema so weigh constraints first.

Recommended Answers

All 3 Replies

Member Avatar for 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
Member Avatar for 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
commented: Very good, that is what I want. Thank you very much. +2
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.