September 6, 2018 at 12:27 am
I have a sample table and its data as below:
CREATE TABLE [dbo].[ProductBMRate](
[ProductBMRateId] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [int] NOT NULL,
[Rate] [decimal](9, 0) NOT NULL,
[EffectiveDate] [date] NOT NULL,
[ExpiryDate] [date] NULL,
CONSTRAINT [PK_ProductBMRate] PRIMARY KEY CLUSTERED
(
[ProductBMRateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (1, 45, '08/01/2018','09/04/2018')
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (1, 50, '09/05/2018','09/10/2019')
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (1, 52, '09/11/2018','09/25/2018')
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (1, 52, '09/26/2018', NULL)
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (2, 50, '08/01/2018','09/13/2018')
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (2, 55, '09/14/2018','09/22/2018')
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (2, 60, '09/23/2018',NULL)
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (3, 70, '09/06/2018','09/20/2018')
INSERT INTO [dbo].[ProductBMRate] ([ProductId], [Rate], [EffectiveDate], [ExpiryDate]) VALUES (3, 72, '09/21/2018',NULL)
I want to pull the ProductBMRateId which is between the given date incase of expiry is null then it should pick the rate which belongs to effective date.
Declare @Today date = '2-Sep-2018'
select @Today, max(ProductBMRateId) ProductBMRateId, productId from ProductBMRate
WHERE @Today >= [EffectiveDate] AND @Today <= Case [ExpiryDate] when null then null else [ExpiryDate] end
group by productId
If today is 02-Sep-2018, it picks correct "ProductBMRateId" according to slots between Effective date and Expiry Date, for some how if we do not know the expiry date it should pick the date which is >= effective date. For following case it is giving incorrect "ProductBMRateId" for the case if today is 26-Sep-2018, as we do not know the expiry, it should pick the "ProductBMRateId" = 4 but its showing 2
Declare @Today date = '26-Sep-2018'
select @Today, max(ProductBMRateId) ProductBMRateId, productId from ProductBMRate
WHERE @Today >= [EffectiveDate] AND @Today <= Case [ExpiryDate] when null then null else [ExpiryDate] end
group by productId
SELECT * FROM ProductBMRate WHERE ProductId = 1
please help.
September 6, 2018 at 3:37 am
Your case statement isn't doing anything, it always returns the value of ExpiryDate.
Your problem is that you can't compare things to null using anything other than 'is null'.
this would work
select @Today, max(ProductBMRateId) ProductBMRateId, productId from ProductBMRate
WHERE @Today >= [EffectiveDate] AND ((@Today <= [ExpiryDate]) or (ExpiryDate is null))
group by productId
September 6, 2018 at 8:39 am
You should never use a NULL value as an endpoint of an interval. It becomes complicated very quickly when both the beginning and end of an interval can be null. It is much better to use arbitrary values to represent missing or unknown start or end dates. Typically 1900-01-01 is used for start dates and 9999-12-31, 9999-01-01, or 9000-01-01 are used for end dates. It is important that the dates you pick do not naturally occur in your data, so you may need to pick other dates if your data can contain one or more of these dates. It is even more important that you use a past date for unknown dates in the past, and a future date for unknown dates in the future.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply