Help in SUM - 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

General SQL Server Forums

New to SQL Server Programming

Help in SUM

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2012-01-15 : 05:10:14

Hi, please guys i need help to modifiy this query

WITH CTE
AS
(
SELECT b.ClientID,c.ClientName,b.billnumber,ISNULL(bd.AmountLL, 0) As AmountLL ,ISNULL(bd.AmountUSD, 0) As AmountUSD,ISNULL(CAST(NULL AS Numeric(15,2)),0) AS ValueUSD,ISNULL(CAST(NULL AS Numeric(15,2)),0) AS ValueLBP,CONVERT(VARCHAR(10),bd.ModifiedDate,110) AS ModifiedDate,bd.Notes,1 AS Ord
FROM Bills b
INNER JOIN BillDetails bd On bd.BillID = b.BillID
INNER JOIN Clients c On c.ClientID = b.ClientID
Where b.ClientID=@ClientID and b.Hidden=0
UNION
SELECT cp.ClientID,c.ClientName,cp.BillNumber,ISNULL(NULL,0),ISNULL(NULL,0),ISNULL(cp.ValueUSD,0),ISNULL(cp.ValueLBP,0),cp.PaymentDate,cp.PaymentDescription,2
FROM ClientPayments cp
INNER JOIN Clients c On c.ClientID = cp.ClientID
Where cp.ClientID=@ClientID
)

SELECT c.*,c.AmountLL-c.ValueLBP AS BalanceLBP,AmountUSD-ValueUSD AS BalanceUSD
FROM CTE c
LEFT JOIN (SELECT ClientID,COUNT(DISTINCT Ord) AS DistOrd
FROM CTE
GROUP BY ClientID) c1
ON c1.ClientID = c.ClientID
AND c1.DistOrd =2

This query is returning the following results:

what i need to do is the following:
instead of displaying all the items in the column INV/LBP and INV/USD, i need to display the SUM so i won't have 4 rows, i will have 1 row where the code is the same
in this case i will have 150.000 in the INV/LBP and 110 in the USD/INV

is this doable?

Thanks a lot for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 05:14:14

what about the other fields? when you make them as 1 , you've currently four rows with different Description which need to be made as one. In such case you can show only one of desciptions. So what should be displayed description value in that case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mike1973
Starting Member

46 Posts

Posted - 2012-01-15 : 05:24:05

Hi Visakh,

I don't need the description column, i can remove it.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 10:14:31

[code]
WITH CTE
AS
(
SELECT b.ClientID,c.ClientName,b.billnumber,ISNULL(bd.AmountLL, 0) As AmountLL ,ISNULL(bd.AmountUSD, 0) As AmountUSD,ISNULL(CAST(NULL AS Numeric(15,2)),0) AS ValueUSD,ISNULL(CAST(NULL AS Numeric(15,2)),0) AS ValueLBP,CONVERT(VARCHAR(10),bd.ModifiedDate,110) AS ModifiedDate,bd.Notes,1 AS Ord
FROM Bills b
INNER JOIN BillDetails bd On bd.BillID = b.BillID
INNER JOIN Clients c On c.ClientID = b.ClientID
Where b.ClientID=@ClientID and b.Hidden=0
UNION
SELECT cp.ClientID,c.ClientName,cp.BillNumber,ISNULL(NULL,0),ISNULL(NULL,0),ISNULL(cp.ValueUSD,0),ISNULL(cp.ValueLBP,0),cp.PaymentDate,cp.PaymentDescription,2
FROM ClientPayments cp
INNER JOIN Clients c On c.ClientID = cp.ClientID
Where cp.ClientID=@ClientID
)

SELECT c.[Date],,SUM(c.AmountLL-c.ValueLBP) AS BalanceLBP,SUM(AmountUSD-ValueUSD) AS BalanceUSD
FROM CTE c
LEFT JOIN (SELECT ClientID,COUNT(DISTINCT Ord) AS DistOrd
FROM CTE
GROUP BY ClientID) c1
ON c1.ClientID = c.ClientID
AND c1.DistOrd =2
GROUP BY c.[Date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mike1973
Starting Member

46 Posts

Posted - 2012-01-15 : 12:19:27

Hi Visakh,

can you please take a look at the results and how it should looks like?

i executed the previous query and it didn't return the correct data presentation

thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 13:20:54

you only told you dont want description and posted output shows description also. Didnt understand what exactly you're expecting.perhaps you can explain with some sample data rather than keeping us guessing!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mike1973
Starting Member

46 Posts

Posted - 2012-01-15 : 18:24:02

Hi Visakh,

I apologize for the missunderstanding and i'll try to explain more the output. i added the diagram for the database also

DATE - CODE - INV/LBP - INV/USD - CREDIT/LBP - CREDIT/USD - BALANCE/LBP - BALANCE/USD

DATE: is the ClientsPayments.PaymentDATE
CODE: is the Bills.BillNumber
INV/LBP: is SUM (BillDetails.AmountLL) Where BillDetails.BillID=Bills.BillID AND Bills.ClientID=@ClientID
INV/USD: is SUM (BillDetails.AmountUSD) Where BillDetails.BillID=Bills.BillID AND Bills.ClientID=@ClientID
CREDIT/LBP: is ClientsPayments.ValueLBP where ClientsPayments.ClientID=@ClientID
CREDIT/USD: is ClientsPayments.ValueUSD where ClientsPayments.ClientID=@ClientID

BALANCE/LBP: is c.AmountLL-c.ValueLBP
BALANCE/LBP: is AmountUSD-ValueUSD

WHERE ClientID=@ClientID and Bills.Hidden=0

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 22:48:23

your explanation is fine but i still didnt understand how you want to merge those rows. what combination of columns values you need to consider for merging?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mike1973
Starting Member

46 Posts

Posted - 2012-01-16 : 02:59:41

For the ClientsPayments the criteria is the @ClientID only
and for the Billing Data, is the same @clientID and i start summing all the records by BILLID for that @ClientID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 03:07:07

where's billid? its not even there in output
how do you expect someone who has no access to your system to understand what you're looking at with details provided?

Please provide sample data and required output if you really want someone to help you out.

see guidelines on how to post question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources