November 16, 2011 at 5:46 pm
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.63793103432152.1293103447002
488.85344827560.0000000000003
932.57758620680.0000000000004
107.11206896520.0000000000005
272.81034482750.0000000000006
19.6551724137110.8534482758008
596.16379310310.0000000000009
832.82758620680.0000000000010
0.0000000000360.3620689655011
However, what I need is this
DEV NC Code
2820.63793103432152.1293103447002
488.85344827560.0000000000003
932.57758620680.0000000000004
107.11206896520.0000000000005
272.81034482750.0000000000006
0.00000000000.0000000000007
19.6551724137110.8534482758008
596.16379310310.0000000000009
832.82758620680.0000000000010
0.0000000000360.3620689655011
0.00000000000.0000000000013
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 or 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.
November 17, 2011 at 1:43 am
Try this
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
LEFT OUTER JOIN [@SYS_PSUCURSALES] T5
ON T5.Code = T4.U_SYS_SUCU
group by T5.Code
Without your table definitions and data I can't be sure it will create what you want or even work, but it seems you want to include rows from SYS_PDEVOLUCIONES which do not have any entries in SYS_PSUCURSALES. OUTER JOIN is the way to do this.
November 17, 2011 at 10:14 am
Tom Brown (11/17/2011)
Try this
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
LEFT OUTER JOIN [@SYS_PSUCURSALES] T5
ON T5.Code = T4.U_SYS_SUCU
group by T5.Code
Without your table definitions and data I can't be sure it will create what you want or even work, but it seems you want to include rows from SYS_PDEVOLUCIONES which do not have any entries in SYS_PSUCURSALES. OUTER JOIN is the way to do this.
Tried
[@SYS_PDEVOLUCIONES] T4
right OUTER JOIN [@SYS_PSUCURSALES] T5
ON T5.Code = T4.U_SYS_SUCU
Worked like a charm, thanks a lot, sir. This has been bugging me for quite a long time!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply