| Author |
 Topic  |
|
|
nvoyatzopoulos
Starting Member
Greece
7 Posts |
 Posted - 03/11/2013 : 04:58:43
|
Select CMPIMGSIZESCATDT.CISCODE,
CMPIMGSIZES.DESCR,
CMPIMGGENCAT.DESCR,
CMPIMGSIZESCAT.DESCR,
Sum(Case CMPIMGSIZESDT.CRDB When 0 Then A.XRE * CMPIMGSIZESDT.MSIGN
When 1 Then A.PIS * CMPIMGSIZESDT.MSIGN
When 2 Then (A.XRE - A.PIS) * CMPIMGSIZESDT.MSIGN When 3 Then (Case
When (A.XRE - A.PIS) > 0 Then Abs(A.XRE - A.PIS) * CMPIMGSIZESDT.MSIGN
Else 0 End) When 4 Then (Case
When (A.XRE - A.PIS) < 0 Then Abs(A.XRE - A.PIS) * CMPIMGSIZESDT.MSIGN
Else 0 End) End) AMT
From CMPIMGSIZES Inner Join
CMPIMGSIZESDT On CMPIMGSIZES.CISCODE = CMPIMGSIZESDT.CISCODE And
CMPIMGSIZES.CMPCODE = CMPIMGSIZESDT.CMPCODE Inner Join
CMPIMGSIZESCATDT On CMPIMGSIZESDT.CISCODE = CMPIMGSIZESCATDT.CISCODE
And CMPIMGSIZESDT.CMPCODE = CMPIMGSIZESCATDT.CMPCODE Inner Join
CMPIMGSIZESCAT On CMPIMGSIZESCATDT.CMPCODE = CMPIMGSIZESCAT.CMPCODE And
CMPIMGSIZESCATDT.CISCCODE = CMPIMGSIZESCAT.CISCCODE Inner Join
CMPIMGGENCATDT On CMPIMGSIZESCATDT.CISCCODE = CMPIMGGENCATDT.CISCCODE And
CMPIMGSIZESCATDT.CMPCODE = CMPIMGGENCATDT.CMPCODE Inner Join
CMPIMGGENCAT On CMPIMGGENCATDT.CISGCODE = CMPIMGGENCAT.CISGCODE And
CMPIMGGENCAT.CMPCODE = CMPIMGGENCATDT.CMPCODE Inner Join
(Select CMPIMGSIZESDT.CISCODE,
CMPIMGSIZESDT.CRDB,
TRNS.LCODE,
CMPIMGSIZESDT.MSIGN,
Sum(TRNS.AMOUNT * (1 - TRNS.CRDB)) XRE,
Sum(TRNS.AMOUNT * TRNS.CRDB) PIS
From CMPIMGSIZESDT,
TRNS,
ARTS
Left Join
GLPAR On GLPAR.CMPCODE = ARTS.CMPCODE And GLPAR.MTYPE = ARTS.MTYPE
Where TRNS.CMPCODE = CMPIMGSIZESDT.CMPCODE And ARTS.ARTID = TRNS.ARTID And
ARTS.CMPCODE = TRNS.CMPCODE And TRNS.LCODE Like CMPIMGSIZESDT.LCODE + '%'
And ((GLPAR.TRNSTYPE In (0, 1, 2, 3) Or (ARTS.MTYPE In (0, 1, 2, 3))) And
(ARTS.CMPCODE = 126) And (( -2 In ( -2) And ARTS.INBRCODE Is Null) Or
ARTS.INBRCODE In ( -2)))
Group By CMPIMGSIZESDT.CISCODE, CMPIMGSIZESDT.CRDB, TRNS.LCODE,
CMPIMGSIZESDT.MSIGN
Union
Select CMPIMGSIZESDT.CISCODE,
CMPIMGSIZESDT.CRDB,
ALTRNS.LCODE,
CMPIMGSIZESDT.MSIGN,
Sum(ALTRNS.AMOUNT * (1 - ALTRNS.CRDB)) XRE,
Sum(ALTRNS.AMOUNT * ALTRNS.CRDB) PIS
From CMPIMGSIZESDT,
ALTRNS,
ALARTS,
ALPAR
Where ALTRNS.CMPCODE = CMPIMGSIZESDT.CMPCODE And ALARTS.ALARTID =
ALTRNS.ALARTID And ALARTS.CMPCODE = ALTRNS.CMPCODE And
ALPAR.MTYPE = ALARTS.MTYPE And ALPAR.CMPCODE = ALARTS.CMPCODE And
ALTRNS.LCODE Like CMPIMGSIZESDT.LCODE + '%' And ALPAR.TRNSTYPE In (0, 1,
2, 3) And ALARTS.CMPCODE = 126
Group By CMPIMGSIZESDT.CISCODE, CMPIMGSIZESDT.CRDB, ALTRNS.LCODE,
CMPIMGSIZESDT.MSIGN) A On A.CISCODE = CMPIMGSIZESDT.CISCODE
Where CMPIMGSIZESDT.CMPCODE = 126
Group By CMPIMGSIZESCATDT.CISCODE, CMPIMGSIZES.DESCR, CMPIMGGENCAT.DESCR,
CMPIMGSIZESCAT.DESCR
Hello everybody ,I'm trying to get results from this script but its all wrong ! The AMT is the field with number but it is repeated multiple times ! Please help ! |
Edited by - nvoyatzopoulos on 03/11/2013 06:18:17
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 03/11/2013 : 06:23:21
|
can you show some sample data and explain your issue?
We cant understand anything from your above query as we dont know how data exists in table and also whats the output you're trying to get.
follow below guideline for posting the data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
nvoyatzopoulos
Starting Member
Greece
7 Posts |
Posted - 03/11/2013 : 06:34:13
|
ciscode amt
4 9684,49
7 6701,05
15 5045,6
25 7873,4
29 1199,3
30 1811,09
57 17,56
63 111,39
64 210
72 60
73 57,2
136 2371,65
this the result i want to get with some other information (descr) . the strange think is that some nymber are correct and some other not .(it multiplies them) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 03/11/2013 : 06:36:07
|
please follow the link and post data in required easy consumable format as insert statements. include sample data as well as your required output.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Wrong Results in GROUP BY
