[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql syntax

Posted on 2009-02-23
12
Medium Priority
?
233 Views
Last Modified: 2012-05-06
lets suppose that we have the following tables
----------------------------------------
trcust (transaction customer)
---------------------------------------
trcust_code
trcust_date
trcust_debit
trcust_credit

the compibantion of trcust_code, trcust_date is the key of trcust

----------------------------
customer
----------------------------
cust_code (key)
cust_firm  

i want to create a select statement that will return the following data

===========================================================
cust_code   cust_firm                   date_a             credit_a          debit_a
===========================================================
  99999  xxxxxxxxxxxxxxx       dd/yy/yyyy    99999999.99   9999999.99

date_a = last transaction date for each customer
credit_a = credit anount in date_a
debit_a =debit anount in date_a

let me remind you that a customer may have one transaction per day only in this example . if a customer has no transactions it should be inclued in the results with zeroes in credit_a, debit_a columns and null for the date_a


0
Comment
Question by:basilhs_s
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23709477
try this
Select C.cust_code , C.cust_firm , T.trcust_date, T.trcust_debit ,T.trcust_credit
 
From customer C inner Join 
(Select T1.trcust_code , Max(T1.trcust_date) From trcust ) T
on C.cust_code = T.trcust_code 

Open in new window

0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 23709564
select c.cust_code, c.cust_firm, t1.trcust_date, nvl(t1.trcust_debit,0) trcust_debit,
nvl(t1.trcust_credit,0) trcust_credit
from customer c,
(select t.trcust_code, t.trcust_date, t.trcust_credit, t.trcust_debit
from trcust t
where
(trcust_code, trcust_date) = (select trcust_code, max(trcust_date) from trcust group by trcust_code)
) t1
where c.cust_code = t1.trcust_code (+)
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 23709578
Sorry should be:
select c.cust_code, c.cust_firm, t1.trcust_date date_a, 
nvl(t1.trcust_debit,0) debit_a, nvl(t1.trcust_credit,0) credit_a
from customer c,
(select t.trcust_code, t.trcust_date, t.trcust_credit, t.trcust_debit
from trcust t where
(trcust_code, trcust_date) = 
(select trcust_code, max(trcust_date) from trcust group by trcust_code)) t1
where c.cust_code = t1.trcust_code (+)

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Expert Comment

by:Peter Kwan
ID: 23709591
A minor change:
select c.cust_code, c.cust_firm, t1.trcust_date date_a, 
nvl(t1.trcust_debit,0) debit_a, nvl(t1.trcust_credit,0) credit_a
from customer c,
(select t.trcust_code, t.trcust_date, t.trcust_credit, t.trcust_debit
from trcust t where
(trcust_code, trcust_date) IN 
(select trcust_code, max(trcust_date) from trcust group by trcust_code)) t1
where c.cust_code = t1.trcust_code (+)

Open in new window

0
 

Author Comment

by:basilhs_s
ID: 23709598
i received the following error message

Server: Msg 107, Level 16, SState 2, Line 1
The column prefix 'T1' does not match with a table name or alias name used in the query.


I am using sql server 2000

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23709640
ohh sorry

try this
Select C.cust_code , C.cust_firm , T.trcust_date, T.trcust_debit ,T.trcust_credit
 
From customer C inner Join 
(Select T1.trcust_code , Max(T1.trcust_date) From trcust T1 ) T
on C.cust_code = T.trcust_code 

Open in new window

0
 

Author Comment

by:basilhs_s
ID: 23709683
thanks pkwan. i tried your solution but it received the folllowing error
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 't1'.
0
 

Author Comment

by:basilhs_s
ID: 23709737
thanks pratima. i am still having an error
No column was specified for column 2 of 'T'.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23709778
Select C.cust_code , C.cust_firm , T.trcust_date, T.trcust_debit ,T.trcust_credit
 
From customer C inner Join
(Select trcust_code , Max(trcust_date) From trcust  ) T
on C.cust_code = T.trcust_code
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23709786

Select C.cust_code , C.cust_firm , T.trcust_date, T.trcust_debit ,T.trcust_credit
 
From customer C inner Join 
(Select trcust_code , Max(trcust_date) From trcust  ) T1 on C.cust_code = T1.trcust_code 
inner join trcust T  on C.cust_code = T.trcust_code 

Open in new window

0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23709799
Try -
SELECT   C.cust_code 
	,C.cust_firm
	,T.trcust_date AS date_a
	,ISNULL(SUM(T.trcust_credit), 0) AS credit_a
	,ISNULL(SUM(T.trcust_debit), 0) AS debit_a
FROM
	customer C
LEFT JOIN 
	trcust T
	ON	T.trcust_code = C.cust_Code
	AND	T.trcust_date = (SELECT MAX(trcust_date) FROM trcust WHERE trcust_code = C.cust_code)
GROUP BY
	 C.cust_code 
	,C.cust_firm
	,T.trcust_date

Open in new window

0
 

Author Closing Comment

by:basilhs_s
ID: 31549986
perfect solution
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question