Hi, I have 2 tbls . I need it to look like the results below. Notice on tbl1 there is no 9.3.15 data and on tbl 2 there is not 9.4.15 data.
tbl1 tbl2
date Region tool cnt date Region misc cnt
9/1/2015 south 4 9/1/2015 south 12
9/2/2015 north 5 9/2/2015 north 14
9/4/2015 east 6 9/3/2015 west 35
9/5/2015 west 7
results
date region tool cnt misc cnt
9/1/2015 south 4 12
9/2/2015 north 5 14
9/3/2015 east null 35
9/4/2015 west 6 null
9/5/2015 west 7 null
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005
I think you're looking for a union and a subquery. This should do the trick...
SELECT [tbl1and2].[date], [tbl1and2].[Region], SUM([tbl1and2].[tool cnt]) AS [tool cnt], SUM([tbl1and2].[misc cnt]) AS [misc cnt]FROM( SELECT [tbl1].[date], [tbl1].[Region], [tbl1].[tool cnt], NULL AS [misc cnt] FROM [dbo].[tbl1] UNION ALL SELECT [tbl2].[date], [tbl2].[Region], NULL AS [tool cnt], [tbl2].[misc cnt] FROM [dbo].[tbl2] ) AS [tbl1and2]GROUP BY [tbl1and2].[date], [tbl1and2].[Region]ORDER BY [tbl1and2].[date]
select D.Date, isnull(t1.region, t2.region) as region, t1.[tool cnt], t2.[misc cnt]from ( select date from tbl1 UNION select date from tbl2) AS Dleft join tbl1 as t1on t1.date = D.dateleft join tbl2 as t2on t2.Date = D.Date
sorry it's right the full join. I wanted to give all points, but I accidently hit the later 2 first, then I couldn't give it to you. Is there a way I can go back and give you points?
Open in new window