SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Wrong Results in GROUP BY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nvoyatzopoulos
Starting Member

Greece
7 Posts

Posted - 03/11/2013 :  04:58:43  Show Profile  Reply with Quote
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
52325 Posts

Posted - 03/11/2013 :  06:23:21  Show Profile  Reply with Quote
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/

Go to Top of Page

nvoyatzopoulos
Starting Member

Greece
7 Posts

Posted - 03/11/2013 :  06:34:13  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/11/2013 :  06:36:07  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000