November 30, 2011 at 2:52 am
This is the table ProductRate, rate is set for a product for different date range
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductRate](
[RateID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Rate] [float] NOT NULL,
[Fromdate] [date] NOT NULL,
[Todate] [date] NOT NULL,
CONSTRAINT [PK_ProductRate] PRIMARY KEY CLUSTERED
(
[RateID] 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 [dbo].[ProductRate] ([RateID], [ProductID], [Rate], [Fromdate], [Todate]) VALUES (1, 1, 200, CAST(0xB9330B00 AS Date), CAST(0xD6330B00 AS Date))
INSERT [dbo].[ProductRate] ([RateID], [ProductID], [Rate], [Fromdate], [Todate]) VALUES (2, 1, 300, CAST(0xD8330B00 AS Date), CAST(0xE6330B00 AS Date))
INSERT [dbo].[ProductRate] ([RateID], [ProductID], [Rate], [Fromdate], [Todate]) VALUES (3, 2, 500, CAST(0xC7330B00 AS Date), CAST(0xE6330B00 AS Date))
INSERT [dbo].[ProductRate] ([RateID], [ProductID], [Rate], [Fromdate], [Todate]) VALUES (4, 2, 600, CAST(0xE7330B00 AS Date), CAST(0xFB330B00 AS Date))
INSERT [dbo].[ProductRate] ([RateID], [ProductID], [Rate], [Fromdate], [Todate]) VALUES (5, 1, 1000, CAST(0xF4330B00 AS Date), CAST(0x13340B00 AS Date))
I want to list out the product rate for a date range based on this condition
ProductID =1
1) Fromdate = 2011-01-01 and Todate = 2011-01-29 -- it is simple codition
2)FromDate = 2011-01-20 and Todate = 2011-02-10 -- this range is spread over 2 rows
the result should look like this
date productID rate
2011-01-20 1 200
2011-01-21 " "
2011-01-22 " "
"
"
"
2011-01-30 1 200
2011-02-01 1 300
2011-02-02 1 300
"
"
"
[2011-02-10 1 300
pls help
thank you
November 30, 2011 at 3:20 am
Hi,
does this work for you?
DECLARE @fromdate DATETIME = '2011-01-20';
DECLARE @todate DATETIME = '2011-02-10';
DECLARE @productid INT =1;
WITH DateRange AS
(SELECT @fromdate AS [date]
UNION ALL
SELECT DATEADD(DAY,1,[date]) AS [date] FROM DateRange WHERE [date] < @todate)
SELECT [date], @productid as [productid], p.Rate FROM DateRange
INNER JOIN ProductRate p ON @productid = p.ProductID AND [date] <= p.Todate AND [date] >= p.fromdate
Lars
November 30, 2011 at 4:09 am
thank you very much this is exactly i wanted
November 30, 2011 at 4:37 am
You can also use Tally tables for issues like this which will give you better performance compared to Recursive CTE's, Cursors or While loops.
Search for articles related to Tally Table in this site and you will get a whole lot of usefull information.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply