February 24, 2016 at 12:46 pm
Hi
I'm trying figure out how to build a timeline solution without the cursor?
Scenario:
There is the table where users can define discounts for various sale items, date ranges can overlap.
Sample discount table output
Id ItemId FromDate UpToDate OpenUsr OpenTime DscVal
----------- ----------- ---------- ---------- --------------------------------------------- ----------------------- -------
1 10015 2016-01-01 2016-12-31 STom 2016-02-24 14:02:22.900 20.84
3 10015 2016-03-01 2016-03-28 PArdo 2016-02-24 14:02:22.900 33.33
4 10015 2016-03-19 2016-05-31 PArdo 2016-02-24 14:02:22.900 35.00
Main goal is to take all the dates in the ranges and make a timeline where the last discount has the highest priority for selected item,
they say “a picture is worth a thousand words”, so it is explained graphically (example.jpg).
I'm looking for the outcome:
ItemId FromDate UpToDate OpenUsr OpenTime DscVal
----------- ---------- ---------- --------------------------------------------- ----------------------- ------
10015 2016-01-01 2016-02-29 STom 2016-02-24 20:02:22.900 20.84
10015 2016-03-01 2016-03-18 PArdo 2016-02-24 20:02:22.900 33.33
10015 2016-03-19 2016-05-31 PArdo 2016-02-24 20:02:22.900 35.00
10015 2016-06-01 2016-12-31 STom 2016-02-24 20:02:22.900 20.84
What I did (so far):
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'dbo.Discount', N'U') IS NOT NULL
DROP TABLE dbo.Discount;
CREATE TABLE dbo.Discount
(
IdINTNOT NULL IDENTITY(1, 1),
ItemIdINTNOT NULL,
FromDateDATENOT NULL,
UpToDateDATENOT NULL,
OpenUsrNVARCHAR(45)NOT NULL,
OpenTimeDATETIMEDEFAULT GETDATE(),
DscValDECIMAL(8,2)
CONSTRAINT PK_Discount PRIMARY KEY(id),
CONSTRAINT C_date
CHECK (FromDate <= UpToDate)
);
--Sapmle dummy data
INSERT INTO dbo.Discount (ItemId, FromDate, UpToDate,OpenUsr,DscVal) VALUES
(10015,'20160101','20161231','STom',20.84),
(10036,'20160101','20161231','IVcker',18.02),
(10015,'20160301','20160328','PArdo',33.33),
(10015,'20160319','20160531','PArdo',35),
(10036,'20160401','20160430','LCosta',25.50),
(10036,'20160502','20161031','ITrumph',20.85);
--Calendar table
--SELECT * from dbo.Discount where itemId = 10015
IF OBJECT_ID('dbo.Calendar', N'U') IS NOT NULL
DROP TABLE dbo.Calendar;
CREATE TABLE dbo.Calendar
(
CalDateDATE NOT NULL
CONSTRAINT PK_Calendar PRIMARY KEY(CalDate));
--Albert Hetzel's fuction to populate Calendar
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DateRange]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.DateRange;
GO
CREATE FUNCTION [dbo].[DateRange]
(
@Increment CHAR(1),
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS
@SelectedRange TABLE
(IndividualDate DATETIME)
AS
BEGIN
;WITH cteRange (DateRange) AS (
SELECT @StartDate
UNION ALL
SELECT
CASE
WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
END
FROM cteRange
WHERE DateRange <=
CASE
WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
END)
INSERT INTO @SelectedRange (IndividualDate)
SELECT DateRange
FROM cteRange
OPTION (MAXRECURSION 3660);
RETURN
END
GO
INSERT INTO dbo.Calendar
SELECT IndividualDate FROM DateRange('d', '20160101', '20161231')
GO
-- Temporary table for cursor
DECLARE @ItemTimeLine TABLE (
LineDateDATE NOT NULL,
IdINT NOT NULL,
RowGroupINT
);
--Timeline, discount for item 10015
;WITH ctDicount AS (
SELECTId,
ItemId,
FromDate,
UpToDate,
DscVal
FROM Discount
WHERE ItemId = 10015
), ctWithCal AS (
SELECTctm.Id,
cld.CalDate
FROM ctDicount AS ctm
INNER JOIN
dbo.Calendar AS cld
ON cld.CalDate BETWEEN ctm.FromDate AND ctm.UpToDate
)
-- so far w/o cursor
INSERT INTO @ItemTimeLine (LineDate, Id)
SELECTCalDate,
Id = MAX(Id)
FROM ctWithCal
GROUP BY CalDate;
--Cursor, extremly slow!!
DECLARE
@mdAS DATE,
@idAS INT = 1,
@idoldAS INT = 1,
@rnAS INT = 1
DECLARE TmelineCursor CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT LineDate,
Id
FROM @ItemTimeLine ORDER BY LineDate;
OPEN TmelineCursor;
FETCH NEXT FROM TmelineCursor INTO @md, @id;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @ItemTimeLine SET RowGroup = @rn WHERE LineDate = @md;
SET @idold = @id
FETCH NEXT FROM TmelineCursor INTO @md, @id;
IF (@idold <> @id)
BEGIN
SET @rn = @rn + 1
END
END
CLOSE TmelineCursor;
DEALLOCATE TmelineCursor;
-- Final outcome
; WITH ctDisp AS
(
SELECTRowGroup,
Id,
FromDate= MIN(LineDate),
ToDate= MAX(LineDate)
FROM @ItemTimeLine
GROUP BY RowGroup,Id
)
SELECT
dsc.ItemId,
FromDate= ctd.FromDate,
UpToDate= ctd.ToDate,
dsc.OpenUsr,
dsc.OpenTime,
dsc.DscVal
FROM dbo.Discount as dsc
INNER JOIN
ctDisp as ctd
ON dsc.Id = ctd.Id
ORDER BY ctd.RowGroup;
GO
I'm investigating what to do to avoid cursor and improve performance?
Thers's only one year defined in the calendar at this moment, but there will be 20 years in final solution,
so the timeline calculation extremaly slow down.
Is there a way to rewrite this code so that it doesn't use cursor?
February 24, 2016 at 12:55 pm
Do a web search for sql server gaps and island and review what you find. I would focus on Itzik Ben-Gan's stuff probably (and note he wrote a chapter in the MVP Deep Dives book on that topic). There are some VERY slick solutions to this on 2012+ that you can probably adapt to do this very efficiently.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 26, 2016 at 1:01 pm
I didn't read all your code, sorry.
Is this helpful?
(common alternative to a cursor)
while @RowCnt <= @MaxRows
begin
...
Select @RowCnt = @RowCnt + 1
end
February 27, 2016 at 3:02 am
Jon.Morisi (2/26/2016)
I didn't read all your code, sorry.Is this helpful?
(common alternative to a cursor)
while @RowCnt <= @MaxRows
begin
...
Select @RowCnt = @RowCnt + 1
end
No, it's not. Your code is indeed a common alternative to a cursor. And that is very unfortunate, because it also is slower than a well-tuned cursor.
Cursors should be replaced by set-based code, not by other iterative solutions. If there is no setbased alternative, then tuning the cursor is the least bad option.
March 1, 2016 at 1:44 am
Hi All
Thanks for suggestions, as Hugo wrote, looping through table records as a substitute for cursor is not the best solution.
I'm trying to find a set-based option to increase counter on RowGroup when Id is changed, but I couldn't figure out how to do that.
Expected result:
LineDateIdRowGroup
2016-01-0111
2016-01-0211
...
2016-02-2811
2016-02-2911
2016-03-0132
2016-03-0232
...
2016-03-1832
2016-03-1943
...
2016-05-3143
2016-06-0114
...
2016-12-3114
Best regards
Mike
March 1, 2016 at 8:38 am
Seems that a LAG statement in a CASE should be able to interrogate the prior value and increment the current count when those two differ.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2016 at 8:56 am
Here's an option that could help you achieve your goal. I'm not posting the full solution so you need to understand what's going on.
WITH
cteRows AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY c.calDate, d.ItemId ORDER BY FromDate DESC) rn
FROM Calendar c
JOIN Discount d ON c.calDate BETWEEN d.FromDate AND d.UpToDate
)
SELECT *,
DATEADD( dd, -ROW_NUMBER() OVER( PARTITION BY ItemId, FromDate ORDER BY calDate), calDate) grouper
FROM cteRows
WHERE rn = 1;
March 1, 2016 at 1:01 pm
michal.lisinski (3/1/2016)
Thanks for suggestions, as Hugo wrote, looping through table records as a substitute for cursor is not the best solution.I'm trying to find a set-based option to increase counter on RowGroup when Id is changed, but I couldn't figure out how to do that.
Your expected result confirms what Kevin (TheSQLGuru) already suspected: this is an "islands" problem.
Go back in the thread to the first answer you received and follow the suggestions posted there for a great, fast, and set-based solution to this problem.
March 2, 2016 at 1:48 am
Thanks Luis, you saved my time, that's it what I was looking for 🙂
Hugo, Kevin's advice was very helpful, but I have been scratching my head on this for three
days now trying different ways, come close but not ever what I want, so Luis's example
pointed me in the right direction.
Best regards
Mike
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply