Complex Query - 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

Complex Query

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2011-11-17 : 08:26:44

Hello friends,
I have the following diagram as attached below
I need to create the following result based on the client ID

ClientID-Date-BillNum-AmountLBP(billdetail)-AmountUSD(billdetail)-AmountLBP(ClientPayments)-AmountUSD(ClientPayments)

Thanks in advance for your help

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-17 : 09:35:46

What have you tried so far? This is in no way complex. It is the most basic of the basics...

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

daniel.nospam
Starting Member

11 Posts

Posted - 2011-11-17 : 09:40:54

How does ClientPauments relates to Bills? Is it via ClientID and BillNumber?

mike1973
Starting Member

46 Posts

Posted - 2011-11-17 : 11:27:14

It's just via the ClientID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:29:38

have a look at concept of joins and you can very easily get this on your own

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

mike1973
Starting Member

46 Posts

Posted - 2011-11-17 : 11:44:12

here is what i wrote but the columns results in some cases should be zero because the rows are not the same for the same client (clientpayments and billdetails)

Select BI.BillNumber, BI.BillFile,BI.ClientID, CL.ClientName,BD.AmountLL, BD.AmountUSD, CP.ValueUSD,CP.ValueLBP
from Bills AS BI
JOIN Clients CL on BI.ClientID=CL.ClientID
JOIN BillDetails BD on BI.BillID=BD.BillID
JOIN ClientPayments CP on BI.ClientID=CP.ClientID
where BI.Hidden=0 and BI.ClientID=20 and CP.ClientID=20

Result

12 1 20 ???????? ??????? 500000 100 5000.00 20000.00
12 1 20 ???????? ??????? 500000 100 200.00 10000.00
12 1 20 ???????? ??????? 200000 100 5000.00 20000.00
12 1 20 ???????? ??????? 200000 100 200.00 10000.00

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-17 : 11:50:34

Without sample data, we have no way of knowing why you get the results that you do. Follow the HOW TO ASK link, and it will provide you with instructions on how to provide DDL and DML.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mike1973
Starting Member

46 Posts

Posted - 2011-11-17 : 12:17:53

DB Diagram:

Bills Data Sample:
11-7/13/2011-12-1-20-dfgsd-4-1-1-5-444.00000-444.00000-45434534-7/13/2011-543453453-0-0-NULL-NULL-False

BillDetails Data Sample:
22-11-1-500000-100-NULL-NULL-NULL-3
23-11-1-200000-100-NULL-NULL-NULL-1

ClientsPayments Data Sample:
1-20-12-NULL-21-Aug-11-test-5000.0000-20000.0000
2-20-12-NULL-21-Aug-11-sdfsdfsdf-200.0000-10000.0000

Expected Result:
BillNumber-BillFile-ClientID-ClientName-AmountLL-AmountUSD-ValueUSD-ValueLBP
12-1-20-ClientName-500000.00-100.00-NULL-NULL
12-1-20-ClientName-200000.00-100.00-NULL-NULL
12-1-20-ClientName-NULL-NULL-5000.00-20000.00
12-1-20-ClientName-NULL-NULL-200.00-10000.00

ClientID=20

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 12:45:04

[code]
SELECT *
FROM
(
SELECT b.BillNumber,b.BillFile,b.ClientID,c.ClientName,bd.AmountLL,bd.AmuntUSD,CAST(NULL AS Numeric(15,2)) AS ValueUSD,CAST(NULL AS Numeric(15,2)) AS ValueLBP,1 AS Ord
FROM Bills b
INNER JOIN BillDetails bd
On bd.BillID = b.BillID
INNER JOIN Clients c
On c.ClientID = b.ClientID
UNION ALL
SELECT b.BillNumber,b.BillFile,b.ClientID,c.ClientName,NULL,NULL,cp.ValueUSD,cp.ValueLBP,2
FROM Bills b
INNER JOIN ClientsPayments cp
ON cp.ClientID = b.ClientID
INNER JOIN Clients c
On c.ClientID = b.ClientID
)t
ORDER BY BillNumber,BillFile,ClientID,ClientName,Ord
[/code]

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-17 : 12:47:06

try this

SELECT
	cp.billnumber, b.billfile, cp.clientid
	, c.clientname, bd.amountLL, bd.amountUSD
	, cp.valueUSD, cp.valueLBP
FROM
	clientpayments cp
INNER JOIN
	clients c 
ON
	cp.Clientid = c.Clientid
INNER JOIN
	bills b 
ON
	cp.Billnumber = b.billnumber
AND
	cp.clientID = b.clientID
LEFT JOIN
	billdetails bd
ON
	b.billID = bd.billID
-- to get your client
WHERE
	c.clientID = 20
AND
	b.hidden = 0

EDIT: of course visahk posts correctly before i do

mike1973
Starting Member

46 Posts

Posted - 2011-11-18 : 11:48:01

Hi DonAtWork, the query didn't return anything

Bills Table:
29 11/18/2011 1 1 45 5 12 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
30 11/18/2011 1 2 45 8 20 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
31 11/18/2011 1 3 35 5 34 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
32 11/18/2011 1 4 45 5 10 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
33 11/18/2011 1 5 45 5 12 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
34 11/18/2011 2 6 45 5 7 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
35 11/18/2011 3 7 45 5 35 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False
36 11/18/2011 4 8 45 5 16 NULL NULL NULL NULL NULL NULL 0 0 NULL NULL False

BillDetails:
27 36 1 1 1 NULL NULL NULL 1

ClientPayments:
1 20 12 NULL 21-Aug-11 5000.0000 20000.0000
2 20 12 NULL 21-Aug-11 200.0000 10000.0000
3 20 45 NULL 11/17/2011 0.0000 500000.0000
4 27 NULL 11/17/2011 0.0000 523000.0000
5 25 NULL 11/17/2011 0.0000 20.0000
6 53 NULL 11/18/2011 1.0000 20.0000
7 45 NULL 11/18/2011 5.0000 5.0000

expected results:

ClientID ClientName AmountLL AmountUSD ValueLBP ValueUSD
45 Name 1 1 NULL NULL
45 name NULL NULL 5000 5000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 11:52:04

you mean you dont have records in BillDetails for all the bills?

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

mike1973
Starting Member

46 Posts

Posted - 2011-11-18 : 11:56:31

Yes i have just 1 record and it's related to clientID(as billnumber can be optional)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:06:06

quote:
Originally posted by mike1973

Yes i have just 1 record and it's related to clientID(as billnumber can be optional)

ok ... can you explain how you will get above output with given data?

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

mike1973
Starting Member

46 Posts

Posted - 2011-11-18 : 12:11:16

I have a selected ClientID which exists in both tables (ClientPayments and Bills >> BillDetails)
the requirement is to select a client and to display (what we paid for him=bill>>>billdetails) and (what he paid for us=clientpayments), let's ignore the billnumber.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 12:53:05

do you mean this then?


;WITH CTE ()
AS
(
SELECT b.BillNumber,b.BillFile,b.ClientID,c.ClientName,bd.AmountLL,bd.AmuntUSD,CAST(NULL AS Numeric(15,2)) AS ValueUSD,CAST(NULL AS Numeric(15,2)) AS ValueLBP,1 AS Ord
FROM Bills b
INNER JOIN BillDetails bd
On bd.BillID = b.BillID
INNER JOIN Clients c
On c.ClientID = b.ClientID
UNION ALL
SELECT  b.BillNumber,b.BillFile,b.ClientID,c.ClientName,NULL,NULL,cp.ValueUSD,cp.ValueLBP,2
FROM Bills b
INNER JOIN ClientsPayments cp
ON cp.ClientID = b.ClientID
INNER JOIN Clients c
On c.ClientID = b.ClientID
)

SELECT c.*
FROM CTE c
INNER JOIN (SELECT ClientID,COUNT(DISTINCT Ord) AS DistOrd
            FROM CTE
            GROUP BY ClientID) c1
ON c1.ClientID = c.ClientID
AND c1.DistOrd =2

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

mike1973
Starting Member

46 Posts

Posted - 2011-11-18 : 13:21:50

Here is the result i received, i even tried to remove the billnumber and billfile and it's not giving me the result
45 ClientName 1 1 NULL NULL 1
45 ClientName NULL NULL 5.0000 5.0000 2
45 ClientName NULL NULL 5.0000 5.0000 2
45 ClientName NULL NULL 5.0000 5.0000 2
45 ClientName NULL NULL 5.0000 5.0000 2
45 ClientName NULL NULL 5.0000 5.0000 2
45 ClientName NULL NULL 5.0000 5.0000 2
45 ClientName NULL NULL 5.0000 5.0000 2

it should be like this
45 ClientName 1 1 NULL NULL
45 ClientName NULL NULL 5.0000 5.0000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 01:24:25

[code]
;WITH CTE ()
AS
(
SELECT b.ClientID,c.ClientName,bd.AmountLL,bd.AmuntUSD,CAST(NULL AS Numeric(15,2)) AS ValueUSD,CAST(NULL AS Numeric(15,2)) AS ValueLBP,1 AS Ord
FROM Bills b
INNER JOIN BillDetails bd
On bd.BillID = b.BillID
INNER JOIN Clients c
On c.ClientID = b.ClientID
UNION
SELECT b.ClientID,c.ClientName,NULL,NULL,cp.ValueUSD,cp.ValueLBP,2
FROM Bills b
INNER JOIN ClientsPayments cp
ON cp.ClientID = b.ClientID
INNER JOIN Clients c
On c.ClientID = b.ClientID
)

SELECT c.*
FROM CTE c
INNER JOIN (SELECT ClientID,COUNT(DISTINCT Ord) AS DistOrd
FROM CTE
GROUP BY ClientID) c1
ON c1.ClientID = c.ClientID
AND c1.DistOrd =2
[/code]

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

mike1973
Starting Member

46 Posts

Posted - 2011-11-20 : 03:39:31

Thanks a lot Visakh, it's working for me i appreciate your help now the query is like this

SELECT b.ClientID,c.ClientName,b.billnumber,ISNULL(bd.AmountLL, 0) As AmountLL ,ISNULL(bd.AmountUSD, 0) As AmountUSD,CAST(NULL AS Numeric(15,2)) AS ValueUSD,CAST(NULL AS Numeric(15,2)) 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=20 and b.Hidden=0
UNION
SELECT cp.ClientID,c.ClientName,cp.BillNumber,NULL,NULL,cp.ValueUSD,cp.ValueLBP,cp.PaymentDate,cp.PaymentDescription,2
FROM ClientPayments cp
INNER JOIN Clients c On c.ClientID = cp.ClientID
Where cp.ClientID=20

if i want to add 2 columns to the result BalanceLBP = (AmountLL - ValueLBP) and another one BalanceUSD = (AmountUSD - ValueUSD)
how i do so please?

thanks for your help again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 03:43:36

do you mean this?


;WITH CTE ()
AS
(
SELECT b.ClientID,c.ClientName,bd.AmountLL,bd.AmuntUSD,CAST(NULL AS Numeric(15,2)) AS ValueUSD,CAST(NULL AS Numeric(15,2)) AS ValueLBP,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=20 and b.Hidden=0
UNION 
SELECT  b.ClientID,c.ClientName,NULL,NULL,cp.ValueUSD,cp.ValueLBP,2
FROM Bills b
INNER JOIN ClientsPayments cp
ON cp.ClientID = b.ClientID
INNER JOIN Clients c
On c.ClientID = b.ClientID
Where cp.ClientID=20
)

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

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

mike1973
Starting Member

46 Posts

Posted - 2011-11-20 : 05:54:12

Dear Visakh

looks good but it is showing me the same numbers when substracting like this

17037.4824 345372.6434
17037.4824 345372.6434
17037.4824 345372.6434
17037.4824 345372.6434
17037.4824 345372.6434

because it is SUM(AmountLL) - SUM(ValueLBP) AS BalanceLBP, SUM(AmountUSD)-SUM(ValueUSD) AS BalanceUSD
while i need it like this but not working (AmountLL - ValueLBP) AS BalanceLBP, (AmountUSD-ValueUSD) AS BalanceUSD

Thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources