-4

Possible Duplicate:
How to join two tables

table 1 

Date         StartingAum

07/01/2010     120
08/01/2010     220
09/01/2010     320

table 2

Date          DepContr   withdra    
01/01/2010      60          15
02/01/2010      70          25
03/01/2010      80          15
04/01/2010      30          89 
05/01/2010      40          15
06/01/2010      25          85
07/01/2010      16          17   
08/01/2010      19          21
09/01/2010      68          79

the output should be 

Date         StartingAum     DepContr   withdra    
01/01/2010      0              60          15
02/01/2010      0              70          25
03/01/2010      0              80          15
04/01/2010      0              30          89 
05/01/2010      0              40          15
06/01/2010      0              25          85
07/01/2010     120             16          17   
08/01/2010     220             19          21
09/01/2010     320             68          79

i need the output exactly similar to that

Community
  • 1
  • 1
rakesh
  • 31
  • 4
  • 1
    it's inappropriate for you to repeatedly ask how to generate results sets. Learn from your [previous question](http://stackoverflow.com/questions/3945837/how-to-join-two-tables), asked not even an hour ago. – Michael Petrotta Oct 15 '10 at 21:10
  • 1
    -1 for the similar questions so close together in time. Rapid fire Q and A might be more appropriate in an IRC (chat room). Google "IRC sql". You can have dialogue there that could be really helpful. – orolo Oct 15 '10 at 21:18
  • 1
    welcome to stackoverflow! some people are a little cranky sometimes, just ignore them and ask your questions. – KM. Oct 15 '10 at 21:32
  • 1
    I think its okay, if we try to keep certain standards so this forum wont get flooded by questions like the above, so they are right in their comments. But lets try to help him out this time and be nice, nothing bad will happend to us from doing that. – BerggreenDK Oct 15 '10 at 21:41

2 Answers2

2
DECLARE @Table1 table ([date] datetime, StartingAum int)
DECLARE @Table2 table ([date] datetime, DepContr int, withdra int)
INSERT @Table1 VALUES ('07/01/2010',     120)
INSERT @Table1 VALUES ('08/01/2010',     220)
INSERT @Table1 VALUES ('09/01/2010',     320)

INSERT @Table2 VALUES ('01/01/2010',      60 ,         15)
INSERT @Table2 VALUES ('02/01/2010',      70 ,         25)
INSERT @Table2 VALUES ('03/01/2010',      80 ,         15)
INSERT @Table2 VALUES ('04/01/2010',      30 ,         89)
INSERT @Table2 VALUES ('05/01/2010',      40 ,         15)
INSERT @Table2 VALUES ('06/01/2010',      25 ,         85)
INSERT @Table2 VALUES ('07/01/2010',      16 ,         17)
INSERT @Table2 VALUES ('08/01/2010',      19 ,         21)
INSERT @Table2 VALUES ('09/01/2010',      68 ,         79)


SELECT 
    t2.[Date]
        ,ISNULL(t1.StartingAum, 0) AS StartingAum     
        ,t2.DepContr
        ,t2.withdra   
    FROM @Table2           t2
        LEFT JOIN @Table1  t1 ON t2.[Date] = t1.[Date]
    ORDER BY t2.[Date]

OUTPUT: 

Date                    StartingAum DepContr    withdra
----------------------- ----------- ----------- -----------
2010-01-01 00:00:00.000 0           60          15
2010-02-01 00:00:00.000 0           70          25
2010-03-01 00:00:00.000 0           80          15
2010-04-01 00:00:00.000 0           30          89
2010-05-01 00:00:00.000 0           40          15
2010-06-01 00:00:00.000 0           25          85
2010-07-01 00:00:00.000 120         16          17
2010-08-01 00:00:00.000 220         19          21
2010-09-01 00:00:00.000 320         68          79
KM.
  • 98,537
  • 33
  • 172
  • 205
  • the startingAum column is returning all 0's – rakesh Oct 15 '10 at 21:16
  • 1
    startingAum will return a zero if its Table1 value is a zero, or if the matching row is not in Table2. If there are any times on any of these date values (like `2010-01-01 13:45:23.123`) then they have to be exactly the same or the join will not find a match and startingAum will be zero. If the dates DO have any time on them, change the `LEFT JOIN` line to : `LEFT JOIN @Table1 t1 ON DATEADD(day,DATEDIFF(day,0,t2.[Date]),0) = DATEADD(day,DATEDIFF(day,0,t1.[Date]),0)` and it will join on just the date and not the time. – KM. Oct 15 '10 at 21:22
  • if they are variable @temp tables or actual tempdb #temp tables shouldn't matter, just change `FROM @Table2 t2` to `FROM #Table2 t2` and `LEFT JOIN @Table1 t1` to `LEFT JOIN #Table1 t1` and you should be good to go. – KM. Oct 15 '10 at 21:32
  • StartingAum coloumn is returning 0's – rakesh Oct 15 '10 at 21:34
0

I think you should have a look at this page: http://www.w3schools.com/sql/sql_union.asp That might be helpful?

BerggreenDK
  • 4,786
  • 9
  • 38
  • 60
  • sorry my answer might not be the best, but I need a better explanation of what you are trying to calculate before I can help you construct the SQL needed. I see some of the other answers are already doing that so I will wait and see if more answers needed or not. Sorry about the lame link. – BerggreenDK Oct 15 '10 at 21:44