SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Appending current month data to historical data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srattman72
Starting Member

USA
4 Posts

Posted - 01/30/2013 :  09:02:11  Show Profile  Reply with Quote
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

3707 Posts

Posted - 01/30/2013 :  09:48:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/30/2013 :  12:59:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/04/2013 :  23:47:41  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/04/2013 :  23:59:14  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000