August 7, 2009 at 4:00 am
With many thanks to the previous forum posters, I've worked out how to implement the stop clock solution. It involves a Recursive CTE, but now I'm stuck with how to nicely wrap it into a view or even a table valued function that I can use easily.
Here is what I have so far (to make it as simple for people to help me as possible!)
First, create the tables needed.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTable]') AND type in (N'U'))
DROP TABLE [dbo].[TempTable]
create table TempTable (request_no int not null, ref int not null, StartStop varchar (5), theDate datetime)
alter table TempTable add primary key (request_no, ref)
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[StartStopView]'))
DROP VIEW [dbo].[StartStopView]
create view StartStopView as
select * from TempTable
union all
select t2.request_no, t2.ref+1, 'Stop', GetDate()
from TempTable t2
inner join (
select t1.request_no, max(t1.ref) "ref"
from TempTable t1
group by t1.request_no
) X on t2.request_no=X.request_no and X.ref=t2.ref and t2.StartStop='Start'
Now insert some test values:
insert into TempTable values (1, 0, 'Stop', '2008-01-01')
insert into TempTable values (1, 1, 'Start', '2009-01-01')
insert into TempTable values (1, 2, 'Stop', '2009-01-02')
insert into TempTable values (1, 3, 'Start', '2009-01-05')
insert into TempTable values (1, 4, 'Stop', '2009-01-05')
insert into TempTable values (1, 5, 'Start', '2009-02-04')
insert into TempTable values (1, 6, 'Stop', '2009-03-04')
insert into TempTable values (1, 7, 'Stop', '2009-03-12')
insert into TempTable values (1, 8, 'Start', '2009-03-14')
insert into TempTable values (1, 9, 'Stop', '2009-03-14')
insert into TempTable values (2, 0, 'Stop', '2008-01-01')
insert into TempTable values (2, 1, 'Start', '2009-01-01')
insert into TempTable values (2, 2, 'Stop', '2009-01-02')
insert into TempTable values (2, 3, 'Start', '2009-01-05')
insert into TempTable values (2, 4, 'Stop', '2009-01-05')
insert into TempTable values (2, 5, 'Start', '2009-02-04')
insert into TempTable values (2, 6, 'Stop', '2009-03-04')
insert into TempTable values (2, 7, 'Stop', '2009-03-12')
insert into TempTable values (2, 8, 'Start', '2009-03-14')
And now the Recursive CTE:
WITH cteStage1 (request_no, ref, status, cn) AS (
SELECT
request_no, ref,
CASE WHEN StartStop = 'Stop' THEN 1 ELSE 0 END AS status,
ROW_NUMBER() OVER (ORDER BY request_no, ref)
- ROW_NUMBER() OVER (PARTITION BY (CASE WHEN StartStop = 'Stop' THEN 1 ELSE 0 END) ORDER BY request_no, ref) AS cn
FROM StartStopView
),
cteStage2 (request_no, ref, status, theDate, rn) AS (
SELECT S.request_no, S.ref, S.status, T.theDate,
ROW_NUMBER() OVER (ORDER BY S.request_no, S.ref) AS rn
FROM StartStopView T
INNER JOIN (
SELECT request_no, MIN(ref) AS ref, status
FROM cteStage1
GROUP BY request_no, status, cn
) S ON (S.ref = T.ref and S.request_no = T.request_no)
)
select request_no, sum(X.Days) from (
SELECT
T0.request_no AS request_no,
T0.ref AS StartRef,
T0.theDate AS StartDate,
T1.ref AS StopRef,
T1.theDate AS StopDate,
DATEDIFF(day, T0.theDate, T1.theDate) AS Days
FROM cteStage2 T0
INNER JOIN cteStage2 T1 ON (T0.rn = T1.rn - 1)
WHERE (T0.status = 0 AND T1.status = 1)
) X group by request_no
So how do I wrap that into a view or a table valued function?
August 7, 2009 at 8:39 am
No! The recursive CTE method is the wrong method for this! Do you have the link back to the original problem? Recursive CTE's are just too damned slow.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2009 at 8:41 am
Jeff Moden (8/7/2009)
No! The recursive CTE method is the wrong method for this! Do you have the link back to the original problem? Recursive CTE's are just too damned slow.
Heh... never mind... I read the post, not the code. That isn't a recursive CTE.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2009 at 6:07 pm
(Walks in and hoses Jeff down with high pressure ice water hose, just prior to meltdown.)
😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 7, 2009 at 6:18 pm
I got this from http://www.sqlservercentral.com/Forums/FindPost764457.aspx
But indeed it isn't a recursive CTE! It's just two CTEs, but the second doesn't recurse on the first, it just uses it to derive a table and joins on this...
Back to rereading the query to understand it better...
I'm still a relative newbie on this sort of thing I'm afraid. I thought I understood this, evidently not very well.
So the first CTE finds the sequences I'm interested in, but the second then groups these together to only show the first row of each group.... then it does the datediff to find the time difference.
I'm beginning to realise that maybe this doesn't need a CTE.. though it makes it cleaner.
Though it would be nice to know how to wrap the CTE in a function or view.
August 7, 2009 at 6:52 pm
Bob Hovious (8/7/2009)
(Walks in and hoses Jeff down with high pressure ice water hose, just prior to meltdown.)😉
BWAA-HAA!!! Man, I probably shouldn't be posting... let's see... I'm trying to quit smoking, just passed a kidney stone, have a secondary infection from it, my S.O. just lost her job, the company I'm working for as a 1099 consultant wants to hire me but has a hiring freeze on and doesn't know if they can find any more 1099 money, my truck just started leaking oil from the rear differential, and dinner is late... I've got about one nerve left and I guess I better save it for a rainy day. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2009 at 7:33 pm
Jeff Moden (8/7/2009)
Bob Hovious (8/7/2009)
(Walks in and hoses Jeff down with high pressure ice water hose, just prior to meltdown.)😉
BWAA-HAA!!! Man, I probably shouldn't be posting... let's see... I'm trying to quit smoking, just passed a kidney stone, have a secondary infection from it, my S.O. just lost her job, the company I'm working for as a 1099 consultant wants to hire me but has a hiring freeze on and doesn't know if they can find any more 1099 money, my truck just started leaking oil from the rear differential, and dinner is late... I've got about one nerve left and I guess I better save it for a rainy day. 😛
Hey Jeff... I don't suppose you know how to wrap a CTE in a view? 🙂
P.S. sorry to hear about all your troubles 🙁
August 7, 2009 at 8:28 pm
ta.bu.shi.da.yu (8/7/2009)
Hey Jeff... I don't suppose you know how to wrap a CTE in a view? 🙂
Sure... it's easy... you've just gotta follow one of the main rules. Every column must have a name... find the comment where I made the fix in the following...
CREATE VIEW ViewFromCte AS
WITH cteStage1 (request_no, ref, status, cn) AS (
SELECT
request_no, ref,
CASE WHEN StartStop = 'Stop' THEN 1 ELSE 0 END AS status,
ROW_NUMBER() OVER (ORDER BY request_no, ref)
- ROW_NUMBER() OVER (PARTITION BY (CASE WHEN StartStop = 'Stop' THEN 1 ELSE 0 END) ORDER BY request_no, ref) AS cn
FROM StartStopView
),
cteStage2 (request_no, ref, status, theDate, rn) AS (
SELECT S.request_no, S.ref, S.status, T.theDate,
ROW_NUMBER() OVER (ORDER BY S.request_no, S.ref) AS rn
FROM StartStopView T
INNER JOIN (
SELECT request_no, MIN(ref) AS ref, status
FROM cteStage1
GROUP BY request_no, status, cn
) S ON (S.ref = T.ref and S.request_no = T.request_no)
)
select request_no, sum(X.Days) AS Days --Looky here... needed column name for sum
from (
SELECT
T0.request_no AS request_no,
T0.ref AS StartRef,
T0.theDate AS StartDate,
T1.ref AS StopRef,
T1.theDate AS StopDate,
DATEDIFF(day, T0.theDate, T1.theDate) AS Days
FROM cteStage2 T0
INNER JOIN cteStage2 T1 ON (T0.rn = T1.rn - 1)
WHERE (T0.status = 0 AND T1.status = 1)
) X group by request_no
... and thanks for making it easy with all the code in your original post. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2009 at 9:33 pm
Jeff Moden (8/7/2009)
... and thanks for making it easy with all the code in your original post. 🙂
Hey - thank you Jeff! Brilliant... now I'm all set to fix up the amazingly terrible RBAR solution our developers came up with (time pressures I think).
The RBAR consisted of a column select that used a UDF, the UDF basically used a cursor and in the cursor it called on another UDF. Ick!
You guys have helped me out heaps - I can't thank you all enough.
August 8, 2009 at 1:51 pm
Ummmm... be careful... aggregated views (views with things like SUM) in them, aren't necessarily going to be faster if you use a criteria against their result set. For example, if you want to find everything in the view that has a sum of 10 or more, it will still have to resolve everything in the view to be able to do that (provided it's a non-indexed view). Sometimes, a stored procedure or good set based inline table valued function is much, much better than a view.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply