ansaurus

Question

joining two tables

Answer 1

+2 A:

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 2010-10-15 21:12:25

the startingAum column is returning all 0's

rakesh 2010-10-15 21:16:21

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 2010-10-15 21:22:02

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 2010-10-15 21:32:02

StartingAum coloumn is returning 0's

rakesh 2010-10-15 21:34:11

Answer 2

A:

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

BerggreenDK 2010-10-15 21:42:38

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 2010-10-15 21:44:27

ansaurus

tags:

views:

answers:

joining two tables

related questions