May 23, 2012 at 7:30 am
Hi
Following is the code
DECLARE @ProductOption TABLE(ProductOptionID int,Name varchar(100),ProductID int)
INSERT INTO @ProductOption (ProductOptionID,Name,ProductID)
SELECT '1','Wifi','1'
UNION ALL
SELECT '2','Broadband','1'
DECLARE @ProductOptionRate TABLE(ProductOptionID int, FromDate date,Rate numeric(19,5))
INSERT INTO @ProductOptionRate (ProductOptionID,FromDate,Rate)
SELECT '1','2012-05-01','100'
UNION ALL
SELECT '1','2012-05-15','200'
UNION ALL
SELECT '1','2012-05-25','1200'
UNION ALL
SELECT '2','2012-05-01','500'
UNION ALL
SELECT '2','2012-05-15','800'
UNION ALL
SELECT '2','2012-05-25','1800'
DECLARE @FromDate date = '2012-05-18'
DECLARE @ProductID int = 1
SELECT * FROM
(
SELECT
ROW_NUMBER()OVER(PARTITION BY AO.ProductOptionID,FromDate ORDER BY AO.ProductOptionID,FromDate) AS Rownum,
AO.ProductOptionID,
FromDate,
Rate
FROM @ProductOptionRate AS AO
JOIN @ProductOption AS PO
ON AO.ProductOptionID = PO.ProductOptionID
WHERE (PO.ProductID = @ProductID) AND (FromDate <= @FromDate)
) AS D
WHERE D.Rownum = 1
Here DECLARE @FromDate date = '2012-05-18'
So it should display only data which has FromDate = '2012-05-15'
If @FromDate date is Set date between this range from '2012-05-01' to '2012-05-14'
it should display only data which has FromDate = '2012-05-01'
If @FromDate date is Set date between this range from '2012-05-15' to '2012-05-24'
it should display only data which has FromDate = '2012-05-15'
May 23, 2012 at 7:40 am
Your going to have to build in some code which does a check on @FromDate and change the value to the right one for the date range it is in, then change FromDate <= @FromDate to FromDate = @FromDate
Psuedo Code
IF DATEPART(DAY,@FromDate) >= 1 AND DATEPART(DAY,@FromDate) <=14
BEGIN
SET @FromDate = '2012-05-01'
END
ELSE IF DATEPART(DAY,@FromDate) >= 15 AND DATEPART(DAY,@FromDate) <=24
BEGIN
SET @FromDate = '2012-05-15'
END
ELSE
BEGIN
SET @FromDate = '2012-05-25'
END
May 23, 2012 at 8:21 am
The FromDate is not fixed it can be any date ..we can insert any date
May 23, 2012 at 8:23 am
well you will need two variable then one for the lower value in the range and one for @FromDate
Something like @FromDate date, @ToDate date where @FromDate = 2012-05-01 or 2012-05-15 and @ToDate = 2012-05-18 or what ever value you pass in
then do FromDate >= @FromDate and FromDate <=@ToDate
same concept applies, as if your only passing in 1 date, you need to calculate the other date.
May 23, 2012 at 8:38 am
Actually we had the ToDate field..but due to some date range overlapping validation we removed that ....
May 23, 2012 at 8:44 am
So for a month you have ranges split as follows
1st to 14th
15th to 24th
24th to EOM
or do your ranges differ month on month.
If they differ your going to have to build in a ranges table, where each date maps to a range and then do your query based on that range, using a sub query.
If they are static ranges, all you need to do is get the year & month from the passed in variable, and then append the right start DAY value to the variable, following the premesis of the pseudo code I gave before, making sure that you change the SET parts to calculate the dynamic date.
May 23, 2012 at 8:48 am
What are the rules for determining the date range in question?
For example:
If @FromDate date is Set date between this range from '2012-05-01' to '2012-05-14'
it should display only data which has FromDate = '2012-05-01'
If @FromDate date is Set date between this range from '2012-05-15' to '2012-05-24'
it should display only data which has FromDate = '2012-05-15'
How are the date ranges above determined based on an input date?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply