Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
srattman72
Starting Member
4 Posts |
Posted - 2013-01-30 : 09:02:11
|
I was hoping I could get help with the following scenario. I have a dataset containing model scores for members for the current month. I want to append this to a dataset that contains the same scores historically over previous months. My final output will have ALL members with their scores. Here are the caveats:1. If a member is being scored for the first time, then I want to show the score for the current month, then show 0's for all other months.2. If a member was on the historical file, but is not on the current file, I want to show a 0 for the current month score, and show the historical scores as populatedSo, if I have the following tables:Current Month ScoreMemberID Score0001 245002 275004 300Historical Months ScoresMemberID Score1 Score2001 230 265002 325 255003 500 450I want the output to look like this:MemberID Score0 Score1 Score2001 245 230 265002 275 325 255 003 0 500 450004 300 0 0Any help is appreciated.Thanks,Shane |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-30 : 09:48:23
|
Assuming you are doing this in a SQL Server database, this is not a scalable design. What I would suggest instead, is to have a table with 3 columns - Date, MemberId and Score. Then, as the data for each month comes in, simply insert the data into the table (with the first day of the current month as date).Now, that does not give you the report in the way you described - but that can be done via pivoting in SQL or on a report server (or other places where you may be consuming this data).I realize this is not the exact answer you want to hear, but doing it this way will make it easy for you in a number of ways - scalability, ability to query data for various questions that may be asked etc. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-30 : 12:59:38
|
Ihave to agree with JaMes on the schema, but if you are looking for a solution given your current structure take a look at FULL OUTER JOIN:http://msdn.microsoft.com/en-us/library/ms187518(v=sql.105).aspx |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-04 : 23:47:41
|
Just applied FULL JOINDECLARE @CurrentMonthScore TABLE(MemberID CHAR(3), Score0 int)INSERT INTO @CurrentMonthScore SELECT '001', 245 union allSELECT '002', 275 union allSELECT '004', 300DECLARE @HistoricalMonthsScores TABLE(MemberID CHAR(3), Score1 int, Score2 int)INSERT INTO @HistoricalMonthsScoresSELECT '001', 230, 265 union allSELECT '002', 325, 255 union allSELECT '003', 500, 450SELECT COALESCE(c.MemberID, h.MemberId) MemberID , ISNULL(Score0, 0) Score0 , ISNULL(Score1,0) Score1, ISNULL(Score2,0) Score2FROM @CurrentMonthScore cFULL JOIN @HistoricalMonthsScores h ON c.MemberID= h.MemberIDORDER BY MemberID --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 23:59:14
|
another way DECLARE @CurrentMonthScore TABLE(MemberID CHAR(3), Score0 int)INSERT INTO @CurrentMonthScore SELECT '001', 245 union allSELECT '002', 275 union allSELECT '004', 300DECLARE @HistoricalMonthsScores TABLE(MemberID CHAR(3), Score1 int, Score2 int)INSERT INTO @HistoricalMonthsScoresSELECT '001', 230, 265 union allSELECT '002', 325, 255 union allSELECT '003', 500, 450SELECT MemberID,SUM(Score0) AS Score0,SUM(Score1) AS Score1,SUM(Score2) AS Score2FROM(SELECT MemberID,Score0,CAST(0 AS int) AS Score1,CAST(0 AS int) AS Score2 FROM @CurrentMonthScoreUNION ALLSELECT MemberID,0,Score1,Score2 FROM @HistoricalMonthsScores)tGROUP BY MemberIDoutput-----------------------------------------MemberID Score0 Score1 Score2-----------------------------------------001 245 230 265002 275 325 255003 0 500 450004 300 0 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|