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.
Author |
Topic |
Isianfas
Starting Member
2 Posts |
Posted - 2012-01-30 : 05:04:18
|
Hello,I have a Table named TH_Ventas, and this table I have to count and sum a row.I need to get this count and sum of 2010, 2011 and 2012.I am joining the same table 3 times to get this values, and I have the same values with this query:SELECT a.hdart, sum(distinct a.hdinet), count(distinct a.hdinet), sum(distinct b.hdinet), count(distinct b.hdinet), sum(distinct c.hdinet), count( distinct c.hdinet) FROM [QSOLAPBD_015].[dbo].[TH_Ventas] a,[QSOLAPBD_015].[dbo].[TH_Ventas] b, [QSOLAPBD_015].[dbo].[TH_Ventas] cwhere a.hcdid = b.hcdidand a.hcdid = c.hcdidand a.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and b.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and c.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') group by a.hdartNow when I want to filter the table, with this:SELECT a.hdart, sum(distinct a.hdinet), count(distinct a.hdinet), sum(distinct b.hdinet), count(distinct b.hdinet), sum(distinct c.hdinet), count( distinct c.hdinet) FROM [QSOLAPBD_015].[dbo].[TH_Ventas] a,[QSOLAPBD_015].[dbo].[TH_Ventas] b, [QSOLAPBD_015].[dbo].[TH_Ventas] cwhere a.hcdid = b.hcdidand a.hcdid = c.hcdidand a.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and b.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and c.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) like '2010' and SUBSTRING(CONVERT(VARCHAR, b.fechadoc), 0, 5) like '2011' and SUBSTRING(CONVERT(VARCHAR, c.fechadoc), 0, 5) like '2012' group by a.hdartReturn 0 rows, Do you guys know how I cant get the same table, with sum of diferent years? |
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2012-01-30 : 05:33:46
|
Should 'AND' be used for your filters? Or do you want it to be either 2010 or 2011 or 2012?where a.hcdid = b.hcdidand a.hcdid = c.hcdidand a.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and b.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and c.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010') and ((SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) like '2010' OR SUBSTRING(CONVERT(VARCHAR, b.fechadoc), 0, 5) like '2011' OR SUBSTRING(CONVERT(VARCHAR, c.fechadoc), 0, 5) like '2012' ))Also are you using the like statement correctly? are you maybe looking for like '%2010%'I also prefer using CHARINDEX as opposed to LIKE - it seems to perform better.Duane. |
 |
|
Isianfas
Starting Member
2 Posts |
Posted - 2012-01-30 : 05:49:37
|
I think I have the solution.What do you whink guys?SELECT a.hdart, sum(distinct CASE WHEN SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2010' THEN a.hdinet ELSE 0 END), sum(distinct CASE WHEN SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2010' THEN a.hdcan ELSE 0 END), sum(distinct CASE WHEN SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2011' THEN a.hdinet ELSE 0 END), sum(distinct CASE WHEN SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2011' THEN a.hdcan ELSE 0 END), sum(distinct CASE WHEN SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2012' THEN a.hdinet ELSE 0 END), sum(distinct CASE WHEN SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2012' THEN a.hdcan ELSE 0 END) FROM [QSOLAPBD_015].[dbo].[TH_Ventas] awhere a.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010')and(SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2010' orSUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2011' orSUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2012')group by a.hdart |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-30 : 11:25:05
|
is a.fechadoc a datetime column?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|