| Author |
 Topic  |
|
|
srattman72
Starting Member
USA
4 Posts |
 Posted - 01/30/2013 : 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 populated
So, if I have the following tables:
Current Month Score
MemberID Score0
001 245
002 275
004 300
Historical Months Scores
MemberID Score1 Score2
001 230 265
002 325 255
003 500 450
I want the output to look like this:
MemberID Score0 Score1 Score2
001 245 230 265
002 275 325 255
003 0 500 450
004 300 0 0
Any help is appreciated.
Thanks,
Shane |
|
|
James K
Flowing Fount of Yak Knowledge
1709 Posts |
Posted - 01/30/2013 : 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
Flowing Fount of Yak Knowledge
3856 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1681 Posts |
Posted - 02/04/2013 : 23:47:41
|
Just applied FULL JOIN
DECLARE @CurrentMonthScore TABLE(MemberID CHAR(3), Score0 int)
INSERT INTO @CurrentMonthScore
SELECT '001', 245 union all
SELECT '002', 275 union all
SELECT '004', 300
DECLARE @HistoricalMonthsScores TABLE(MemberID CHAR(3), Score1 int, Score2 int)
INSERT INTO @HistoricalMonthsScores
SELECT '001', 230, 265 union all
SELECT '002', 325, 255 union all
SELECT '003', 500, 450
SELECT COALESCE(c.MemberID, h.MemberId) MemberID , ISNULL(Score0, 0) Score0 , ISNULL(Score1,0) Score1, ISNULL(Score2,0) Score2
FROM @CurrentMonthScore c
FULL JOIN @HistoricalMonthsScores h ON c.MemberID= h.MemberID
ORDER BY MemberID
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47969 Posts |
Posted - 02/04/2013 : 23:59:14
|
another way
DECLARE @CurrentMonthScore TABLE(MemberID CHAR(3), Score0 int)
INSERT INTO @CurrentMonthScore
SELECT '001', 245 union all
SELECT '002', 275 union all
SELECT '004', 300
DECLARE @HistoricalMonthsScores TABLE(MemberID CHAR(3), Score1 int, Score2 int)
INSERT INTO @HistoricalMonthsScores
SELECT '001', 230, 265 union all
SELECT '002', 325, 255 union all
SELECT '003', 500, 450
SELECT MemberID,SUM(Score0) AS Score0,SUM(Score1) AS Score1,SUM(Score2) AS Score2
FROM
(
SELECT MemberID,Score0,CAST(0 AS int) AS Score1,CAST(0 AS int) AS Score2
FROM @CurrentMonthScore
UNION ALL
SELECT MemberID,0,Score1,Score2
FROM @HistoricalMonthsScores
)t
GROUP BY MemberID
output
-----------------------------------------
MemberID Score0 Score1 Score2
-----------------------------------------
001 245 230 265
002 275 325 255
003 0 500 450
004 300 0 0
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 02/05/2013 00:00:40 |
|
|
| |
Topic |
|
|
|
Appending current month data to historical data
