InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:29:09 GMT20http://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]CELKO (12/10/2012)[/b][hr][quote].. why true portability is a myth and I actually hope it always is. If all makes of SQL engines had identical capabilities, there would be no competition to improve. Many former proprietary enhancements have made their way into ISO/ANSI standards and that's a good thing to continue.[/quote]Again, I have earned a living writing portable SQL for decades. It has been a good paying myth for me :-DMost proprietary features do not make it. The beauty of SQL is that it is abstract. The same SQL will run on a hand-held device, a mainframe, hashed tables, columnar data stores, ISAM and any other architectures. The improvements come not in the SQL language, but in the optimizer and storage engines. The idea of that abstraction was the code could stay the same while the hardware changes.[/quote]Heh... throw in just one variable and portability goes to hell in a handbasket. ;-)Mon, 10 Dec 2012 11:50:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote].. why true portability is a myth and I actually hope it always is. If all makes of SQL engines had identical capabilities, there would be no competition to improve. Many former proprietary enhancements have made their way into ISO/ANSI standards and that's a good thing to continue.[/quote]Again, I have earned a living writing portable SQL for decades. It has been a good paying myth for me :-DMost proprietary features do not make it. The beauty of SQL is that it is abstract. The same SQL will run on a hand-held device, a mainframe, hashed tables, columnar data stores, ISAM and any other architectures. The improvements come not in the SQL language, but in the optimizer and storage engines. The idea of that abstraction was the code could stay the same while the hardware changes.Mon, 10 Dec 2012 09:36:35 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]CELKO (12/9/2012)[/b][hr][quote] Regarding your ANSI/ISO INSERT INTO comment, unless I'm mistaken, your suggestion only works on 2008 or higher. The DATE data type is also not a valid 2005 data type. Most people here are probably on 2012 at least for testing purposes, but I wanted to make it as generic as possible. [/quote] Yep, but the DML is valid back to SQL-92 Standards.[/quote]Not your fault but that DML is still just as useless in all versions up to and not icluding SQL Server 2012, as it was back then. This is also one of the many reasons why true portability is a myth and I actually hope it always is. If all makes of SQL engines had identical capabilities, there would be no competition to improve. Many former proprietary enchancements have made their way into ISO/ANSI standards and that's a good thing to continue.Sun, 09 Dec 2012 16:16:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote] Regarding your ANSI/ISO INSERT INTO comment, unless I'm mistaken, your suggestion only works on 2008 or higher. The DATE data type is also not a valid 2005 data type. Most people here are probably on 2012 at least for testing purposes, but I wanted to make it as generic as possible. [/quote] Yep, but the DML is valid back to SQL-92 Standards.Sun, 09 Dec 2012 11:09:29 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxModified my code to update the table:[code="sql"]CREATE TABLE dbo.UpdateGaps( MachineID int NOT NULL, RecordedDate datetime NOT NULL, Pressure float NULL, CONSTRAINT PK_UpdateGaps PRIMARY KEY CLUSTERED (MachineID,RecordedDate))INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-02', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-03', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-04', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-05', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-06', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-07', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-08', 330);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-09', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-10', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-02', 380);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-03', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-04', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-05', 350);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-06', 350);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-07', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-08', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-09', 400);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-10', 0);GOselect * from dbo.UpdateGaps;gowith Base0Data as (select MachineID, RecordedDate, Pressure, rn = row_number() over (partition by MachineID, case when Pressure = 0 then 0 else 1 end order by RecordedDate)from dbo.UpdateGaps),BaseData as (select MachineID, RecordedDate, Pressure, GrpDate = dateadd(dd,-rn,RecordedDate), rn, rn1 = row_number() over (partition by MachineID, dateadd(dd,-rn,RecordedDate) order by dateadd(dd,-rn,RecordedDate))from Base0Data)--select * from BaseData where Pressure = 0--select-- bd1.MachineID,-- bd1.RecordedDate,-- Pressure = case when bd1.Pressure = 0 then coalesce(bd2.Pressure, bd1.Pressure) else bd1.Pressure endupdate up set Pressure = case when bd1.Pressure = 0 then coalesce(bd2.Pressure, bd1.Pressure) else bd1.Pressure endfrom dbo.UpdateGaps up inner join BaseData bd1 on up.MachineID = bd1.MachineID and up.RecordedDate = bd1.RecordedDate left outer join BaseData bd2 on (bd1.MachineID = bd2.MachineID and dateadd(dd, -bd1.rn1, bd1.RecordedDate) = bd2.RecordedDate);goselect * from dbo.UpdateGaps;godrop table dbo.UpdateGaps;go[/code]Sun, 09 Dec 2012 00:34:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]Sam S Kolli (12/6/2012)[/b][hr]Let me say before hand that Jeff Moden's solution is better than this for the specific task you asked for; (on my laptop, my solution is 85% of the cost, whereas jeff's solution is 15% of the cost) [/quote]Oh... we have to be careful here, Sam. My query is based on a "Triangular Join" and it relies on just the right index or it can be hundreds and, sometimes, thousands of times slower and more resource intensive than a While Loop. It turns out that this solution worked perfectly because of the indexing and data available in the columns but such is not always the case.Also, a word of caution. The costs you find in execution plans, especially the % of batch cost are fickle lovers and they can lie like you wouldn't believe. While such costs are helpful in finding potential problems, they should never be used to make the "final decision". For example, here's some very simple code to build 30 years worth of dates using two different methods. The details, of course, are in the comments in the code.[code="sql"]/**************************************************************************************** Purpose: This code demonstrates that the estimated and actual execution plans in SQL Server can be 100% INCORRECT and that the execution plan should only be relied on to provide hints as to what may be wrong with a query rather than an absolute indication. This code runs in SQL Server 2005 only. The code creates 30 years worth of dates starting with 2000-01-01 using two different methods. The first method uses a recursive CTE and the second method uses a "Tally" table. The output of each method is directed to a "throw-away" variable to take display delays out of the picture. Please check both the actual and estimated execution plans and compare the % of batch. Please see the following article on how to build a Tally table and how they can be used to replace certain While Loops. http://www.sqlservercentral.com/articles/T-SQL/62867/****************************************************************************************/SET NOCOUNT ON--=======================================================================================-- Recursive method shown by (Name with-held)--======================================================================================= PRINT '========== Recursive method =========='--===== Turn on some performance counters =============================================== SET STATISTICS IO ON SET STATISTICS TIME ONDECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.--===== Execute the code being tested ===================================================DECLARE @DateVal DATETIME SET @DateVal = '2000-01-01';with mycte as ( select @DateVal AS DateVal union all select DateVal + 1 from mycte where DateVal + 1 < DATEADD(yy, 30, @DateVal) )select @BitBucket = d.datevalfrom mycte dOPTION (MAXRECURSION 0)--===== Turn off the performance counters and print a separator ========================= SET STATISTICS TIME OFF SET STATISTICS IO OFF PRINT REPLICATE('=',90)GO--=======================================================================================-- Tally table method by Jeff Moden--======================================================================================= PRINT '========== Tally table method =========='--===== Turn on some performance counters =============================================== SET STATISTICS IO ON SET STATISTICS TIME ONDECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.--===== Execute the code being tested ===================================================DECLARE @StartDate AS DATETIME SET @StartDate = '2000-01-01'SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,30,@StartDate))) @BitBucket = @StartDate-1+t.N FROM dbo.Tally t ORDER BY N--===== Turn off the performance counters and print a separator ========================= SET STATISTICS TIME OFF SET STATISTICS IO OFF PRINT REPLICATE('=',90)GO[/code]Here are the execution plans for the code. Notice that the % of batch is 0% for the Recursive CTE and its 100% for the Tally Table method. One would look at this and insist that Recursive CTEs are much better than the Tally Table method for doing such a thing.[img]http://www.sqlservercentral.com/Forums/Attachment12821.aspx[/img]But, if we look at the performance of the code, we can see that the % of Batch is 100% incorrect. The Tally Table code is actually MUCH faster than the Recursive CTE.[code="plain"]========== Recursive method ==========SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'Worktable'. Scan count 2, logical reads 65749, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 391 ms, elapsed time = 449 ms.==================================================================================================== Tally table method ==========SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'Tally'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 31 ms, elapsed time = 27 ms.==========================================================================================[/code]Try your code and measure it for performance. Also, be careful about using SET STATISTICS. It can also lie (not in this case, though). See the following article about how SET STATISTICS can lie about performance. [url]http://www.sqlservercentral.com/articles/T-SQL/91724/[/url]Sat, 08 Dec 2012 21:13:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]CELKO (12/7/2012)[/b][hr]>> Here's the SQL to create the table and populate it with data. <<Do you really want to do FLOAT? How will you handle the rounding errors and slowness of floating point math without special hardware? The data element names are vague; pressure of what? NULL is the right choice for a missing reading because zero is a real value for reading on a busted meter, an empty pipe or whatever. I will guess that two decimal is good enough, rather than 1.79E+308 you have now. You also need to start using the ANSI/ISO Standard INSERT INTO syntax.[/quote]This is an inherited database, and I am not allowed to change the schema except to add new objects. There are many things I would LOVE to change. The zero values are a result of a bulk load, and not real-time pressure values. The column names are not the actual schema column names, and I don't think it matters for what I was needing help with. The pressure column does allow for NULL values, however, there is a default value of 0. I assume when they loaded the data, they must not have explicitly populated that column.Regarding your ANSI/ISO INSERT INTO comment, unless I'm mistaken, your suggestion only works on 2008 or higher. The DATE data type is also not a valid 2005 data type. Most people here are probably on 2012 at least for testing purposes, but I wanted to make it as generic as possible.Thanks for your suggestions.Fri, 07 Dec 2012 21:15:52 GMToralinquehttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]Cadavre (12/7/2012)[/b][hr][quote][b]Jeff Moden (12/6/2012)[/b][hr]I don't know what others call this type of problem but I call them "data smears" because it's like you smearing strips of color down from one wet paint spot to another.[/quote]I thought I was the only one that referred to this as a smear or smudge (I've used both to describe this type of solution in the past :-) ). Now you have me wondering if I picked up the term from you![/quote]Dunno... I've been using the term on these forums for a long time. Might simply be that great minds think alike. :-)Fri, 07 Dec 2012 12:14:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx>> Here's the SQL to create the table and populate it with data. <<Do you really want to do FLOAT? How will you handle the rounding errors and slowness of floating point math without special hardware? The data element names are vague; pressure of what? NULL is the right choice for a missing reading because zero is a real value for reading on a busted meter, an empty pipe or whatever. I will guess that two decimal is good enough, rather than 1.79E+308 you have now. You also need to start using the ANSI/ISO Standard INSERT INTO syntax. Here is my re-write: DROP TABLE Machine_Readings;CREATE TABLE Machine_Readings(machine_id INTEGER NOT NULL, sample_date DATE NOT NULL, PRIMARY KEY (machine_id, sample_date), something_pressure DECIMAL (8,2) DEFAULT 0 CHECK (something_pressure >= 0.00));INSERT INTO Machine_ReadingsVALUES (60, '2012-10-02', 0.00), (60, '2012-10-03', 300.00), (60, '2012-10-04', 300.00), (60, '2012-10-05', NULL), (60, '2012-10-06', NULL), (60, '2012-10-07', NULL), (60, '2012-10-08', 330.00), (60, '2012-10-09', NULL), (60, '2012-10-10', 300.00), (97, '2012-10-02', 380.00), (97, '2012-10-03', NULL), (97, '2012-10-04', NULL), (97, '2012-10-05', 350.00), (97, '2012-10-06', 350.00), (97, '2012-10-07', NULL), (97, '2012-10-08', NULL), (97, '2012-10-09', 400.00), (97, '2012-10-10', NULL); >> What I need to do is fill in the zero values with the value that precedes it (grouping by machine_id). Non-zero values do not change. <<SELECT MR1.machine_id, MR1.sample_date, COALESCE (MR1.something_pressure, (SELECT MAX (something_pressure) FROM Machine_Readings AS MR2 WHERE MR1.machine_id = MR2.machine_id AND MR2.sample_date <= MR1.sample_date AND MR2.something_pressure IS NOT NULL)) AS something_pressure FROM Machine_Readings AS MR1;The idea is that we look at each row. If its pressure reading IS NOT NULL, then use for this row. If its pressure reading IS NULL, then use a scalar subquery to find the most recent prior pressure reading for this machine at a row where its pressure reading IS NOT NULL. I did another version of this recently, but I cannot find it right now. Fri, 07 Dec 2012 11:35:33 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]Jeff Moden (12/6/2012)[/b][hr][quote][b]dwain.c (12/6/2012)[/b][hr][quote][b]Jeff Moden (12/6/2012)[/b][hr]I don't believe the following won't be quite as fast as a Quirky Update...[/quote]Easy for you to say...[/quote]Heh... suffering from a NEC (Not Enough Coffee) problem on that one, for sure. :-P I meant to say that "I don't believe that the following will be quite as fast as a Quirky Update" but got my tangle all toungled up. :hehe:[/quote]Ha! I don't care how you speak as long as you're fluent in T-SQL. :laugh:Thanks to everyone who replied. I've added a few new tools to my toolbox.Fri, 07 Dec 2012 08:42:52 GMToralinquehttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]Jeff Moden (12/6/2012)[/b][hr]I don't know what others call this type of problem but I call them "data smears" because it's like you smearing strips of color down from one wet paint spot to another.[/quote]I thought I was the only one that referred to this as a smear or smudge (I've used both to describe this type of solution in the past :-) ). Now you have me wondering if I picked up the term from you!Fri, 07 Dec 2012 02:12:10 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]Jeff Moden (12/6/2012)[/b][hr][quote][b]oralinque (12/6/2012)[/b][hr]dwain.c & Jeff Moden, your solutions worked great! Thank you both so much!Jeff - "Data smears" sounds good to me. Regarding the "clear requirements and readily consumable data" comment, I think it's just common courtesy. I mean, if you're asking people to help you, why not make it easier for them to help you.You two were quick too! Thanks again![/quote]The "quiick" comes from you making it so easy. Thank you again for your comments. I wish everyone would figure that out.[/quote]+1 to that!Fri, 07 Dec 2012 00:29:59 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]oralinque (12/6/2012)[/b][hr]dwain.c & Jeff Moden, your solutions worked great! Thank you both so much!Jeff - "Data smears" sounds good to me. Regarding the "clear requirements and readily consumable data" comment, I think it's just common courtesy. I mean, if you're asking people to help you, why not make it easier for them to help you.You two were quick too! Thanks again![/quote]The "quiick" comes from you making it so easy. Thank you again for your comments. I wish everyone would figure that out.Thu, 06 Dec 2012 23:27:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]dwain.c (12/6/2012)[/b][hr][quote][b]Jeff Moden (12/6/2012)[/b][hr]I don't believe the following won't be quite as fast as a Quirky Update...[/quote]Easy for you to say...[/quote]Heh... suffering from a NEC (Not Enough Coffee) problem on that one, for sure. :-P I meant to say that "I don't believe that the following will be quite as fast as a Quirky Update" but got my tangle all toungled up. :hehe:Thu, 06 Dec 2012 23:23:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxHere is another solution to the problem:[code="sql"]CREATE TABLE dbo.UpdateGaps( MachineID int NOT NULL, RecordedDate datetime NOT NULL, Pressure float NULL, CONSTRAINT PK_UpdateGaps PRIMARY KEY CLUSTERED (MachineID,RecordedDate))INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-02', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-03', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-04', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-05', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-06', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-07', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-08', 330);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-09', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-10', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-02', 380);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-03', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-04', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-05', 350);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-06', 350);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-07', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-08', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-09', 400);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-10', 0);GOwith Base0Data as (select MachineID, RecordedDate, Pressure, rn = row_number() over (partition by MachineID, case when Pressure = 0 then 0 else 1 end order by RecordedDate)from dbo.UpdateGaps),BaseData as (select MachineID, RecordedDate, Pressure, GrpDate = dateadd(dd,-rn,RecordedDate), rn, rn1 = row_number() over (partition by MachineID, dateadd(dd,-rn,RecordedDate) order by dateadd(dd,-rn,RecordedDate))from Base0Data)--select * from BaseData where Pressure = 0select bd1.MachineID, bd1.RecordedDate, Pressure = case when bd1.Pressure = 0 then coalesce(bd2.Pressure, bd1.Pressure) else bd1.Pressure endfrom BaseData bd1 left outer join BaseData bd2 on (bd1.MachineID = bd2.MachineID and dateadd(dd, -bd1.rn1, bd1.RecordedDate) = bd2.RecordedDate)order by bd1.MachineID, bd1.RecordedDate;godrop table dbo.UpdateGaps;go[/code]Thu, 06 Dec 2012 22:44:40 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxHere is another possibility. Let me say before hand that Jeff Moden's solution is better than this for the specific task you asked for; (on my laptop, my solution is 85% of the cost, whereas jeff's solution is 15% of the cost) But in case you want to assign sequential ids to each group and sequential ids to rows within a group, you can consider this approach; With this approach, you can answer, for example, what is the average pressure of the 15th group after 10/24/2012.[code="sql"]; WITH R (MachineId, Pressure, RecordedDate, Rid) AS(SELECT MachineId, Pressure, RecordedDate, ROW_NUMBER() OVER(PARTITION BY MachineId ORDER BY MachineId, RecordedDate) AS Rid FROM dbo.UpdateGaps),GRPROW(MachineId, Pressure, RecordedDate, GroupId, RowId) AS(SELECT G1.MachineId, G1.Pressure, G1.RecordedDate, DENSE_RANK() OVER (PARTITION BY G1.MachineId ORDER BY G1.MachineId, ISNULL(MIN(G2.Rid) - 1, G1.Rid)) AS GroupId, ROW_NUMBER() OVER (PARTITION BY G1.MachineId, ISNULL(MIN(G2.Rid) - 1, G1.Rid) ORDER BY G1.MachineId, ISNULL(MIN(G2.Rid) - 1, G1.Rid), G1.RecordedDate DESC) AS RowId FROM R G1 LEFT JOIN R G2 ON (G1.MachineID = G2.MachineID AND G2.Rid > G1.Rid AND G1.Pressure <> G2.Pressure) GROUP BY G1.MachineId, G1.Pressure, G1.RecordedDate, G1.Rid)SELECT G1.MachineId, G1.Pressure, G1.RecordedDate, G1.GroupId, G1.RowId AS DescendingRowId, CASE WHEN ISNULL(G2.Pressure, 0) = 0 THEN G1.Pressure ELSE G2.Pressure END AS NewPressure FROM GRPROW G1 LEFT JOIN GRPROW G2 ON (G1.MachineId = G2.MachineId AND G1.GroupId - 1 = G2.GroupId AND G2.RowId = 1) ORDER BY G1.MachineId, G1.RecordedDate[/code]Thu, 06 Dec 2012 21:56:15 GMTSam S Kollihttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxYou're welcome!I note that I neglected to mention mine only works because you happened to already have the clustered index needed to order the QU.Thu, 06 Dec 2012 20:45:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxdwain.c & Jeff Moden, your solutions worked great! Thank you both so much!Jeff - "Data smears" sounds good to me. Regarding the "clear requirements and readily consumable data" comment, I think it's just common courtesy. I mean, if you're asking people to help you, why not make it easier for them to help you.You two were quick too! Thanks again!Thu, 06 Dec 2012 20:33:57 GMToralinquehttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxwith updatebase_cte (MachineID,RecordedDate,Pressure,ind) as ( select s.MachineID,s.RecordedDate,s.Pressure, 0 as ind from yourtable s where s.RecordedDate='2012-10-02' union all select s.MachineID,s.RecordedDate, case when s.Pressure = 0 and sc.Pressure <> 0 then sc.Pressure else s.Pressure end as Pressure, case when s.Pressure = 0 and sc.Pressure <> 0 then 1 else 0 end as ind from yourtable s inner join updatebase_cte sc on (s.RecordedDate = dateadd(day,1,sc.RecordedDate) and s.MachineID=sc.MachineID) )-- this will return data need be updated part, you can use it to update by a join.select * from updatebase_cte where ind=1Just a quick coding, have not got time to test. Does this work?:-)Thu, 06 Dec 2012 18:45:38 GMTbj_shenglonghttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspx[quote][b]Jeff Moden (12/6/2012)[/b][hr]I don't believe the following won't be quite as fast as a Quirky Update...[/quote]Easy for you to say...Thu, 06 Dec 2012 18:44:30 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxI don't know what others call this type of problem but I call them "data smears" because it's like you smearing strips of color down from one wet paint spot to another.I don't believe the following will be quite as fast as a Quirky Update, but it'll blow the doors off a cursor especially in the face of the correct index. And thank you VERY much for posting such clear requirements and readily consumable data![code="sql"] UPDATE ug SET Pressure = ca.Pressure FROM dbo.UpdateGaps ug CROSS APPLY (SELECT TOP 1 Pressure FROM dbo.UpdateGaps ugca WHERE ugca.MachineID = ug.MachineID AND ugca.RecordedDate <= ug.RecordedDate AND ugca.Pressure > 0 ORDER BY ugca.MachineID,ugca.RecordedDate DESC) ca;[/code]Thu, 06 Dec 2012 18:34:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxYou can try a quirky update (QU).[code="sql"]DECLARE @MachineID INT = 0 ,@Pressure FLOAT = 0UPDATE u WITH(TABLOCKX)SET Pressure = CASE WHEN Pressure = 0 THEN @Pressure ELSE Pressure END ,@Pressure = CASE WHEN Pressure = 0 AND @MachineID = MachineID THEN @Pressure ELSE Pressure END ,@MachineID = MachineIDFROM dbo.UpdateGaps uOPTION(MAXDOP 1)[/code][b]Edit:[/b] Ooops! Initially forgot to account for change in Machine ID.Thu, 06 Dec 2012 17:50:30 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1393805-392-1.aspxI'm trying to update gaps in a table using a set based approach, but have been struggling with the solution. I've read a few gaps and islands posts in this forum, and was hopeful that I could figure it out, but I'm at a loss. Here's the sql to create the table and populate it with data.[code="sql"]CREATE TABLE dbo.UpdateGaps( MachineID int NOT NULL, RecordedDate datetime NOT NULL, Pressure float NULL, CONSTRAINT PK_UpdateGaps PRIMARY KEY CLUSTERED (MachineID,RecordedDate))INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-02', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-03', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-04', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-05', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-06', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-07', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-08', 330);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-09', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (60, '2012-10-10', 300);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-02', 380);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-03', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-04', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-05', 350);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-06', 350);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-07', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-08', 0);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-09', 400);INSERT dbo.UpdateGaps (MachineID, RecordedDate, Pressure) VALUES (97, '2012-10-10', 0);GO[/code]What I need to do is fill in the zero values with the value that precedes it (grouping by MachineID). Non-zero values do not change.[code]MachineID RecordedDate Pressure--------- ------------ --------60 2012-10-02 0 <-- no change (nothing precedes this record)60 2012-10-03 300 <-- no change60 2012-10-04 300 <-- no change60 2012-10-05 0 <-- update to 30060 2012-10-06 0 <-- update to 30060 2012-10-07 0 <-- update to 30060 2012-10-08 330 <-- no change60 2012-10-09 0 <-- update to 33060 2012-10-10 300 <-- no change97 2012-10-02 380 <-- no change (new MachineID)97 2012-10-03 0 <-- update to 38097 2012-10-04 0 <-- update to 38097 2012-10-05 350 <-- no change97 2012-10-06 350 <-- no change97 2012-10-07 0 <-- update to 35097 2012-10-08 0 <-- update to 35097 2012-10-09 400 <-- no change97 2012-10-10 0 <-- update to 400[/code]I read this yesterday, [url]http://www.manning.com/nielsen/nielsenMEAP_freeCh5.pdf[/url], which gave me hope, but I still can't figure out how to update the table without using a cursor. Using the information in the PDF, I created the query below which gives a date range for the gaps. This is where I was hopeful that I could figure it out, but I still keep falling back to a cursor based approach.[code="sql"]WITH C AS( SELECT MachineID, RecordedDate, Pressure, ROW_NUMBER() OVER(ORDER BY MachineID, RecordedDate) - ROW_NUMBER() OVER(ORDER BY Pressure,MachineID,RecordedDate) AS grp FROM dbo.UpdateGaps)SELECT MachineID, MIN(RecordedDate) AS mn, MAX(RecordedDate) AS mx, PressureFROM CGROUP BY MachineID, Pressure, grpORDER BY MachineID, mn;[/code]If anyone has done this sort of thing before, I would greatly appreciate any help you have to offer. And thank you, capnhector, for linking to the PDF in the thread, "[url=http://www.sqlservercentral.com/Forums/Topic1393173-392-1.aspx][b]Solve Problems Using Recursive CTE[/b][/url]". That's a great source of information on gaps and islands. Thanks to Itzik Ben-Gan for writing it.Thu, 06 Dec 2012 17:36:04 GMToralinque