Solved

How can I generate a view that will give a SUM of specific columns from multiple other views?

Posted on 2011-03-18
3
199 Views
Last Modified: 2012-05-11
Good morning, Experts-Exchange community.  

I currently have 8 SQL views that give some detailed calculations based on a Customer ID which in turn will be used to generate a scoring model.  Is there a way for me to create another view that will show all Customer ID's, all 8 results, and a SUM?

Here's the kicker.  One view holds a DISTINCT listing of every Customer ID in the database along side it's Customer Type with a score given to the type.  Other views do not show every Customer ID as they may not receive a score due to not meeting the criteria.

Examples:

View 1:
CustID  |  CustType  |  TypeScore
1234  |  O  |   5
5678  |  B   |   15
6543  |  O  |   5
3456  |  B  |   15

View 2:
CustID   |   CustState   |   StateScore
1234  |  OH  |   0
5678  |  CA   |  20
6543  |  MI    |   20
3456  |  OH  |   0

View 3:
CustID   |   CashCount   |   CountScore
1234   |    6    |    30
6543   |    2    |    15

(The view I would want to create w/ the three "Score" columns with any and all Distinct Customer ID's - from "View 3", the customer ID's that aren't there would get a default value of "0"...)
SUM View:
CustID   |   TypeScore  |   StateScore   |   CountScore   |    ScoreSUM
1234   |   5     |   0    |   20  |   25
5678   |   15   |   20  |   0    |   35
6543   |   5     |   20  |   15  |   40
3456   |   15   |   0    |   0    |   15

Is something like this possible???
0
Comment
Question by:NCollinsBBP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:NCollinsBBP
ID: 35166381
Better yet.  Would it be more wise to combine all 8 views into one?  I can post the Queries used in each view if this helps.
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35166403
this will do

SELECT
	View1.* ,
	IsNull(View2.StateScore, 0) StateScore,
	IsNull(View3.CountScore, 0) CountScore,
	View1.TypeScore + IsNull(View2.StateScore, 0) + IsNull(View3.CountScore, 0) ScoreSUM
FROM
	View1
	LEFT OUTER JOIN View2 ON View1.CustID = View2.CustID
	LEFT OUTER JOIN View3 ON View1.CustID = View3.CustID

Open in new window


if all View get data from same table.
create SUM view directly from code maybe better.
0
 

Author Closing Comment

by:NCollinsBBP
ID: 35166548
Thank you JoeNuvo!
The code you provided worked wonders.  Combining all views into one is going to have to wait until I open up more programming time.  This will help me greatly in the meantime.  

-NCollinsBBP
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question