May 30, 2012 at 4:58 am
Hi
Following is the code
CREATE TABLE #ProductRate(
[ProductID] [int] NOT NULL,
[FromDate] [date] NOT NULL,
[Rate] [numeric](9,2) NOT NULL)
GO
INSERT INTO #ProductRate(ProductID,FromDate,Rate)
SELECT 1,'2012-05-23',100
UNION ALL
SELECT 1,'2012-05-26',150
UNION ALL
SELECT 1,'2012-05-28',1000
UNION ALL
SELECT 2,'2012-05-20',1500
UNION ALL
SELECT 2,'2012-05-25',2000
UNION ALL
SELECT 2,'2012-05-28',1000
GO
--SELECT * FROM #ProductRate
--GO
DECLARE @fromdate DATETIME = '2012-05-24';
DECLARE @todate DATETIME = '2012-05-26';
SELECT * FROM #ProductRate
WHERE FromDate BETWEEN @fromdate AND @todate
--@fromdate>= FromDate AND @todate >= FromDate
--(@fromdate BETWEEN [FromDate] AND @todate)
DROP TABLE #ProductRate
GO
In table #ProductRate there is only one field FromDate, For a ProductID = 1 FromDate = '2012-05-23' and Rate = 100 is set
so for ProductID = 1 Rate = 100 is set for Date from 23 to 25 till the next Fromdate is set
I want the result to display like this
ProductID FromDate Rate
1 2012-05-23 100.00
1 2012-05-26 150.00
2 2012-05-20 1500.00
2 2012-05-25 2000.00
May 30, 2012 at 5:05 am
I tried this also
DECLARE @fromdate DATETIME = '2012-05-24';
DECLARE @todate DATETIME = '2012-05-26';
SELECT @fromdate = (SELECT TOP 1 FromDate FROM #ProductRate WHERE FromDate <= @fromdate ORDER BY FromDate DESC)
SELECT @fromdate
SELECT @todate = (SELECT TOP 1 FromDate FROM #ProductRate WHERE FromDate >= @todate)
SELECT @todate
;WITH DateRange AS
(
SELECT @fromdate AS [date]
UNION ALL
SELECT DATEADD(DAY,1,[date]) AS [date] FROM DateRange WHERE [date] < @todate
)
SELECT ProductID,[date],Rate FROM DateRange
INNER JOIN #ProductRate
ON [date] <= FromDate AND [date] <= FromDate
May 30, 2012 at 5:47 am
This was removed by the editor as SPAM
May 30, 2012 at 6:16 am
Thanks for the reply..it almost solved the problem
But for this Set of values it is not displaying anything
DECLARE @fromdate DATETIME = '2012-05-29';
DECLARE @todate DATETIME = '2012-05-30';
It should display like this
ProductID FromDate Rate
1 2012-05-28 1000
2 2012-05-28 1000
May 30, 2012 at 6:20 am
This was removed by the editor as SPAM
May 30, 2012 at 7:09 am
For the following set of value
DECLARE @fromdate DATETIME = '2012-05-23';
DECLARE @todate DATETIME = '2012-05-27';
ProductID FromDate Rate
1 2012-05-23 100.00
2 2012-05-20 1500.00
2 2012-05-25 2000.00
One row is missing For ProductID = 1,FromDate = 2012-05-26, rate = 150,
This problem is arising specifically for value containing data in table FromDate = @fromdate or FromDate = @todate
The problem exists in the previous query too
May 30, 2012 at 7:54 am
Think this is the required query. Could be tidier and perform between other ways but this passes all your suggested outcomes.
-- Sample data
declare @ProductRate table (
[ProductID] [int] NOT NULL,
[FromDate] [date] NOT NULL,
[Rate] [numeric](9,2) NOT NULL
)
INSERT INTO @ProductRate
SELECT 1,'2012-05-23',100
UNION ALL
SELECT 1,'2012-05-26',150
UNION ALL
SELECT 1,'2012-05-28',1000
UNION ALL
SELECT 2,'2012-05-20',1500
UNION ALL
SELECT 2,'2012-05-25',2000
UNION ALL
SELECT 2,'2012-05-28',1000
--Query starts here
declare @fromdate datetime ='2012-05-23';
declare @todate datetime ='2012-05-27';
-- as not sequence number exists find sequence using row_number()
with DateSorted as (
select * ,
ROW_NUMBER() over (partition by ProductID order by FromDate asc) as RN
from @ProductRate
),
-- find todate for each range. The last range is hardcoded to 2999-12-31
DateRanges as (
select DR1.*, dateadd(dd,-1,isnull(DR2.FromDate ,'2999-12-31')) as ToDate
from DateSorted as DR1
left join DateSorted as DR2
on DR1.ProductID = DR2.ProductID
and DR1.RN = DR2.RN-1
)
select *
from DateRanges
where
(@fromdate between FromDate and ToDate)
OR (@todate between FromDate and ToDate)
Fitz
May 30, 2012 at 7:55 am
This was removed by the editor as SPAM
May 30, 2012 at 8:15 am
Hi dilipd006,
I have debugged your code and the problem lies with the declaration of your parameters.
You must explicitly convert them to date e.g.
DECLARE @fromdate DATETIME = CONVERT(DATE,'2012-05-24');
DECLARE @todate DATETIME = CONVERT(DATE,'2012-05-26');
As for the date range between '2012-05-29' and '2012-05-30'
I would not expect this to return any data anyway as there are no dates in this date range in the sample set you provided.
Hope this helps.
May 30, 2012 at 8:21 am
Anthony Kowaliw (5/30/2012)
Hi dilipd006,I have debugged your code and the problem lies with the declaration of your parameters.
You must explicitly convert them to date e.g.
DECLARE @fromdate DATETIME = CONVERT(DATE,'2012-05-24');
DECLARE @todate DATETIME = CONVERT(DATE,'2012-05-26');
As for the date range between '2012-05-29' and '2012-05-30'
I would not expect this to return any data anyway as there are no dates in this date range in the sample set you provided.
Hope this helps.
The rows that have the fromdate of '2012-05-28' are still the live rows (values) so can be thought of as having an end date of infinity. It is probably easier to select a fixed date well in the future.
The date range '2012-05-29' to '2012-05-30' should return all rows that are live on these dates.
If its easier to think about, try having each record as a person with only the date of birth stated. Until the new row is added that person is alive. So the query is who is alive between these dates.
Fitz
May 30, 2012 at 9:04 am
How does this do?
CREATE TABLE #ProductRate(
[ProductID] [int] NOT NULL,
[FromDate] [datetime] NOT NULL,
[Rate] [numeric](9,2) NOT NULL)
GO
INSERT INTO #ProductRate(ProductID,FromDate,Rate)
SELECT 1,'2012-05-23',100
UNION ALL
SELECT 1,'2012-05-26',150
UNION ALL
SELECT 1,'2012-05-28',1000
UNION ALL
SELECT 2,'2012-05-20',1500
UNION ALL
SELECT 2,'2012-05-25',2000
UNION ALL
SELECT 2,'2012-05-28',1000
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
May 30, 2012 at 11:28 pm
Stewart "Arturius" Campbell (5/30/2012)
Does changing the MaxtoDate CTE as follows help:
MaxToDate AS(SELECT ProductID, MIN(FromDate) MinToDate
FROM #ProductRate
WHERE FromDate >= @todate
GROUP BY ProductID)
Hi stewart
for the following values it fails
DECLARE @fromdate DATETIME = '2012-05-23';
DECLARE @todate DATETIME = '2012-05-26';
May 30, 2012 at 11:36 pm
Mark Fitzgerald-331224 (5/30/2012)
Think this is the required query. Could be tidier and perform between other ways but this passes all your suggested outcomes.
-- Sample data
declare @ProductRate table (
[ProductID] [int] NOT NULL,
[FromDate] [date] NOT NULL,
[Rate] [numeric](9,2) NOT NULL
)
INSERT INTO @ProductRate
SELECT 1,'2012-05-23',100
UNION ALL
SELECT 1,'2012-05-26',150
UNION ALL
SELECT 1,'2012-05-28',1000
UNION ALL
SELECT 2,'2012-05-20',1500
UNION ALL
SELECT 2,'2012-05-25',2000
UNION ALL
SELECT 2,'2012-05-28',1000
--Query starts here
declare @fromdate datetime ='2012-05-23';
declare @todate datetime ='2012-05-27';
-- as not sequence number exists find sequence using row_number()
with DateSorted as (
select * ,
ROW_NUMBER() over (partition by ProductID order by FromDate asc) as RN
from @ProductRate
),
-- find todate for each range. The last range is hardcoded to 2999-12-31
DateRanges as (
select DR1.*, dateadd(dd,-1,isnull(DR2.FromDate ,'2999-12-31')) as ToDate
from DateSorted as DR1
left join DateSorted as DR2
on DR1.ProductID = DR2.ProductID
and DR1.RN = DR2.RN-1
)
select *
from DateRanges
where
(@fromdate between FromDate and ToDate)
OR (@todate between FromDate and ToDate)
Fitz
Hi mark
For the following value it fails
declare @fromdate datetime ='2012-05-20';
declare @todate datetime ='2012-05-30';
this result is coming
ProductIDFromDate RateRNToDate
1 2012-05-281000.0032999-12-30
2 2012-05-201500.0012012-05-24
2 2012-05-281000.0032999-12-30
Expected result
ProductIDFromDate Rate
1 2012-05-23100.00
1 2012-05-26150.00
1 2012-05-281000.00
2 2012-05-201500.00
2 2012-05-252000.00
2 2012-05-281000.00
May 30, 2012 at 11:47 pm
Thank you very much Lynn Pettis.... it works really fine
Can u explain the CTE part..Taking 10 rows,100 rows
or may be some url
Thanks
May 31, 2012 at 12:08 am
dilipd006 (5/30/2012)
Thank you very much Lynn Pettis.... it works really fineCan u explain the CTE part..Taking 10 rows,100 rows
or may be some url
Thanks
The first part of the CTE (e1 through cteTally) creates a dynamic tally (or numbers) table that starts at 0 and ends at 10,000 for a total 10,001 rows.
I use it with the base data to establish the active dates for ProductID and Rate . This allows me to query the data to determine what ProductID and Rate pairs are active during any specified period.
For more information regarding Tally Tables, click on the fourth link (actually references Tally Tables) in my signature block below.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply