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 2008 Forums
 Transact-SQL (2008)
 Datediff in a loop

Author  Topic 

aim22
Starting Member

7 Posts

Posted - 2015-01-29 : 07:52:30
Hello all,

I am new to SQL (functions and stored procedures at least) and I come to you for help :)
I have a table where I store a date with a corresponding status (in/out/end) for instance:

DATE/STATUS
2015-01-01 / In
2015-01-02 / Out
2015-01-03 / In
2015-01-06 / End

And I want to accomplish the following procedure:
1. Find the first "in" status and save the date.
2. Find the first "out" status and do a datediff(day,in,out).
3. Find next "in" status and save the date.
4. Find next "out" or "end" status and do datediff
5. sum the result of both datediffs

*there can be unlimited ins and outs, the loop ends at status "end".

I hope I was clear enough? Best regards.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-29 : 08:35:13
Instead of thinking in terms of doing it in a loop, try to do it in a set-based query. I have not tested the following, but something like this:
;WITH cte AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [Date]) AS RN
FROM YourTable
)
SELECT
SUM( DATEDIFF(dd,a.[Date],b.[Date]) ) AS [Total]
FROM
cte a
INNER JOIN cte b ON
a.RN = b.RN
WHERE
a.[Status] = 'In'
AND b.[Status] = 'Out';
Go to Top of Page

aim22
Starting Member

7 Posts

Posted - 2015-01-29 : 09:03:39
OK I think I got it now, thanks a lot!!
Go to Top of Page

aim22
Starting Member

7 Posts

Posted - 2015-01-29 : 10:41:43
A little follow up. This solution works great except for one thing, in the subquery
SELECT ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [Date]) AS RN FROM YourTable
the actual result rows are still in need of sorting by DATE.

I add:

SELECT ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [Date]) AS RN FROM YourTable order by Date


which by itself works fine but with the complete query I get the error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-01-29 : 11:08:46
select top 100 percent .....



Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)
Go to Top of Page

aim22
Starting Member

7 Posts

Posted - 2015-01-29 : 11:32:22
sorry! Yes I did that, forgot to update the thread, but as I found out that was not the problem. The issue is that it only works if I have only "ins" and "outs" since the partition puts them together here:

SELECT ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [Date]) AS RN FROM YourTable

Gives:
1 I 2015-01-28
1 U 2015-01-29
2 I 2015-01-31
2 U 2015-02-04

Which is correct. But since I have another type of status "end" which should be handled the same as a "out" the last column should be a 2 as well? But I get the following:
1 I 2015-01-28
1 U 2015-01-29
2 I 2015-01-31
1 O 2015-02-04
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-29 : 11:47:56
SELECT ROW_NUMBER() OVER (PARTITION BY CASE WHEN [Status] = 'In' THEN 'In' ELSE 'Out' END ORDER BY [Date]) AS RN FROM YourTable
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-29 : 13:15:41
quote:
Originally posted by aim22

A little follow up. This solution works great except for one thing, in the subquery
SELECT ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [Date]) AS RN FROM YourTable
the actual result rows are still in need of sorting by DATE.

Why do you need to sort the results of the CTE?

You cannot predictably sort it. The output of the CTE is a set, not a cursor. Elements in a set have no order to it.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-29 : 13:39:47
Add the ORDER BY to the outermost query, the one that returns results to you. Inner queries/subqueries don't pass their order out to the outer queries anyway.
Go to Top of Page
   

- Advertisement -