merging values and nulls

  • Hi All,

    I have two tables of this structure:

    MonthYear, Location, Metric 1, Metric 2

    In table 1, as an example, I have:

    2009-01, Room1, null, 1

    2009-02, Room1, null, 2

    And in table 2, I have:

    2009-01, Room1, 3, null

    2009-02, Room1, 4, null.

    I want to end up with:

    2009-01, Room1, 3, 1

    2009-02, Room1, 4, 2

    I'm look to group by the Yearmonth and Location columns as keys, but then kind of merge in the metrics.

    I'd appreciate any advice,

    Regards, Greg

  • Something like this might do it:

    select t1.MonthYear, t1.Location, Coalesce(t1.Metric1, t2.Metric1) Metric1, Coalesce(t1.Metric2, t2.Metric2) Metric2

    from t1 join t2 on t1.MonthYear = t2.MonthYear and t1.Location = t2.Location

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks Phil,

    Supplementary Question:

    If all of these rows were in 1 table - could you think of a solution ? Maybe something like selecting from itself ?

    Regards, Greg.

  • greg.bull (8/15/2011)


    Thanks Phil,

    Supplementary Question:

    If all of these rows were in 1 table - could you think of a solution ? Maybe something like selecting from itself ?

    Regards, Greg.

    Sure - I think it would work (it's Monday morning and thinking is not coming naturally :-))

    You'd just need to alias accordingly:

    select t1.MonthYear,

    t1.Location,

    coalesce(t1.Metric1, t2.Metric1) Metric1,

    coalesce(t1.Metric2, t2.Metric2) Metric2

    from t1 t1

    join t1 t2 on t1.MonthYear = t2.MonthYear and

    t1.Location = t2.Location

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thanks Phil,

    I appreciate the help (on a Monday AM in particular) and shall beetle off to give this a try.

    Regards, Greg.

  • No problem, happy beetling.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin (8/15/2011)


    You'd just need to alias accordingly:

    Just adding an alias isn't enough. The original solution assumes that the records are unique for MonthYear and Location within their respective tables. We know this cannot be the case if both records are in the same table. The proposed solution will produce eight rows instead of the expected two.

    2009-01 Room1 1 1

    2009-01 Room1 1 3

    2009-01 Room1 3 1

    2009-01 Room1 3 3

    2009-02 Room2 2 2

    2009-02 Room2 2 4

    2009-02 Room2 4 2

    2009-02 Room2 4 4

    Without further details, it's difficult to tell what the correct approach is. You might want to group on MonthYear and Location and use Max() or Sum() for the metrics.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Yep thanks - I've started with a list of all locations and dates, then left joined the other metrics in !

  • Just adding an alias isn't enough.

    Ah indeed. No doubt that fact would have become obvious very rapidly during development. I knew it was too early for me to have been answering questions ... 🙂

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • A more general way is to take the original code that Phil suggested, and add a third join criterion based on the order:

    WITH metric1Values AS (

    SELECT MonthYear, Location, Metric1, ROW_NUMBER() OVER(PARTITION BY MonthYear, Location ORDER BY Metric1) AS mergeKey

    FROM t1

    WHERE Metric1 IS NOT NULL

    ),

    metric2Values AS (

    SELECT MonthYear, Location, Metric2, ROW_NUMBER() OVER(PARTITION BY MonthYear, Location ORDER BY Metric2) AS mergeKey

    FROM t1

    WHERE Metric2 IS NOT NULL

    ),

    mergedMetrics AS (

    SELECT COALESCE(m1.MonthYear, m2.MonthYear) AS MonthYear,

    COALESCE(m1.Location, m2.Location) AS Location,

    m1.Metric1 AS Metric1,

    m2.Metric2 AS Metric2,

    COALESCE(m1.mergeKey, m2.mergeKey) AS SequenceSpecifier,

    FROM metric1Values m1

    FULL OUTER JOIN metric1Values m2 ON m1.MonthYear = m2.MonthYear

    AND m1.Location = m2.Location

    AND m1.mergeKey = m2.mergeKey)

    )

    SELECT * FROM mergedMetrics

    ORDER BY SequenceSpecifier

    What this does is line up the two sets of results in order,, and then grab the left and right value for each row.

    HTH

    -a.



    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

  • A more general way is to take the original code that Phil suggested, and add a third join criterion based on the order:

    WITH metric1Values AS (

    SELECT MonthYear, Location, Metric1, ROW_NUMBER() OVER(PARTITION BY MonthYear, Location ORDER BY Metric1) AS mergeKey

    FROM t1

    WHERE Metric1 IS NOT NULL

    ),

    metric2Values AS (

    SELECT MonthYear, Location, Metric2, ROW_NUMBER() OVER(PARTITION BY MonthYear, Location ORDER BY Metric2) AS mergeKey

    FROM t1

    WHERE Metric2 IS NOT NULL

    ),

    mergedMetrics AS (

    SELECT COALESCE(m1.MonthYear, m2.MonthYear) AS MonthYear,

    COALESCE(m1.Location, m2.Location) AS Location,

    m1.Metric1 AS Metric1,

    m2.Metric2 AS Metric2,

    COALESCE(m1.mergeKey, m2.mergeKey) AS SequenceSpecifier,

    FROM metric1Values m1

    FULL OUTER JOIN metric1Values m2 ON m1.MonthYear = m2.MonthYear

    AND m1.Location = m2.Location

    AND m1.mergeKey = m2.mergeKey)

    )

    SELECT * FROM mergedMetrics

    ORDER BY SequenceSpecifier

    What this does is line up the two sets of results in order,, and then grab the left and right value for each row.

    HTH

    -a.



    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

  • Thanks for your efforts, that is very useful and much appreciated.

    Regards, Greg

  • gosh, i hope you feel better now, having got that off you chest,

    Greg

Viewing 13 posts - 1 through 12 (of 12 total)

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