May 31, 2012 at 7:43 am
Hi Lynn
For the following set of values ..it generating error
INSERT INTO #ProductRate(ProductID,FromDate,Rate)
SELECT 1, '2012-05-01', 10
UNION ALL
SELECT 1, '2012-05-15', 20
UNION ALL
SELECT 1, '2012-05-23', 55
UNION ALL
SELECT 1, '2012-05-26', 55
UNION ALL
SELECT 1, '2012-05-28', 25
UNION ALL
SELECT 1, '2012-06-01', 50
UNION ALL
SELECT 2, '2012-05-28', 50
GO
here is the full code that executing this data
CREATE TABLE #ProductRate(
[ProductID] [int] NOT NULL,
[FromDate] [datetime] NOT NULL,
[Rate] [numeric](9,2) NOT NULL)
GO
SELECT * FROM #ProductRate
INSERT INTO #ProductRate(ProductID,FromDate,Rate)
SELECT 1, '2012-05-01', 10
UNION ALL
SELECT 1, '2012-05-15', 20
UNION ALL
SELECT 1, '2012-05-23', 55
UNION ALL
SELECT 1, '2012-05-26', 55
UNION ALL
SELECT 1, '2012-05-28', 25
UNION ALL
SELECT 1, '2012-06-01', 50
UNION ALL
SELECT 2, '2012-05-28', 50
GO
DECLARE @fromdate DATETIME;
SET @fromdate = '2012-05-24';
DECLARE @todate DATETIME;
SET @todate = '2012-05-26';
WITH
e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows
e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows
e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows
cteTally(n) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4),
BaseData1 AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY FromDate) RowNum,
ProductID,
FromDate,
Rate
FROM
#ProductRate
), BaseData2 as (
SELECT
bd1a.RowNum,
bd1a.ProductID,
bd1a.FromDate,
bd1a.Rate,
ISNULL(bd1b.FromDate, GETDATE()) ToDate
FROM
BaseData1 bd1a
LEFT OUTER JOIN BaseData1 bd1b
ON (bd1a.ProductID = bd1b.ProductID
AND bd1a.RowNum = bd1b.RowNum - 1)
)
SELECT DISTINCT
pr.ProductID,
pr.FromDate,
pr.Rate
FROM
BaseData2 pr
CROSS APPLY (SELECT TOP(DATEDIFF(dd,pr.FromDate,pr.ToDate)) DATEADD(dd,n,pr.FromDate) ActiveDate FROM cteTally) ad
WHERE
ad.ActiveDate BETWEEN @fromdate AND @todate;
DROP TABLE #ProductRate
GO
This Error is coming with the result
Msg 127, Level 15, State 1, Line 7
A TOP N value may not be negative.
May 31, 2012 at 8:41 am
Problem is data related:
SELECT 1, '2012-06-01', 50
The data above is a future date and was not anticipated when I wrote the code provided. Here is an apportunity for you to figure out what needs to be changed to fix this issue. If you have any questions, be sure to ask.
May 31, 2012 at 9:39 am
Lynn Pettis (5/31/2012)
Problem is data related:SELECT 1, '2012-06-01', 50
The data above is a future date and was not anticipated when I wrote the code provided. Here is an apportunity for you to figure out what needs to be changed to fix this issue. If you have any questions, be sure to ask.
Thanks Lynn for pointing out the Error...
Changed the Getdate() to future date --'2999-12-31'
it solved the issue
Thank you very much for your support
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply