select a.date, 
sum(case when a.type="xyz" Then cast(a.price as int) else 0 end) as XYZ_table1,
sum(case when b.type='xyz' Then cast(b.price as int) else 0 end) as XYZ_table2
from table1 a
inner join table2 b on a.date = convert(varchar(8),convert(datetime,b.date,112))
where 
a.date = '20121201' and convert(varchar(8),convert(datetime,b.date,112)) = '20121201' 
group by a.date

Date      | XYZ_table1 | xyz_table2
20121201  | 5365       | 8430

WITH T1
AS
(
	SELECT CAST([date] AS datetime) AS [date]
		,SUM(CASE WHEN [type] = 'xyz' THEN CAST(price AS int) ELSE 0 END) AS XYZ_table1
	FROM table1
	GROUP BY CAST([date] AS datetime)
)
, T2
AS
(
	SELECT CONVERT(datetime, [date], 112) AS [date]
		,SUM(CASE WHEN [type] = 'xyz' THEN CAST(price AS int) ELSE 0 END) AS XYZ_table2
	FROM table2
	GROUP BY CONVERT(datetime, [date], 112)
)
SELECT T1.[date], T1.XYZ_table1, T2.XYZ_table2
FROM T1
	JOIN T2
		ON T1.[date] = T2.[date];

quote:

---

Originally posted by Ifor

WITH T1
AS
(
	SELECT CAST([date] AS datetime) AS [date]
		,SUM(CASE WHEN [type] = 'xyz' THEN CAST(price AS int) ELSE 0 END) AS XYZ_table1
	FROM table1
	GROUP BY CAST([date] AS datetime)
)
, T2
AS
(
	SELECT CONVERT(datetime, [date], 112) AS [date]
		,SUM(CASE WHEN [type] = 'xyz' THEN CAST(price AS int) ELSE 0 END) AS XYZ_table2
	FROM table2
	GROUP BY CONVERT(datetime, [date], 112)
)
SELECT T1.[date], T1.XYZ_table1, T2.XYZ_table2
FROM T1
	JOIN T2
		ON T1.[date] = T2.[date];

---

quote:

---

Originally posted by learning_grsql

@visakh......I said the total is incorrect because each day xyz value cannot be more than 10000
Here is my first table - here date datetype is varchar(8)
Date | type | price | supplier code .....more columns
20121201 | XYZ | 32 | 254
20121201 | ABC | 44 | 121
20121201 | DEF | 12 | 333
20121201 | XYZ | 20 | 452
20121201 | XYZ | 33 | 333
.....etc.

Second Table - here date datatype is 'varchar(20)'

Date | type | price | supplier code....more columns
01/12/2012 | XYZ | 32 | 254
01/12/2012 | ABC | 44 | 121
01/12/2012 | DEF | 12 | 333
01/12/2012 | XYZ | 20 | 452
01/12/2012 | XYZ | 33 | 333
...etc.

@ifor...your code gets me error something like "arithmetic flow error while converting to datetime"...................

---

SELECT m.DateVal,m.[Type],
m.Total AS table1total,
n.Total AS table2total,
m.Total + n.Total AS Total
FROM
(
SELECT CONVERT(datetime,[Date],112) AS DateVal,type, SUM(price) AS Total
FROM Table1
GROUP BY  CONVERT(datetime,[Date],112),type
)m
INNER JOIN (SELECT CONVERT(datetime,[Date],103) AS DateVal,type, SUM(price) AS Total
FROM Table2
GROUP BY  CONVERT(datetime,[Date],103),type
)n
ON n.DateVal = m.DateVal
AND n.[Type] = m.[Type]

quote:

---

Originally posted by learning_grsql

@visakh16...Thanks a lot. I understand that but a lot of stored procedures and other stuffs depending on it. I'm still not good enough to understand what are the things it's going to affect and what are the things I have to correct if I change it to Date data type now.

In brief, I'm scared to change it to date now but I'm sure will change it.

---