Post reply

Help- Very Complex SQL query

  • February 20, 2010 at 4:18 am

    #1122301

    Re-using ChrisM's super test-data script:

    DROP TABLE #temp;

    GO

    CREATE TABLE #temp

    (

    date1 DATETIME NULL,

    date2 DATETIME NULL

    );

    GO

    INSERT #temp (date1,date2)

    SELECT '2009-01-01','2009-01-01' UNION ALL

    SELECT '2009-01-02',null UNION ALL

    SELECT '2009-01-03',null UNION ALL

    SELECT '2009-01-04',null UNION ALL

    SELECT '2009-01-05','2009-01-05' UNION ALL

    SELECT '2009-01-06',null UNION ALL

    SELECT '2009-01-07',null UNION ALL

    SELECT '2009-01-08',null UNION ALL

    SELECT '2009-01-09',null UNION ALL

    SELECT '2009-01-10','2009-01-10' UNION ALL

    SELECT '2009-01-11',null UNION ALL

    SELECT '2009-01-12',null UNION ALL

    SELECT '2009-01-13',null UNION ALL

    SELECT '2009-01-14',null UNION ALL

    SELECT '2009-01-15','2009-01-15' UNION ALL

    SELECT '2009-01-16',null UNION ALL

    SELECT '2009-01-17',null UNION ALL

    SELECT '2009-01-18',null UNION ALL

    SELECT '2009-01-19',null

    GO

    ...the following solution appeals to me:

    WITH RowsToUpdate

    AS (

    SELECT T1.date1,

    T1.date2 AS original_date2,

    CA.date2 AS new_date2

    FROM #temp T1

    CROSS

    APPLY (

    SELECT TOP (1)

    T2.date2

    FROM #temp T2

    WHERE T2.date2 IS NOT NULL

    AND T2.date2 <= T1.date1

    ORDER BY

    T2.date2 DESC

    ) CA

    WHERE T1.date2 IS NULL

    )

    UPDATE RowsToUpdate

    SET original_date2 = new_date2;

    For larger data sets, an index on the date2 column would be beneficial 🙂

    edit: to reproduce the sample data, to add comment about the index, and to fix a bug!

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 20, 2010 at 4:43 am

    #1122304

    Paul White (2/20/2010)

    Using ChrisM's Arun's super test-data script, the following appeals to me:

    snip

    'Course it does, it's got a CROSS APPLY in it!:hehe:

    CROSS APPLY is an awesome operator to use for compartmenting complex stuff for an output column without fear of changing the cardinality of the result set. Almost a scalar function replacement, with full code visibility.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • February 20, 2010 at 4:57 am

    #1122306

    ChrisM@home

    'Course it does, it's got a CROSS APPLY in it!:hehe:

    CROSS APPLY is an awesome operator to use for compartmenting complex stuff for an output column without fear of changing the cardinality of the result set. Almost a scalar function replacement, with full code visibility.

    Quite so. I am an APPLY addict - I admit it 😉

    Throw in an UPDATEable CTE, and I am very happy. Small things, eh?

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 20, 2010 at 5:55 am

    #1122310

    Guys,

    thanks for your awesome replies and suggestions but i think i myself got deviated from the original requirement. Below is what I actually need.

    if OBJECT_ID('tempdb..#tmp_Dates1') is not null

    drop table #tmp_Dates1

    create table #tmp_Dates1

    (

    value_date datetime,

    value_date_minus_1 datetime,

    notional numeric(18,6)

    )

    -- PLEASE NOTE THAT THE FIRST ROW notional will always be NULL in this table

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-05 00:00:00.000','2008-11-04 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-13 00:00:00.000','2008-11-12 00:00:00.000',10)

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-26 00:00:00.000','2008-11-25 00:00:00.000',20)

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-30 00:00:00.000','2008-11-30 00:00:00.000',30)

    INSERT INTO #tmp_Dates1 VALUES ('2008-12-12 00:00:00.000','2008-12-11 00:00:00.000',40)

    INSERT INTO #tmp_Dates1 VALUES ('2008-12-19 00:00:00.000','2008-12-18 00:00:00.000',50)

    INSERT INTO #tmp_Dates1 VALUES ('2008-12-31 00:00:00.000','2008-12-31 00:00:00.000',60)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-07 00:00:00.000','2009-01-06 00:00:00.000',70)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-21 00:00:00.000','2009-01-20 00:00:00.000',80)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-22 00:00:00.000','2009-01-21 00:00:00.000',90)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-31 00:00:00.000','2009-01-31 00:00:00.000',100)

    if OBJECT_ID('tempdb..#tmp_Dates_Notional') is not null

    drop table #tmp_Dates_Notional

    create table #tmp_Dates_Notional

    (

    value_date_minus_1 datetime,

    notional numeric(18,6)

    )

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-06 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-07 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-08 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-09 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-10 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-11 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-12 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-13 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-14 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-15 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-16 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-17 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-18 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-19 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-20 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-21 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-22 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-23 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-24 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-25 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-26 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-27 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-28 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-29 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-30 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-01 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-02 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-03 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-04 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-05 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-06 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-07 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-08 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-09 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-10 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-11 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-12 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-13 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-14 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-15 00:00:00.000',NULL)

    UPDATE t1

    SET t1.notional = t2.notional

    from #tmp_Dates_Notional t1

    INNER JOIN

    #tmp_Dates1 t2

    on

    t2.value_date_minus_1 = t1.value_date_minus_1

    select * from #tmp_Dates_Notional

    -- The desired output I need is :

    --value_date_minus_1 notional

    --2008-11-04 00:00:00.000 10

    --2008-11-05 00:00:00.000 10

    --2008-11-06 00:00:00.000 10

    --2008-11-07 00:00:00.000 10

    --2008-11-08 00:00:00.000 10

    --2008-11-09 00:00:00.000 10

    --2008-11-10 00:00:00.000 10

    --2008-11-11 00:00:00.000 10

    --2008-11-12 00:00:00.000 10

    --2008-11-13 00:00:00.000 20

    --2008-11-14 00:00:00.000 20

    --2008-11-15 00:00:00.000 20

    --2008-11-16 00:00:00.000 20

    --2008-11-17 00:00:00.000 20

    --2008-11-18 00:00:00.000 20

    --2008-11-19 00:00:00.000 20

    --2008-11-20 00:00:00.000 20

    --2008-11-21 00:00:00.000 20

    --2008-11-22 00:00:00.000 20

    --2008-11-23 00:00:00.000 20

    --2008-11-24 00:00:00.000 20

    --2008-11-25 00:00:00.000 20

    --2008-11-26 00:00:00.000 30

    --2008-11-27 00:00:00.000 30

    --2008-11-28 00:00:00.000 30

    --2008-11-29 00:00:00.000 30

    --2008-11-30 00:00:00.000 30

    --2008-12-01 00:00:00.000 40

    --2008-12-02 00:00:00.000 40

    --2008-12-03 00:00:00.000 40

    --2008-12-04 00:00:00.000 40

    --2008-12-05 00:00:00.000 40

    --2008-12-06 00:00:00.000 40

    --2008-12-07 00:00:00.000 40

    --2008-12-08 00:00:00.000 40

    --2008-12-09 00:00:00.000 40

    --2008-12-10 00:00:00.000 40

    --2008-12-11 00:00:00.000 40

    --2008-12-12 00:00:00.000 40

    --2008-12-13 00:00:00.000 50

    --2008-12-14 00:00:00.000 50

    --2008-12-15 00:00:00.000 50

    Again, I'd not like to use CTE to achieve this result

  • February 20, 2010 at 5:58 am

    #1122312

    Guys,

    thanks for your awesome replies and suggestions but i think i myself got deviated from the original requirement. Below is what I actually need.

    if OBJECT_ID('tempdb..#tmp_Dates1') is not null

    drop table #tmp_Dates1

    create table #tmp_Dates1

    (

    value_date datetime,

    value_date_minus_1 datetime,

    notional numeric(18,6)

    )

    -- PLEASE NOTE THAT THE FIRST ROW notional will always be NULL in this table

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-05 00:00:00.000','2008-11-04 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-13 00:00:00.000','2008-11-12 00:00:00.000',10)

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-26 00:00:00.000','2008-11-25 00:00:00.000',20)

    INSERT INTO #tmp_Dates1 VALUES ('2008-11-30 00:00:00.000','2008-11-30 00:00:00.000',30)

    INSERT INTO #tmp_Dates1 VALUES ('2008-12-12 00:00:00.000','2008-12-11 00:00:00.000',40)

    INSERT INTO #tmp_Dates1 VALUES ('2008-12-19 00:00:00.000','2008-12-18 00:00:00.000',50)

    INSERT INTO #tmp_Dates1 VALUES ('2008-12-31 00:00:00.000','2008-12-31 00:00:00.000',60)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-07 00:00:00.000','2009-01-06 00:00:00.000',70)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-21 00:00:00.000','2009-01-20 00:00:00.000',80)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-22 00:00:00.000','2009-01-21 00:00:00.000',90)

    INSERT INTO #tmp_Dates1 VALUES ('2009-01-31 00:00:00.000','2009-01-31 00:00:00.000',100)

    if OBJECT_ID('tempdb..#tmp_Dates_Notional') is not null

    drop table #tmp_Dates_Notional

    create table #tmp_Dates_Notional

    (

    value_date_minus_1 datetime,

    notional numeric(18,6)

    )

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-06 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-07 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-08 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-09 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-10 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-11 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-12 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-13 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-14 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-15 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-16 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-17 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-18 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-19 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-20 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-21 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-22 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-23 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-24 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-25 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-26 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-27 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-28 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-29 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-11-30 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-01 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-02 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-03 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-04 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-05 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-06 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-07 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-08 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-09 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-10 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-11 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-12 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-13 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-14 00:00:00.000',NULL)

    INSERT INTO #tmp_Dates_Notional VALUES ('2008-12-15 00:00:00.000',NULL)

    UPDATE t1

    SET t1.notional = t2.notional

    from #tmp_Dates_Notional t1

    INNER JOIN

    #tmp_Dates1 t2

    on

    t2.value_date_minus_1 = t1.value_date_minus_1

    select * from #tmp_Dates_Notional

    -- The desired output I need is :

    --value_date_minus_1notional

    --2008-11-04 00:00:00.00010

    --2008-11-05 00:00:00.00010

    --2008-11-06 00:00:00.00010

    --2008-11-07 00:00:00.00010

    --2008-11-08 00:00:00.00010

    --2008-11-09 00:00:00.00010

    --2008-11-10 00:00:00.00010

    --2008-11-11 00:00:00.00010

    --2008-11-12 00:00:00.00010

    --2008-11-13 00:00:00.00020

    --2008-11-14 00:00:00.00020

    --2008-11-15 00:00:00.00020

    --2008-11-16 00:00:00.00020

    --2008-11-17 00:00:00.00020

    --2008-11-18 00:00:00.00020

    --2008-11-19 00:00:00.00020

    --2008-11-20 00:00:00.00020

    --2008-11-21 00:00:00.00020

    --2008-11-22 00:00:00.00020

    --2008-11-23 00:00:00.00020

    --2008-11-24 00:00:00.00020

    --2008-11-25 00:00:00.00020

    --2008-11-26 00:00:00.00030

    --2008-11-27 00:00:00.00030

    --2008-11-28 00:00:00.00030

    --2008-11-29 00:00:00.00030

    --2008-11-30 00:00:00.00030

    --2008-12-01 00:00:00.00040

    --2008-12-02 00:00:00.00040

    --2008-12-03 00:00:00.00040

    --2008-12-04 00:00:00.00040

    --2008-12-05 00:00:00.00040

    --2008-12-06 00:00:00.00040

    --2008-12-07 00:00:00.00040

    --2008-12-08 00:00:00.00040

    --2008-12-09 00:00:00.00040

    --2008-12-10 00:00:00.00040

    --2008-12-11 00:00:00.00040

    --2008-12-12 00:00:00.00040

    --2008-12-13 00:00:00.00050

    --2008-12-14 00:00:00.00050

    --2008-12-15 00:00:00.00050

  • February 20, 2010 at 6:05 am

    #1122314

    sachin1sharma (2/20/2010)

    AGAIN I don't want to use CTE to achieve this.

    A CTE is a pretty powerful tool - why don't you want to use it?

    In a lot of cases, a CTE can be thought of as "a pre-defined sub-query". If you really don't want to use a CTE, just move it to be a sub-query in your from clause. I'm not sure if this can be done with Paul's updatable CTE, but it sure looks like ChrisM's CTE can be modified to this.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 20, 2010 at 6:09 am

    #1122316

    Because I am using SQL Server 2000

  • February 20, 2010 at 6:25 am

    #1122317

    sachin1sharma (2/20/2010)

    Because I am using SQL Server 2000

    Oh for the love of small fluffy bunnies!!! :w00t:

    You did see that this is the 2008 Forum, right?

    Here is my final contribution, based on your latest expression of the requirement:

    WITH RowsToUpdate

    AS (

    SELECT T1.value_date_minus_1,

    T1.notional,

    CA.notional AS new_value

    FROM #tmp_Dates_Notional T1

    OUTER

    APPLY (

    SELECT TOP (1)

    T2.notional

    FROM #tmp_Dates_Notional T2

    WHERE T2.notional IS NOT NULL

    AND T2.value_date_minus_1 >= T1.value_date_minus_1

    ORDER BY

    T2.value_date_minus_1 ASC

    ) CA

    WHERE T1.notional IS NULL

    )

    UPDATE RowsToUpdate

    SET notional = ISNULL(new_value, 50);

    You'll notice I have hard-coded the 50, since there is no following data with that value, as in previous examples.

    Paul

    edit: toned-down slightly 😀

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 20, 2010 at 6:28 am

    #1122318

    Here's the non-CTE version:

    UPDATE RowsToUpdate

    SET notional = ISNULL(new_value, 50)

    FROM (

    SELECT T1.value_date_minus_1,

    T1.notional,

    CA.notional AS new_value

    FROM #tmp_Dates_Notional T1

    OUTER

    APPLY (

    SELECT TOP (1)

    T2.notional

    FROM #tmp_Dates_Notional T2

    WHERE T2.notional IS NOT NULL

    AND T2.value_date_minus_1 >= T1.value_date_minus_1

    ORDER BY

    T2.value_date_minus_1 ASC

    ) CA

    WHERE T1.notional IS NULL

    ) AS RowsToUpdate;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 20, 2010 at 6:42 am

    #1122322

    Non-CTE, non-APPLY version (2000 compatible?)

    UPDATE RowsToUpdate

    SET notional = ISNULL(new_value, 50)

    FROM (

    SELECT T1.value_date_minus_1,

    T1.notional,

    new_value =

    (

    SELECT MIN(notional)

    FROM #tmp_Dates_Notional T2

    WHERE T2.value_date_minus_1 >= T1.value_date_minus_1

    AND T2.notional IS NOT NULL

    )

    FROM #tmp_Dates_Notional T1

    WHERE T1.notional IS NULL

    ) AS RowsToUpdate;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 20, 2010 at 7:21 am

    #1122331

    sachin1sharma (2/20/2010)

    Guys,

    thanks for your awesome replies and suggestions but i think i myself got deviated from the original requirement. Below is what I actually need.

    select * from #tmp_Dates_Notional

    -- The desired output I need is :

    --value_date_minus_1notional

    --2008-11-04 00:00:00.00010

    --2008-11-05 00:00:00.00010

    --2008-11-06 00:00:00.00010

    --2008-11-07 00:00:00.00010

    So, in addition to updating column notional in table #tmp_Dates_Notional (from table #tmp_Dates1, you also want to add two extra rows to #tmp_Dates_Notional?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • February 20, 2010 at 7:35 am

    #1122332

    Paul White (2/20/2010)

    sachin1sharma (2/20/2010)

    Because I am using SQL Server 2000

    Oh for the love of small fluffy bunnies!!! :w00t:

    You did see that this is the 2008 Forum, right?

    Well, this certainly explains a lot. By posting in the SQL 2008 forum, we were all trying to give you solutions that would work in SQL 2008. The forums here are divided out by the version of SQL that you are using. You would have helped a lot of people that are trying to help you by posting this in the SQL 2000 forum.

    It only took 22 posts for this crucial piece of information to come out... and your previous posts mentioned it as a desire to avoid the CTEs:

    Again, I'd not like to use CTE to achieve this result

    and

    I don't want to user cursors/loop/CTE

    Not only can you not use a CTE, you also wouldn't be able to use the CROSS APPLY from Paul's post (which I see he has already provided a solution that considers this).

    In the future, please try to post your problem in the appropriate forum.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 20, 2010 at 8:04 am

    #1122335

    WayneS (2/20/2010)

    ...you also wouldn't be able to use the CROSS APPLY from Paul's post (which I see he has already provided a totally awesome solution that considers this).

    Fixed that for you, Wayne :laugh:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 20, 2010 at 9:16 am

    #1122341

    ChrisM@home (2/20/2010)

    Paul White (2/20/2010)

    Using ChrisM's Arun's super test-data script, the following appeals to me:

    snip

    'Course it does, it's got a CROSS APPLY in it!:hehe:

    CROSS APPLY is an awesome operator to use for compartmenting complex stuff for an output column without fear of changing the cardinality of the result set. Almost a scalar function replacement, with full code visibility.

    Not that I don't like Crossy Apply, but - I'm missing the part where it's guaranteed to not change the cardinality. The times when I use it (with XML), it's being used to explode out sub nodes into separate rows, so it's changing cardinality on me (specifically based on me asking it to, of course).

    Can you give me an example of how you're using it in this way? I'm thinking you have some specific examples in mind, and I love to pick up new tricks.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 20, 2010 at 9:23 am

    #1122343

    Paul White (2/20/2010)

    Here's the non-CTE version:

    UPDATE RowsToUpdate

    SET notional = ISNULL(new_value, 50)

    FROM (

    SELECT T1.value_date_minus_1,

    T1.notional,

    CA.notional AS new_value

    FROM #tmp_Dates_Notional T1

    OUTER

    APPLY (

    SELECT TOP (1)

    T2.notional

    FROM #tmp_Dates_Notional T2

    WHERE T2.notional IS NOT NULL

    AND T2.value_date_minus_1 >= T1.value_date_minus_1

    ORDER BY

    T2.value_date_minus_1 ASC

    ) CA

    WHERE T1.notional IS NULL

    ) AS RowsToUpdate;

    OMG Paul, you're so amazing !! I apologize for not seeing the forum name before posting the first thread. I really really appreciate your and others great help. You're damn good! 🙂

Viewing 15 posts - 16 through 30 (of 62 total)

You must be logged in to reply to this topic. Login to reply