SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help finding an old post


Help finding an old post

Author
Message
sqldriver
sqldriver
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2134 Visits: 2536
Hi,

A while back I recall reading a post on here where a user needed to update a column with values until there was a new value, then update with that new value until it hit the next new value, and so on.

There were some really excellent solutions, and now that I'm facing a similar problem, I'd really like to review them. But of course I can't find it.

I know it's sort of a gaps and islands problem generally.

Thanks
sqldriver
sqldriver
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2134 Visits: 2536
I'll take that as a no Hehe

Anyway, here's what I'm generally trying to do:


CREATE TABLE testsql (
cid integer,
ctime datetime,
cgm integer);

INSERT INTO testsql VALUES
('1', '2012-01-01 07:00', '1'),
('1', '2012-01-01 07:01', '1'),
('1', '2012-01-01 07:02', '1'),
('1', '2012-01-01 07:03', NULL),
('1', '2012-01-01 07:04', NULL),
('1', '2012-01-01 07:05', '5'),
('1', '2012-01-01 07:06', '5'),
('1', '2012-01-01 07:07', '5'),
('1', '2012-01-01 07:08', NULL),
('1', '2012-01-01 07:09', NULL),
('1', '2012-01-01 07:10', NULL),
('1', '2012-01-01 07:11', NULL),
('2', '2013-05-02 07:12', '24'),
('2', '2013-05-02 07:13', '24'),
('2', '2013-05-02 07:14', '24'),
('2', '2013-05-02 07:15', NULL),
('2', '2013-05-02 07:16', NULL),
('2', '2013-05-02 07:17', '44'),
('2', '2013-05-02 07:18', '44'),
('2', '2013-05-02 07:19', NULL),
('2', '2013-05-02 07:20', '4'),
('2', '2013-05-02 07:21', '4'),
('2', '2013-05-02 07:22', '4');



And here's the query


WITH C (id, pt, gm, dr) as (
SELECT cid, ctime, cgm,
dense_rank() over (partition by cid, cgm order by cid, ctime) as dr
FROM testsql
)
SELECT id, pt, gm,
CASE WHEN gm IS NULL THEN
LAG(gm, cast(c.DR as int)) OVER (PARTITION BY id ORDER BY id, pt)
ELSE gm END as gm2
FROM C
ORDER BY id, pt, gm



There's one result towards the end that is still NULL, rather than being filled in with a LAG value, like the rest in the CASE column are.

Any suggestions?
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15397 Visits: 25280
Could this be the article:

Group Islands of Contiguous Dates (SQL Spackle)
By Jeff Moden, 2013/10/04 (first published: 2010/12/07)

http://www.sqlservercentral.com/articles/T-SQL/71550/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Michael Meierruth
Michael Meierruth
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2300 Visits: 2516
sqldriver,
Could your data also contain a sequence like the following, i.e. cgm changes without one or more rows having cgm null in between:


('1', '2012-01-01 07:05', '5'),
('1', '2012-01-01 07:06', '5'),
('1', '2012-01-01 07:07', '5'),
('2', '2013-05-02 07:10', '24'),
('2', '2013-05-02 07:20', '24'),
('2', '2013-05-02 07:30', '24')



Could there be gaps in the ctime values (as shown above)?
What version of SQL Server must this run under?
sqldriver
sqldriver
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2134 Visits: 2536
bitbucket-25253 (1/30/2014)
Could this be the article:

Group Islands of Contiguous Dates (SQL Spackle)
By Jeff Moden, 2013/10/04 (first published: 2010/12/07)

http://www.sqlservercentral.com/articles/T-SQL/71550/


That's a fine article, but this was a forum post. There were a number of pages of replies, but maybe it only seemed memorable to me!
sqldriver
sqldriver
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2134 Visits: 2536
Michael Meierruth (1/31/2014)
sqldriver,
Could your data also contain a sequence like the following, i.e. cgm changes without one or more rows having cgm null in between:


('1', '2012-01-01 07:05', '5'),
('1', '2012-01-01 07:06', '5'),
('1', '2012-01-01 07:07', '5'),
('2', '2013-05-02 07:10', '24'),
('2', '2013-05-02 07:20', '24'),
('2', '2013-05-02 07:30', '24')



Could there be gaps in the ctime values (as shown above)?
What version of SQL Server must this run under?


Hi,

No, the issue with the data is that there are NULL gaps in the 'check in' column all the way through. I haven't seen a contiguous block. It will be running on 2012.

Thanks
Michael Meierruth
Michael Meierruth
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2300 Visits: 2516
OK, just saw your use of LAG which means SS20012.
But you're making completely wrong use of it.
If you delete one of the rows with cgm='5' from your data and run your script the results become a lot worse.
In your final select display the dr column and you'll see this right away.
Also, no dense_rank needed here. Rank is sufficient.
sqldriver
sqldriver
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2134 Visits: 2536
Michael Meierruth (1/31/2014)
OK, just saw your use of LAG which means SS20012.
But you're making completely wrong use of it.
If you delete one of the rows with cgm='5' from your data and run your script the results become a lot worse.
In your final select display the dr column and you'll see this right away.
Also, no dense_rank needed here. Rank is sufficient.


It's not completely wrong - it's getting me all the test data except one row how I want it. Do you have another suggestion?

Switching from dense rank to rank doesn't change output, so I'm not sure what you mean.

Thanks
Michael Meierruth
Michael Meierruth
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2300 Visits: 2516
sqldriver,

You didn't understand my remark regarding removing one of the rows with cgm='5'.
If you do that your solution will now show even more incorrect results.

I understand what you are trying to do but unfortunately the rank values being generated make your approach invalid.
I can't see a way a to generate the rank values the way you would like them.

Thus here is my solution which takes a more classic approach in dealing with arbitrarily embedded groups.
It also will handle some strange cases.
Thus a more generic problem definition is:
within every cid group, update every group of nulls with the cgm value of the row immediately before the null group (if it exists)

Here is the script:


with

cte1 as -- change cgm nulls to 0 to avoid confusion with other types of nulls later on
(
select cid,ctime,isnull(cgm,0) cgm
from testsql
),

cte2 as -- find cid,cgm values before and after each row (-1 is returned for the first and last row)
(
select cid,ctime,cgm,
lag(cgm,1,-1) over (order by ctime) cgmbef,lead(cgm,1,-1) over (order by ctime) cgmaft,
lag(cid,1,-1) over (order by ctime) cidbef,lead(cid,1,-1) over (order by ctime) cidaft
from cte1
),

cte3 as -- find first and last element of each null group
(
select cid,ctime,cgmbef,cgmaft,
case when cid<>cidbef or cgm<>cgmbef then 1 else 0 end isfirst,
case when cid<>cidaft or cgm<>cgmaft then 1 else 0 end islast
from cte2
where cgm=0
),

cte4 as -- extract only the beginning of each group
(
select cid,ctime,cgmbef,rank() over(partition by cid order by ctime) nullgroupid
from cte3
where isfirst=1
),

cte5 as -- extract only then end of each group
(
select cid,ctime,cgmbef,rank() over(partition by cid order by ctime) nullgroupid
from cte3
where islast=1
)

-- merge the two
select a.cid,a.ctime starttime,b.ctime endtime,a.cgmbef
from cte4 a
join cte5 b on a.cid=b.cid and a.nullgroupid=b.nullgroupid
where a.cgmbef>0



And instead of the final select you could do an update (which accepts the preceeding with block):


update t
set t.cgm=u.cgmbef
from testsql t
join
(
select a.cid,a.ctime starttime,b.ctime endtime,a.cgmbef
from cte4 a
join cte5 b on a.cid=b.cid and a.nullgroupid=b.nullgroupid
where a.cgmbef>0
) u on t.cid=u.cid and t.ctime>=starttime and t.ctime<=endtime


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210283 Visits: 41973
sqldriver (1/30/2014)
Hi,

A while back I recall reading a post on here where a user needed to update a column with values until there was a new value, then update with that new value until it hit the next new value, and so on.

There were some really excellent solutions, and now that I'm facing a similar problem, I'd really like to review them. But of course I can't find it.

I know it's sort of a gaps and islands problem generally.

Thanks


Was this it?
http://www.sqlservercentral.com/Forums/Topic1529984-8-1.aspx

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search