Problem with SQL query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with SQL query

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] c
where a.hcdid = b.hcdid
and a.hcdid = c.hcdid
and 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.hdart

Now 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] c
where a.hcdid = b.hcdid
and a.hcdid = c.hcdid
and 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.hdart

Return 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.hcdid
and a.hcdid = c.hcdid
and 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] a
where a.hdart in (Select codarticulo from [QSOLAPBD_015].[dbo].[TH_PrevArticulos] where codgrupoestadistico = '010')
and(
SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2010' or
SUBSTRING(CONVERT(VARCHAR, a.fechadoc), 0, 5) = '2011' or
SUBSTRING(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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources