SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inner join two tables independence of each ohter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 01/25/2013 :  05:32:27  Show Profile  Reply with Quote
I have two tables independent of each other though with similar data. One table has date format varchar(8)(yyymmdd)and the other one varchar(20)(dd/mm/yyyy)


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


I have written the above code to get the output as below. But I was getting output like "12934234", "13242343" which is impossible.



Date      | XYZ_table1 | xyz_table2
20121201  | 5365       | 8430



Edited by - learning_grsql on 01/25/2013 05:33:39

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/25/2013 :  05:39:30  Show Profile  Reply with Quote
your question is not clear.
12934234 etc values are coming for what dields?
also why do you think its impossible
post some sample data and explain output out of them for us to understand

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

Go to Top of Page

Ifor
Aged Yak Warrior

574 Posts

Posted - 01/25/2013 :  08:31:03  Show Profile  Reply with Quote

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];
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/25/2013 :  09:43:38  Show Profile  Reply with Quote
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];



the CASTing to date part will work so far as OP has date values stored in it in unambiguos consistent format

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

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

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 01/28/2013 :  03:37:46  Show Profile  Reply with Quote
@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"...................

Edited by - learning_grsql on 01/28/2013 03:39:46
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/28/2013 :  04:13:05  Show Profile  Reply with Quote
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"...................


are you trying to group them based on date ? then you could use like


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]


Beware that this will work only if date values in each column are in consistent format

That was the reason why you got overflow error before. Make sure you read my posted link.

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

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 01/28/2013 :  15:40:49  Show Profile  Reply with Quote
@visakh. Thank you very much. However, I'm still getting the same arithmetic flow error and I have created a new thread for that. I'll keep trying and post here back once it works.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 02/01/2013 :  14:15:48  Show Profile  Reply with Quote
@visakh
I managed to get through it using your code. However, I retained the date dataype as Varchar(8). Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2013 :  03:09:17  Show Profile  Reply with Quote
welcome. You're making your life miserable by storing dates as varchar

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

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 02/02/2013 :  04:33:25  Show Profile  Reply with Quote
@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.

Edited by - learning_grsql on 02/02/2013 04:34:39
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/02/2013 :  04:37:12  Show Profile  Reply with Quote
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.


No issues
atleast in future try to use proper datatype for various fields.

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000