problems with shown data - 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)

problems with shown data

Author

Topic

edelgado
Starting Member

2 Posts

Posted - 2011-11-16 : 19:38:23

Good evening:

I have two tables named [@sys_pdevoluciones] and [@sys_psucursales]

they can be joined with [@sys_pdevoluciones].u_sys_sucu = [@sys_psucursales].code

when I run the following query...

select t0.code, t0.name from [@sys_psucursales]

...I get this:

Code Name
002 CABANAS
003 MESONES
004 CORREO MAYOR
005 LOS REYES
006 CHALCO
007 LEON
008 CELAYA
009 CENTRO
010 GONZALEZ ORTEGA
011 SANTA TERE
012 CENTRO DE DISTRIBUCION
013 QUERETARO

Those are my client's stores. But I need to check the returns and credit notes totals, I use the following query:

select SUM(CASE WHEN T4.Name LIKE '%D%' THEN T4.U_SYS_TOTN/1.16 ELSE 0 END) AS 'DEV',
SUM(CASE WHEN T4.NAME LIKE '%N%' THEN T4.U_SYS_TOTN/1.16 ELSE 0 END) AS 'NC', T5.Code
from [@SYS_PDEVOLUCIONES] T4, [@SYS_PSUCURSALES] T5
where T5.Code = T4.U_SYS_SUCU
group by T5.Code

... and get this

DEV NC Code
2820.6379310343 2152.1293103447 002
488.8534482756 0.0000000000 003
932.5775862068 0.0000000000 004
107.1120689652 0.0000000000 005
272.8103448275 0.0000000000 006
19.6551724137 110.8534482758 008
596.1637931031 0.0000000000 009
832.8275862068 0.0000000000 010
0.0000000000 360.3620689655 011

However, what I need is this

DEV NC Code
2820.6379310343 2152.1293103447 002
488.8534482756 0.0000000000 003
932.5775862068 0.0000000000 004
107.1120689652 0.0000000000 005
272.8103448275 0.0000000000 006
0.0000000000 0.0000000000 007
19.6551724137 110.8534482758 008
596.1637931031 0.0000000000 009
832.8275862068 0.0000000000 010
0.0000000000 360.3620689655 011
0.0000000000 0.0000000000 013

Note that 012 is not present because it is a consolidator, not a real store, that's why I don't care for it. 007 and 013 are not present because there are no returns ot credit notes done by those stores.

I've tried to use unions and changing my where clause, but i've only managed to multiply data or add duplicate rows for the data of a specific store.

What I need to do here is to be able to check the totals by a specific range of dates, which, I will add after I solve this (T4.U_SYS_FECH). But right now what I need the most is to add a row containing 0, 0 and code when the store has no data for that specific range of dates.

Sorry for the long question, but I need to be very specific to get help faster.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-16 : 20:08:46

Sounds like you need use an OUTER JOIN. On the "right" table where there is no match for the "left" table, you'll have a NULL value, so replace that with 0. First change your query to use OUTER JOIN, and then it'll be more clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

edelgado
Starting Member

2 Posts

Posted - 2011-11-17 : 12:43:03

quote:
Originally posted by tkizer

Sounds like you need use an OUTER JOIN. On the "right" table where there is no match for the "left" table, you'll have a NULL value, so replace that with 0. First change your query to use OUTER JOIN, and then it'll be more clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thanks a lot, sir. Tried right outer join and it worked like a charm. This has been bugging me for a long time!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-17 : 13:28:05