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
  • 665 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by samuel_1991

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.