November 6, 2015 at 10:16 am
Hey everyone,
I see that another SQLServerCentral member posted a cursor-based query he/she had and you made suggestions to improve the performance. I have a cursor-based procedure that I am hoping you can help with.
The purpose of this procedure is basically to turn this:
HsLog
ClientNum InDate OutDate
4 2013-11-04 16:37:10.000 2013-11-09 12:42:41.000
4 2013-11-09 12:42:41.000 2013-11-13 02:10:22.000
4 2013-11-13 02:10:22.000 2013-11-15 06:17:58.000
Into this (nevermind that the ClientNums aren't the same):
Reformatted_HsLog
ClientNumHousingDate MinutesInFacility
4 2013-11-04 443
4 2013-11-05 1440
4 2013-11-06 1440
4 2013-11-07 1440
4 2013-11-08 1440
4 2013-11-09 1440
4 2013-11-10 1440
4 2013-11-11 1440
4 2013-11-12 1440
4 2013-11-13 1440
4 2013-11-14 1440
4 2013-11-15 377
I need to turn the date ranges (InDate - OutDate) into individual days, tracking the number of minutes that a client
is in one of our facilities for each day.
I made a procedure to do this using a cursor (I know, bad practice). I couldn't think of any other way to do it.
I gave this procedure a spin and it never finished. I stopped it at about 2 hours of run time - I may have won the
award for longest-running procedure in my organization 🙂
Here it is:
----------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE Reformat_HsLog
AS
BEGIN --Begin procedure
SET NOCOUNT ON; --Improve procedure performance
/*Set up the variables & cursor*/
DECLARE @client_num AS char(10); --For the cursor
DECLARE @client_indate AS datetime; --For the cursor
DECLARE @client_outdate AS datetime; --For the cursor
DECLARE @client_cursor AS cursor; --The cursor
DECLARE @previous_client_num AS char(10); --To keep track of the client number of previous row
DECLARE @minutes AS int; --To tally up minutes for each date
DECLARE @previous_outdate AS datetime; --To keep track of outdate of previous row
SET @client_cursor = CURSOR FOR
SELECTclientNum, InDate, OutDate
FROMHsLog;
OPEN @client_cursor;
FETCH NEXT FROM @client_cursor INTO @client_num, @client_indate, @client_outdate; --The first fetch
SET @previous_client_num = @client_num; --Start keeping track of the client number
/*Let the fun begin!*/
------------------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0 --Gets to the end of the table
BEGIN --Begin while loop #1
------------------------------------------------------------------------------------------
WHILE (CAST(@client_indate AS date) = CAST(@client_outdate AS date)) AND
(@client_num = @previous_client_num) --While current indate = current outdate and current client num = previous client num
BEGIN --Begin while loop #2
SET @minutes = @minutes + DATEDIFF(minute, @client_indate, @client_outdate); --Grab the minutes
SET @previous_outdate = @client_outdate; --Keep track of previous outdate
SET @previous_client_num = @client_num; --Keep track of client number of previous row
FETCH NEXT FROM @client_cursor INTO @client_num, @client_indate, @client_outdate; --Next row
IF CAST(@client_indate AS date) = CAST(@previous_outdate AS date) AND
(@client_num = @previous_client_num) --If current indate = previous outdate and current client num = previous client num
SET @minutes = @minutes + DATEDIFF(minute, @previous_outdate, @client_indate); --Grab the minutes
ELSE
INSERT INTO Reformatted_HsLog(ClientNum, HousingDate, MinutesInFacility) --Insert record
VALUES(@previous_client_num, CAST(@previous_outdate AS date), @minutes);
SET @previous_outdate = '1900-01-01 00:00:00'; --Reset variable
SET @minutes = 0; --Reset variable
END; --End while loop #2
------------------------------------------------------------------------------------------
WHILE CAST(@client_indate AS date) <> CAST(@client_outdate AS date) --While current indate <> current outdate
BEGIN --Begin while loop #3
SET @minutes = @minutes + DATEDIFF(minute, @client_indate,
DATEADD(day, 1, DATEADD(dd, DATEDIFF(dd, 0, @client_indate), 0))); --Next day at 00:00:00.000
INSERT INTO Reformatted_HsLog(ClientNum, HousingDate, MinutesInFacility) --Insert record
VALUES(@client_num, CAST(@client_indate AS date), @minutes);
SET @client_indate = DATEADD(day, 1, DATEADD(dd, DATEDIFF(dd, 0, @client_indate), 0)); --Next day at 00:00:00.000
SET @minutes = 0; --Reset variable
END; --End while loop #3
------------------------------------------------------------------------------------------
END; --End while loop #1
------------------------------------------------------------------------------------------
END; --End procedure
-----------------------------------------------------------------------------------------------------------------------------------
Any ideas of how I can turn this into a set-based procedure, or improve it?
Let me know if the procedure doesn't make sense and I will explain it more.
November 6, 2015 at 10:22 am
Hi and welcome to the forums! Kudos on wanting to get rid of this menace of code. You have a cursor and nested inside that cursor are two while loops. YIKES!!! This looks to be replaceable with a single insert statement using a tally table. What I don't quite understand though is what this is trying to do. You did a good job posting sample data but the desired output doesn't match the sample data which makes it really difficult to understand what you are trying to do.
Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 6, 2015 at 10:30 am
Thanks for your quick response, Sean. I will try to make it more understandable.
November 6, 2015 at 11:03 am
Would something like this work for you?
http://www.sqlservercentral.com/Forums/Topic1732401-3412-1.aspx#bm1732424
November 6, 2015 at 11:47 am
Possibly. I am looking at your tally table. Thank you for sharing.
November 6, 2015 at 12:08 pm
CKinley (11/6/2015)
Possibly. I am looking at your tally table. Thank you for sharing.
Feel free to ask any questions that you might have.
November 9, 2015 at 11:35 am
This may give you some ideas. I used a placeholder of <your_tally_table> for the tally table. Replace that with your own tally table name, either a physical tally table or cte tally table. Naturally also change the column name "tally" to whatever the column name in your tally table is.
SELECT
ClientNum,
DATEADD(DAY, (days.tally - 1), InDate_Midnight) AS HousingDate,
SUM(
CASE WHEN is_first_day = 1
THEN CASE WHEN is_last_day = 1
THEN DATEDIFF(MINUTE, hl.InDate, hl.OutDate)
ELSE DATEDIFF(MINUTE, hl.InDate, DATEADD(DAY, days.tally, InDate_Midnight))
END
WHEN is_last_day = 1 THEN DATEDIFF(MINUTE, DATEADD(DAY, (days.tally - 1), InDate_Midnight), hl.OutDate)
ELSE 1440 END
) AS MinutesInFacility
FROM #HsLog hl
CROSS APPLY (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, hl.InDate), 0) AS InDate_Midnight
) AS assign_alias_names1
INNER JOIN <your_tally_table> days ON days.tally BETWEEN 1 AND DATEDIFF(DAY, hl.InDate, hl.OutDate) + 1
CROSS APPLY (
SELECT
CAST(CASE WHEN days.tally = 1 THEN 1 ELSE 0 END AS bit) AS is_first_day,
CAST(CASE WHEN DATEDIFF(DAY, DATEADD(DAY, (days.tally - 1), hl.InDate), hl.OutDate) = 0 THEN 1 ELSE 0 END AS bit) AS is_last_day
) AS assign_alias_names
GROUP BY ClientNum, DATEADD(DAY, (days.tally - 1), InDate_Midnight)
ORDER BY ClientNum, HousingDate
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply