| 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/STATUS2015-01-01 / In 2015-01-02 / Out2015-01-03 / In2015-01-06 / EndAnd 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 datediff5. 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.RNWHERE a.[Status] = 'In' AND b.[Status] = 'Out'; |
 |
|
|
aim22
Starting Member
7 Posts |
Posted - 2015-01-29 : 09:03:39
|
| OK I think I got it now, thanks a lot!! |
|
|
|
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. |
|
|
|
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) |
|
|
|
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 |
|
|
|
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 |
|
|
|
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. |
|
|
|
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. |
|
|
|
|
Datediff in a loop