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_table2from table1 ainner 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_table220121201 | 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 impossiblepost some sample data and explain output out of them for us to understand------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2013-01-25 : 08:31:03
|
[code]WITH T1AS( 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)), T2AS( 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_table2FROM T1 JOIN T2 ON T1.[date] = T2.[date];[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 09:43:38
|
quote: Originally posted by Ifor
WITH T1AS( 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)), T2AS( 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_table2FROM 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 formathttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 10000Here is my first table - here date datetype is varchar(8)Date | type | price | supplier code .....more columns20121201 | XYZ | 32 | 25420121201 | ABC | 44 | 12120121201 | DEF | 12 | 33320121201 | XYZ | 20 | 45220121201 | XYZ | 33 | 333.....etc.Second Table - here date datatype is 'varchar(20)'Date | type | price | supplier code....more columns01/12/2012 | XYZ | 32 | 25401/12/2012 | ABC | 44 | 12101/12/2012 | DEF | 12 | 33301/12/2012 | XYZ | 20 | 45201/12/2012 | XYZ | 33 | 333...etc.@ifor...your code gets me error something like "arithmetic flow error while converting to datetime"................... |
|
|
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 10000Here is my first table - here date datetype is varchar(8)Date | type | price | supplier code .....more columns20121201 | XYZ | 32 | 25420121201 | ABC | 44 | 12120121201 | DEF | 12 | 33320121201 | XYZ | 20 | 45220121201 | XYZ | 33 | 333.....etc.Second Table - here date datatype is 'varchar(20)'Date | type | price | supplier code....more columns01/12/2012 | XYZ | 32 | 25401/12/2012 | ABC | 44 | 12101/12/2012 | DEF | 12 | 33301/12/2012 | XYZ | 20 | 45201/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 likeSELECT m.DateVal,m.[Type],m.Total AS table1total,n.Total AS table2total,m.Total + n.Total AS TotalFROM(SELECT CONVERT(datetime,[Date],112) AS DateVal,type, SUM(price) AS TotalFROM Table1GROUP BY CONVERT(datetime,[Date],112),type)mINNER JOIN (SELECT CONVERT(datetime,[Date],103) AS DateVal,type, SUM(price) AS TotalFROM Table2GROUP BY CONVERT(datetime,[Date],103),type)nON n.DateVal = m.DateValAND n.[Type] = m.[Type] Beware that this will work only if date values in each column are in consistent formatThat was the reason why you got overflow error before. Make sure you read my posted link.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-01 : 14:15:48
|
@visakhI managed to get through it using your code. However, I retained the date dataype as Varchar(8). Thank you. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 issuesatleast in future try to use proper datatype for various fields.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|