SQL Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

amit.2601
Starting Member

8 Posts

Posted - 2010-03-29 : 05:10:24

I have to fetch 2 column for 3 days on the basis of date by joining two table..
i want to fetch data for one column on the basis of date in one table while for other column on the basis of this table

I want to display data in this manner.

date columnA ColumnB
2010-03-24 2 5
2010-03-25 3 8

i.e column A count is from table A on the basis of date of this table while column B count from table B on the basis of date of this column.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-29 : 05:17:41

please post the table DDL, sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 06:11:39

can there be gaps in dates in both tables? which table should be considered as master table in that case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 06:15:09

In any case you can use this


SELECT COALESCE(a.datecolumn,b.datecolumn) AS Date,
COALESCE(Acnt,0) AS columnA,
COALESCE(Bcnt,0) AS columnB
FROM (SELECT datecolumn,COUNT(colA) AS Acnt
      FROM tableA 
      GROUP BY datecolumn)a
FULL OUTER JOIN (SELECT datecolumn,COUNT(colB) AS Bcnt
      FROM tableB 
      GROUP BY datecolumn)b
ON b.datecolumn = a.datecolumn

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources