February 9, 2010 at 9:49 am
Hi,
being a noob at MS SQL selects i have following question:
I have a table with following rows:
Colomnnames are: Check(varchar),Date(datetime),Result(int)
1,2010-01-10,10:50:00,1
2,2010-01-10,10:50:00,1
1,2010-01-10,10:51:00,0
2,2010-01-10,10:51:00,1
1,2010-01-10,10:52:00,0
2,2010-01-10,10:52:00,0
1,2010-01-10,10:53:00,0
2,2010-01-10,10:53:00,1
1,2010-01-10,10:54:00,1
2,2010-01-10,10:55:00,0
1,2010-01-10,10:56:00,1
2,2010-01-10,10:56:00,1
1,2010-01-10,10:57:00,0
2,2010-01-10,10:57:00,0
1,2010-01-10,10:58:00,0
2,2010-01-10,10:58:00,1
- For each Check i need the first date/time where the Check has Result = 0 and the first date/time where the result was 1 again. And this for each time the result becomes 0 after being 1.
- When there is no result =1 at the last record, it needs to take that last record.
Result of above table should be:
1,2010-01-10,10:51:00,2010-01-10,10:54:00
1,2010-01-10,10:57:00,2010-01-10,10:58:00
2,2010-01-10,10:52:00,2010-01-10,10:53:00
2,2010-01-10,10:55:00,2010-01-10,10:58:00
Anyone?
Thanks in advance.
Cheers
February 11, 2010 at 7:53 am
Dear Jakke,
this is actually a kind of "Running Total" problem, where you want one row to depend on the row before.
Depending on the size of the tables, you can solve this in two ways:
* use a classic CURSOR to loop through the rows in the correct order and handle the logic in the loop.
* A "Quirky Update", which perhaps not is the standard procedure...
I tried to solve it with "Quirky Update". Please read the article at http://www.sqlservercentral.com/articles/T-SQL/68467/ for more information.
create table #T
(
[Check] varchar(1),
Datedatetime,
Resultint,
flagtinyint default (NULL),
primary key clustered ([Check], Date)
)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:50:00',1)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:50:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:51:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:51:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:52:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:52:00',0)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:53:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:53:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:54:00',1)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:55:00',0)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:56:00',1)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:56:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:57:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:57:00',0)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:58:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:58:00',1)
declare @LastResultint
set @LastResult = 1
-- Quirky update
update #T
set [Check] = [Check],
flag = @LastResult,
@LastResult = @LastResult + Result
option (maxdop 1)
update #T
set flag = flag - 1
where Result = 1
select *
from #T
order by 1, 2
select [Check], flag, min(Date), max(Date)
from #T
group by [Check], flag
having sum(case when Result = 0 then 1 else 0 end) > 0
order by [Check]
drop table #T
Hope this helps!
/Markus
February 11, 2010 at 8:54 am
Hi again,
just thought i should give the cursor solution aswell, since this is the more usual way to solve this kind of problem. It works fine if the table isn´t too big.
create table #T
(
[Check] varchar(1),
Datedatetime,
Resultint
)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:50:00',1)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:50:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:51:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:51:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:52:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:52:00',0)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:53:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:53:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:54:00',1)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:55:00',0)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:56:00',1)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:56:00',1)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:57:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:57:00',0)
insert into #T ([Check], Date, Result) values ('1','2010-01-10 10:58:00',0)
insert into #T ([Check], Date, Result) values ('2','2010-01-10 10:58:00',1)
----- Cursor solution --------
declare @Checkvarchar(1)
declare @Datedatetime
declare @resultint
declare @lastCheck varchar(1)
declare @lastResult int
declare @Startdatetime
declare @Enddatetime
declare myCursor cursor for
select [Check], Date, Result
from #T
order by [Check], Date
open myCursor
fetch next from myCursor into @Check, @Date, @Result
set @lastCheck = @Check
set @lastResult = 1
create table #Result
(
[Check]char(1),
[From]datetime,
[To]datetime
)
while (@@fetch_status = 0)
begin
if (@lastResult = 0 and @Result = 1 and @lastCheck = @Check)
begin
set @End = @Date
end
if ((@lastResult = 0 and @Result = 1) OR (@lastCheck <> @Check))
begin
insert into #Result values (@lastCheck, @Start, @End)
end
if ((@lastResult = 1 and @Result = 0) OR (@lastCheck <> @Check))
begin
set @Start = @Date
set @lastResult = 1
end
set @lastCheck = @Check
set @lastResult = @Result
set @End = @Date
fetch next from myCursor into @Check, @Date, @Result
end
close myCursor
deallocate myCursor
select *
from #Result
drop table #T
drop table #Result
/Markus
February 11, 2010 at 11:12 am
Hi Markus,
Thanks for the reply. 🙂
I will certainly test your solution.
You stated that it would work on not to big tables. Is a table with about 550000 records per month a big one? 😉
Cheers,
Jakke
February 11, 2010 at 11:54 am
Jakke (2/11/2010)
Hi Markus,Thanks for the reply. 🙂
I will certainly test your solution.
You stated that it would work on not to big tables. Is a table with about 550000 records per month a big one? 😉
Cheers,
Jakke
Don't use the cursor if you have a valid alternative solution.
February 12, 2010 at 12:31 am
Jakke (2/11/2010)
Hi Markus,Thanks for the reply. 🙂
I will certainly test your solution.
You stated that it would work on not to big tables. Is a table with about 550000 records per month a big one? 😉
Cheers,
Jakke
Hi Jakke,
well, it´s big enough to see the performance difference... just make sure to read the article so you understand the code before adapting it into your solution. And of course: test it. 😛
Best Regards,
Markus
February 12, 2010 at 10:26 am
Hi Markus
The "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it on the production database, but need to change the clustered primary key and alter some other flags... it requires a drop/create.
I ll manage it after my holydays 🙂
Thanks again for the help!
Cheers,
Jakke
February 12, 2010 at 5:39 pm
Jakke (2/12/2010)
Hi MarkusThe "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it on the production database, but need to change the clustered primary key and alter some other flags... it requires a drop/create.
I ll manage it after my holydays 🙂
Thanks again for the help!
Cheers,
Jakke
Just copy the data to a temp table... you can do just about anything you want there without having to answer to the "design gods". Done correctly it will still be a lot faster than a cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2010 at 5:43 pm
Jakke (2/12/2010)
Hi MarkusThe "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it on the production database, but need to change the clustered primary key and alter some other flags... it requires a drop/create.
I ll manage it after my holydays 🙂
Thanks again for the help!
Cheers,
Jakke
Jakke... would you post the code you finally ended up with, please? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2010 at 1:38 am
Jakke (2/12/2010)
Hi MarkusThe "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it on the production database, but need to change the clustered primary key and alter some other flags... it requires a drop/create.
I ll manage it after my holydays 🙂
Thanks again for the help!
Cheers,
Jakke
Hi Jakke,
good to hear it works fine!
I just want to draw your attention to one thing: The "quirky update" requires the clustered index to be ordered in the same order as you want to handle the rows.
So if you need another clustered index on the table than the one required by the "Quirky update", you probably need a temporary table for the "Quirky update", on which you can have the clustered index needed...
/Markus
February 22, 2010 at 5:23 am
Hi Marcus,
I was under that impression yes 😉
Thanks for the feedback.
Jakke
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply