Cursor for DateDiff?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cursor for DateDiff??

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2010-09-16 : 12:54:01

Hello

I'm trying to figure out how to get the DateDiff for each of the groups below (by color). I would need to get the datediff for each group and then select the max DateDiff for the ticket. The data appears like the below example

ticket | ID | Type | Date
1234 | 1 | 0 | 09/03/10
1234 | 2 | 1 | 09/04/10
1234 | 3 | 0 | 09/07/10
1234 | 4 | 1 | 09/10/10
1234 | 5 | 0 | 09/11/10
1234 | 6 | 1 | 09/16/10

I appreciate any help provided
Thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 13:10:38

[code]
SELECT t.*,DATEDIFF(dd,t1.Date,t.Date) AS Days
FROM Table t
CROSS APPLY (SELECT TOP 1 Date
FROM Table
WHERE ticket=t.ticket
AND ID < t.ID
AND Type=0
ORDER BY ID DESC)t1
WHERE t.Type=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2010-09-16 : 13:52:31

Thank you very much!! Success :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:03:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-17 : 12:17:48

The data model for histories is to have a start and end date in the table. What you have is an example of a design flaw called attribute splitting.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources