Skip to Content
0
Former Member
May 28, 2013 at 04:45 AM

How to query BasePrise, SalesPrice and GrossProfit by each customers and sum up by each months?

21 Views

Hi all experts, I really need help.

I want to query baseprices , salesprices and grossprofit by each customers and sum up by each months.
Below is my query. but the result that i got from this query is wrong. Maybe i query from the wrong tables.

Any suggestion and ideas? Thanks before hand.

SELECT A.CardCode + ' - ' + A.CardName,

SUM(CASE WHEN c.SubNum=1 THEN (B.GPTtlBasPr) ELSE 0 END) as BJAN,

SUM(CASE WHEN c.SubNum=2 THEN (B.GPTtlBasPr) ELSE 0 END) as BFEB,

SUM(CASE WHEN c.SubNum=3 THEN (B.GPTtlBasPr) ELSE 0 END) as BMarch,

SUM(CASE WHEN c.SubNum=4 THEN (B.GPTtlBasPr) ELSE 0 END) as BAPRIL,

SUM(CASE WHEN c.SubNum=5 THEN (B.GPTtlBasPr) ELSE 0 END) as BMAY,

SUM(CASE WHEN c.SubNum=6 THEN (B.GPTtlBasPr) ELSE 0 END) as BJUNE,

SUM(CASE WHEN c.SubNum=7 THEN (B.GPTtlBasPr) ELSE 0 END) as BJULY,

SUM(CASE WHEN c.SubNum=8 THEN (B.GPTtlBasPr) ELSE 0 END) as BAUG,

SUM(CASE WHEN c.SubNum=9 THEN (B.GPTtlBasPr) ELSE 0 END) as BSEP,

SUM(CASE WHEN c.SubNum=10 THEN (B.GPTtlBasPr) ELSE 0 END) as BOCT,

SUM(CASE WHEN c.SubNum=11 THEN (B.GPTtlBasPr) ELSE 0 END) as BNOV,

SUM(CASE WHEN c.SubNum=12 THEN (B.GPTtlBasPr) ELSE 0 END) as BDEC,

SUM(CASE WHEN c.SubNum=1 THEN (B.Price * B.Quantity) ELSE 0 END) AS SJAN,

SUM(CASE WHEN c.SubNum=2 THEN (B.Price * B.Quantity) ELSE 0 END) AS SFEB,

SUM(CASE WHEN c.SubNum=3 THEN (B.Price * B.Quantity) ELSE 0 END) AS SMarch,

SUM(CASE WHEN c.SubNum=4 THEN (B.Price * B.Quantity) ELSE 0 END) AS SAPRIL,

SUM(CASE WHEN c.SubNum=5 THEN (B.Price * B.Quantity) ELSE 0 END) AS SMAY,

SUM(CASE WHEN c.SubNum=6 THEN (B.Price * B.Quantity) ELSE 0 END) AS SJUNE,

SUM(CASE WHEN c.SubNum=7 THEN (B.Price * B.Quantity) ELSE 0 END) AS SJULY,

SUM(CASE WHEN c.SubNum=8 THEN (B.Price * B.Quantity) ELSE 0 END) AS SAUG,

SUM(CASE WHEN c.SubNum=9 THEN (B.Price * B.Quantity) ELSE 0 END) AS SSEP,

SUM(CASE WHEN c.SubNum=10 THEN (B.Price * B.Quantity) ELSE 0 END) AS SOCT,

SUM(CASE WHEN c.SubNum=11 THEN (B.Price * B.Quantity) ELSE 0 END) AS SNOV,

SUM(CASE WHEN c.SubNum=12 THEN (B.Price * B.Quantity) ELSE 0 END) AS SDEC,

SUM(CASE WHEN c.SubNum=1 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GJAN,

SUM(CASE WHEN c.SubNum=2 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GFEB,

SUM(CASE WHEN c.SubNum=3 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GMarch,

SUM(CASE WHEN c.SubNum=4 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GAPRIL,

SUM(CASE WHEN c.SubNum=5 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GMAY,

SUM(CASE WHEN c.SubNum=6 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GJUNE,

SUM(CASE WHEN c.SubNum=7 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GJULY,

SUM(CASE WHEN c.SubNum=8 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GAUG,

SUM(CASE WHEN c.SubNum=9 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GSEP,

SUM(CASE WHEN c.SubNum=10 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GOCT,

SUM(CASE WHEN c.SubNum=11 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GNOV,

SUM(CASE WHEN c.SubNum=12 THEN (( B.Price * B.Quantity)) - ((B.GPTtlBasPr)) ELSE 0 END) AS GDEC

FROM OINV A INNER JOIN INV1 B ON A.DocEntry = B.DocEntry

LEFT OUTER JOIN OFPR C ON A.FinncPriod = C.AbsEntry

WHERE C.Category = '2012'

Group by A.CardCode,A.CardName order by 1