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
 inner join two tables independence of each ohter

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-01-25 : 05:32:27
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


visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 05:39:30
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

700 Posts

Posted - 2013-01-25 : 08:31:03
[code]
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];
[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 09:43:38
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 - 2013-01-28 : 03:37:46
@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"...................
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 04:13:05
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 - 2013-01-28 : 15:40:49
@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 - 2013-02-01 : 14:15:48
@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

52326 Posts

Posted - 2013-02-02 : 03:09:17
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 - 2013-02-02 : 04:33:25
@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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-02 : 04:37:12
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
   

- Advertisement -