July 26, 2011 at 8:56 am
Hi, I have 3 views with a similar data structure shown below. All columns are the same except the measurment (unit) column, what I would like to do is produce a report that shows data from all views and has the following columns:
MTH, empName, DataUnits, VoiceUnits, BroadcastUnits
what would be the best way to accomplish this,
thank you in advance,
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #DataUnits;
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #VoiceUnits;
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #BroadcastUnits;
CREATE TABLE #DataUnits
(MTH varchar(10),
empName varchar(10),
DataUnits INT)
CREATE TABLE #VoiceUnits
(MTH varchar(10),
empName varchar(10),
VoiceUnits INT)
CREATE TABLE #BroadcastUnits
(MTH varchar(10),
empName varchar(10),
BroadcastUnits INT)
Insert into #DataUnits
(MTH, empName, DataUnits)
select '2011-01', 'john', 11 union all
select '2011-01', 'mary', 14 union all
select '2011-02', 'john', 3 union all
select '2011-02', 'mary', 8
Insert into #VoiceUnits
(MTH, empName, VoiceUnits)
select '2011-01', 'greg', 17 union all
select '2011-01', 'anne', 13 union all
select '2011-02', 'greg', 5 union all
select '2011-02', 'anne', 9
Insert into #BroadcastUnits
(MTH, empName, BroadcastUnits)
select '2011-01', 'sean', 2 union all
select '2011-01', 'michelle', 15 union all
select '2011-02', 'sean', 4 union all
select '2011-02', 'michelle', 7
July 26, 2011 at 9:29 am
i saw that no one name was in two or more of the groups, so i'm assuming they have no relations? is that right? or should they be joined on the MTH?
would something like this work?
/*
MTHempNameDataUnitsVoiceUnitsBroadcastUnits
2011-01john11NULLNULL
2011-01mary14NULLNULL
2011-02john3NULLNULL
2011-02mary8NULLNULL
2011-01gregNULL17NULL
2011-01anneNULL13NULL
2011-02gregNULL5NULL
2011-02anneNULL9NULL
2011-01seanNULLNULL2
2011-01michelleNULLNULL15
2011-02seanNULLNULL4
2011-02michelleNULLNULL7
*/
SELECT MTH, empName, DataUnits,NULL AS VoiceUnits,NULL AS BroadcastUnits FROM #DataUnits
UNION ALL
SELECT MTH, empName, NULL AS DataUnits, VoiceUnits,NULL AS BroadcastUnits FROM #VoiceUnits
UNION ALL
SELECT MTH, empName, NULL AS DataUnits,NULL AS VoiceUnits, BroadcastUnits FROM #BroadcastUnits
or
--or
/*
MTHDataUnitsVoiceUnitsBroadcastUnits
2011-0110012068
2011-02445644
*/
SELECT COALESCE(#DataUnits.MTH,#VoiceUnits.MTH,#BroadcastUnits.MTH) AS MTH,
-- COALESCE(#DataUnits.empName,#VoiceUnits.empName,#BroadcastUnits.empName) AS empName,
SUM(#DataUnits.DataUnits) AS DataUnits,
SUM(#VoiceUnits.VoiceUnits) AS VoiceUnits,
SUM(#BroadcastUnits.BroadcastUnits) AS BroadcastUnits
FROM #DataUnits
LEFT OUTER JOIN #VoiceUnits
ON #DataUnits.MTH = #VoiceUnits.MTH
LEFT OUTER JOIN #BroadcastUnits
ON #DataUnits.MTH = #BroadcastUnits.MTH
GROUP BY COALESCE(#DataUnits.MTH,#VoiceUnits.MTH,#BroadcastUnits.MTH)
Lowell
July 26, 2011 at 9:50 am
Since we don't have information on your base tables or your views, it's hard to say what the best approach might be. If your views are simple selects on the same base tables, it might be better to work with the base tables. On the other hand, if your views have complicated selections or aggregations, you might be better of working with the views. Since we don't know your system, we can't tell you what the best approach really is.
If you provide the DDL for the tables and views involved, we can help you better.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 27, 2011 at 9:07 am
Hi Lowell, your second solution worked well. I never really understood how to use coalesce or how it would come in handy in a practical situation until now, so thank you. I do have a much better understanding of it now.
Drew, yes you're right, I only provided a simplyfied data example to work with. I was given access to about 15 views that are taking specific values from different source systems that are used for different products. I was asked to create a sales scorecard based on these views that shows sales in a month by product and associate. I don't have access to the source systems, just these pre defined views.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply