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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Appending current month data to historical data

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 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
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.
Go to Top of Page

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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-04 : 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
Go to Top of Page

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 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/
Go to Top of Page
   

- Advertisement -