how to do this without a cursor?

Question

how to do this without a cursor?

vmon

SSChasing Mays

Points: 624
More actions
August 11, 2010 at 9:17 am

#226009

I have table with OrderId, LineId, FirstDate, LastDate, DaysBetween. The order and line are the key.
I need to get the number of days between LastDate and FirstDate from different records using the OrderId and LineId of next record. So for the records below the DaysBetween would be calculated.
Order1, Line1, 1/1/2010, 1/13/2010, 0
Order1, Line2, 1/30/2010, 2/15/2010, 17
Order1, Line3, 2/15/2010, 2/17/2010, 2
Order2, Line1, 1/1/2010, 1/5/2010, 4
Order2, Line2, 1/5/2010, 1/10/2010, 0
Can I do this without using a cursor? There are a LOT of records in the table and I am having trouble getting processing time to be acceptable.
Thanks,
John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

You know John, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

I second Wayne and will go one step further:

Please describe what you define as "DaysBetween": calendar days, working days, business days or something totally different...

If it's neither calendar (7 days per week) nor working (5 days a week Mon through Fri) days then you also need to provide the business logic to determine a valid date (most probably there is a calendar table with a business day flag or something like that).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

drew.allen SSC Guru Points: 76776 More actions · Answer 3

I'll go one step further. Your OrderID and LineID should either be int (highly recommended) or at least padded with zeros to get a consistent length. Otherwise when you sort your records by the OrderID it's going to be an alphabetic sort, so you're going to get

Order1

Order10

Order100

.

Order11

Order110

.

Order2

Order20

I don't think this is what you want.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

Order1, Line1, 1/1/2010, 1/13/2010, 0 --why 0?

Order1, Line2, 1/30/2010, 2/15/2010, 17 --1/30 - 1/13?

Order1, Line3, 2/15/2010, 2/17/2010, 2 --?? why not 0 for 2/15 - 2/15

Order2, Line1, 1/1/2010, 1/5/2010, 4 --???? how did you get 4 ????

Order2, Line2, 1/5/2010, 1/10/2010, 0 --1/5 - 1/5?

Scott Pletcher, SQL Server MVP 2008-2010

LutzM SSC Guru Points: 107049 More actions · Answer 5

Four people answering your post all have different, but valid, questions.

I strongly recommend you follow Waynes advice and read the article he pointed you at (which is the same article I referenced in my signature, too.... Coincidence? NO!).

Once you've read the article follow the given advice and post ready to use table def, sample data and expected results.

Once you're done with that, take the time to answer the remaining questions.

Otherwise you'll get even more questions than an answer.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]