February 28, 2014 at 7:59 am
Hi everybody!
I'm trying to figure out the best way to go about a solution, and I'm trying to avoid using a cursor. I have a table that looks something like this:
InvID ActivityCode Date OldLocationID NewLocationID
***** ********** ******* *********** ************
1 1 1/1/2014 NULL 10
1 3 2/1/2014 10 13
1 3 2/5/2014 13 10
1 3 2/10/2014 10 13
1 3 2/15/2014 13 10
1 3 2/20/2014 10 13
For some basic logic explanation:
Activity Code Values:
1 = Inbound
2 = Outbound
3 = Move to new location
LocationID Fields:
10 = In Warehouse
13 = At Customer Location
Also, the date field is a datetime field; however I kept it date only to keep it simple.
I am trying to come up with how much time (in days) each InvID was in a given location. For example, I want to know how many days InvID 1 was in location 13. In this example, the only way I can come up with to do this is through the use of a cursor; iterating through each row and performing a calculation at each iteration depending on the details of the field values. Is there a better way of or a best practice for handling something like this?
February 28, 2014 at 8:14 am
Depending on how many rows you're looking at, you can use a cursor or you can probably use a loop with IF then statements with a temporary table or tables. I like using cursors if there isn't a lot of data as the logic is simple and it's easy for people other than yourself to read. I would say this, if you're trying to sort through a million rows, it's probably not a great idea to use a cursor.
Edit: I'm sure I could come up with a better way to do this but just looking at this data real quick, I think I could do it with a temporary table, and two new fields, one for a pk and another for "Days". But I think this makes it overly complicated and I would just end up using a cursor.
This is a link[/url] to a good write-up on cursors vs while loops.
February 28, 2014 at 8:34 am
You absolutely do not need a cursor for this. Cursors are horrible for performance as you obviously realize. We can help you but first you need to help us by posting ddl (create table statements), sample data (insert statements) and the desired output based on the sample data. We do not need or want a million rows but there should be enough sample data to adequately represent the real data.
Please take a few minutes and read the first article 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/
February 28, 2014 at 8:36 am
JoshDBGuy (2/28/2014)
Depending on how many rows you're looking at, you can use a cursor or you can probably use a loop with IF then statements with a temporary table or tables. I like using cursors if there isn't a lot of data as the logic is simple and it's easy for people other than yourself to read. I would say this, if you're trying to sort through a million rows, it's probably not a great idea to use a cursor.Edit: I'm sure I could come up with a better way to do this but just looking at this data real quick, I think I could do it with a temporary table, and two new fields, one for a pk and another for "Days". But I think this makes it overly complicated and I would just end up using a cursor.
This is a link[/url] to a good write-up on cursors vs while loops.
It is far better to avoid loops OR cursors in almost every situation. There are a few cases where they make sense but most of the time there is no need for them.
_______________________________________________________________
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/
February 28, 2014 at 8:38 am
Do you have a best practice for cursors? Just don't use them at all or only in certain situations? I tend to use them for small sets of data, such as a couple hundred or a thousand rows. Otherwise I generally use while loops.
Edit: Interesting. Makes me curious to see code response to this request.
February 28, 2014 at 8:43 am
JoshDBGuy (2/28/2014)
Do you have a best practice for cursors? Just don't use them at all or only in certain situations? I tend to use them for small sets of data, such as a couple hundred or a thousand rows. Otherwise I generally use while loops.Edit: Interesting. Makes me curious to see code response to this request.
A while loop can very easily perform far worse than a properly defined cursor. I would avoid either construct as a general rule. Just because your dataset is small when you write the code doesn't mean the dataset will always be small.
The basic issue is that you have to change your mindset. Instead of thinking about what you want to do to a row, you have to think about what you want to do to a column. If the body of your loop is an insert, update or similar it can be rewritten as a set based query with no loops.
Looping is very helpful for some administration types of things or sending emails. Things where it is more involved than just modifying some data in some tables.
_______________________________________________________________
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/
February 28, 2014 at 8:48 am
Sean Lange (2/28/2014)
You absolutely do not need a cursor for this. Cursors are horrible for performance as you obviously realize. We can help you but first you need to help us by posting ddl (create table statements), sample data (insert statements) and the desired output based on the sample data. We do not need or want a million rows but there should be enough sample data to adequately represent the real data.Please take a few minutes and read the first article in my signature for best practices when posting questions.
Thanks for the reply. I'll get ddl, sample data and the desired output posted ASAP.
February 28, 2014 at 8:56 am
Sean Lange (2/28/2014)
JoshDBGuy (2/28/2014)
Do you have a best practice for cursors? Just don't use them at all or only in certain situations? I tend to use them for small sets of data, such as a couple hundred or a thousand rows. Otherwise I generally use while loops.Edit: Interesting. Makes me curious to see code response to this request.
A while loop can very easily perform far worse than a properly defined cursor. I would avoid either construct as a general rule. Just because your dataset is small when you write the code doesn't mean the dataset will always be small.
The basic issue is that you have to change your mindset. Instead of thinking about what you want to do to a row, you have to think about what you want to do to a column. If the body of your loop is an insert, update or similar it can be rewritten as a set based query with no loops.
Looping is very helpful for some administration types of things or sending emails. Things where it is more involved than just modifying some data in some tables.
Thank you, I definitely have some more research to do then.
February 28, 2014 at 9:00 am
JoshDBGuy (2/28/2014)
Sean Lange (2/28/2014)
JoshDBGuy (2/28/2014)
Do you have a best practice for cursors? Just don't use them at all or only in certain situations? I tend to use them for small sets of data, such as a couple hundred or a thousand rows. Otherwise I generally use while loops.Edit: Interesting. Makes me curious to see code response to this request.
A while loop can very easily perform far worse than a properly defined cursor. I would avoid either construct as a general rule. Just because your dataset is small when you write the code doesn't mean the dataset will always be small.
The basic issue is that you have to change your mindset. Instead of thinking about what you want to do to a row, you have to think about what you want to do to a column. If the body of your loop is an insert, update or similar it can be rewritten as a set based query with no loops.
Looping is very helpful for some administration types of things or sending emails. Things where it is more involved than just modifying some data in some tables.
Thank you, I definitely have some more research to do then.
If you want to see how it can be done feel free to post a cursor with the same posting specifications (ddl, etc) in a new thread. We can dismantle one of your cursors pretty quickly and show you how it can be done set based.
The first place I would start down this learning path is to examine a tally or numbers table. Our very own Jeff Moden has an excellent article that explains the concepts of this type of table and how it can be leveraged to avoid loops. This is by no means the only way to do this but it is the most common.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
_______________________________________________________________
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/
February 28, 2014 at 9:07 am
Sean Lange (2/28/2014)
You absolutely do not need a cursor for this. Cursors are horrible for performance as you obviously realize. We can help you but first you need to help us by posting ddl (create table statements), sample data (insert statements) and the desired output based on the sample data. We do not need or want a million rows but there should be enough sample data to adequately represent the real data.Please take a few minutes and read the first article in my signature for best practices when posting questions.
I hope I'm giving you what you're asking for here. If there's something I'm missing please let me know and I'll provide it. I just used my sample text table to build the insert:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#history','U') IS NOT NULL
DROP TABLE #history
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
)
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13)
Edit: I would normally provide any code I have tried, but in this case I haven't started to write anything because of the nature of my question.
February 28, 2014 at 9:40 am
pixeldude (2/28/2014)
Sean Lange (2/28/2014)
You absolutely do not need a cursor for this. Cursors are horrible for performance as you obviously realize. We can help you but first you need to help us by posting ddl (create table statements), sample data (insert statements) and the desired output based on the sample data. We do not need or want a million rows but there should be enough sample data to adequately represent the real data.Please take a few minutes and read the first article in my signature for best practices when posting questions.
I hope I'm giving you what you're asking for here. If there's something I'm missing please let me know and I'll provide it. I just used my sample text table to build the insert:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#history','U') IS NOT NULL
DROP TABLE #history
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
)
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13)
Edit: I would normally provide any code I have tried, but in this case I haven't started to write anything because of the nature of my question.
Holy cow!!! You have 7 points and posted absolutely perfect ddl and sample data!!! That is awesome!!! It takes many people hundreds of points and they still can't get it right. Your post makes working on this a million times easier. Thanks!
The only thing I am not sure of is what you are looking for as output. Any chance you can post the values of what you are expecting? I have a rough idea but am not totally sure.
_______________________________________________________________
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/
February 28, 2014 at 10:25 am
Thanks for the compliment. I appreciate the help and I'm happy to provide whatever you need/want to help me. 🙂 I didn't want to quote your last response and get lost in a quote loop. Let me know if that's not the best practice in the forums and I'll change my habits going forward.
Here's a revised code block with a sample of what I'm looking for as output:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#history','U') IS NOT NULL
DROP TABLE #history
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
)
-- insert sample data
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#results','U') IS NOT NULL
DROP TABLE #results
-- create table for sample output
CREATE TABLE #results
(
InventoryItemID INT,
LocationID INT,
DaysInLocation INT
)
-- create sample data
INSERT INTO #results (InventoryItemID, LocationID, DaysInLocation)
VALUES
(1,10,41),
(1,13,9)
SELECT * FROM #history
SELECT * FROM #results
February 28, 2014 at 10:43 am
No you don't need to continuously quote. 😀
This sounds like an islands and gaps issue. Take a look at this article and see if it helps.
http://www.sqlservercentral.com/articles/T-SQL/71550/[/url]
I am kind of swamped at work but will see if I can put some cycles into this later this afternoon. Maybe you will figure out the solution by then or somebody else will pop in.
_______________________________________________________________
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/
February 28, 2014 at 12:57 pm
Try this out:
create table inventory (
InvID int,
ActivityCode int,
MovementDate datetime,
OldLocationID int,
NewLocationID int
)
select I.InvID, I.ActivityCode, I.OldLocationID ,
sum(datediff(day,I.inDateTime, I.OutDateTime))
from (
select I.InvID, I.ActivityCode, I.OldLocationID, I.NewLocationID, I.MovementDate as inDateTime, Min(I2.MovementDate) as OutDateTime
from Inventory I
join Inventory I2
on I.InvID = I2.InvID
and I.MovementDate < I2.MovementDate
group by I.InvID, I.ActivityCode, I.OldLocationID, I.NewLocationID, I.MovementDate
--order by I.InvID, I.ActivityCode, I.MovementDate
)as I
group by I.InvID, I.ActivityCode, I.OldLocationID
February 28, 2014 at 2:26 pm
pixeldude (2/28/2014)
Thanks for the compliment. I appreciate the help and I'm happy to provide whatever you need/want to help me. 🙂 I didn't want to quote your last response and get lost in a quote loop. Let me know if that's not the best practice in the forums and I'll change my habits going forward.Here's a revised code block with a sample of what I'm looking for as output:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#history','U') IS NOT NULL
DROP TABLE #history
CREATE TABLE #history
(
InventoryItemID INT,
ActivityCodeID INT,
Date DATETIME,
OldLocationID INT NULL,
NewLocationID INT
)
-- insert sample data
INSERT INTO #history (InventoryItemID, ActivityCodeID, Date, OldLocationID, NewLocationID)
VALUES
(1,1,'2014-01-01 00:00:00.000',NULL,10),
(1,3,'2014-02-01 00:00:00.000',10,13),
(1,3,'2014-02-05 00:00:00.000',13, 10),
(1,3,'2014-02-10 00:00:00.000',10,13),
(1,3,'2014-02-15 00:00:00.000',13,10),
(1,3,'2014-02-20 00:00:00.000',10,13)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#results','U') IS NOT NULL
DROP TABLE #results
-- create table for sample output
CREATE TABLE #results
(
InventoryItemID INT,
LocationID INT,
DaysInLocation INT
)
-- create sample data
INSERT INTO #results (InventoryItemID, LocationID, DaysInLocation)
VALUES
(1,10,41),
(1,13,9)
SELECT * FROM #history
SELECT * FROM #results
Just an FYI, but the code above will fail on a subsequent run if the system is using a case sensitive collation. You should code TempDB as tempdb if you want it to run correctly on case sensitive system.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply