August 13, 2011 at 4:14 am
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
August 13, 2011 at 5:52 am
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
August 15, 2011 at 2:49 am
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.
August 15, 2011 at 3:02 am
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
August 15, 2011 at 3:04 am
Thanks Phil,
I appreciate the help (on a Monday AM in particular) and shall beetle off to give this a try.
Regards, Greg.
August 15, 2011 at 3:08 am
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
August 15, 2011 at 7:30 am
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
August 15, 2011 at 7:48 am
Yep thanks - I've started with a list of all locations and dates, then left joined the other metrics in !
August 15, 2011 at 7:59 am
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
August 17, 2011 at 3:12 pm
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.'
August 17, 2011 at 3:14 pm
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.'
August 18, 2011 at 2:43 am
Thanks for your efforts, that is very useful and much appreciated.
Regards, Greg
August 19, 2011 at 9:56 am
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