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

SQL Server 2000 Forums

Transact-SQL (2000)

Complex query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-23 : 08:50:45

Carlos writes "Hi,
I'm new to sql server and I would like to know if you guys can help me out. I'm using sql server 2000 on a Windows 2000 server.
I have two tables with the following fields

Order_detail Table

Customer_number
order_number
Start_date
Company_name
Contract_amount

Base_Info Table

Customer_Number
Base_Info

The query that I need is to get the sum of the contract_amount for the
customers from Base_Info table that exits in Order_detail
and have orders where start_date <= '01/02/02'. At the same time sum the contract_amount
for the same customers, but that were placed after start_date '01/02/02'
Is this possible in one select statement?

Thank you in advance..."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-23 : 10:32:01

SELECT Customer_number, Base_Info
SUM(CASE WHEN Start_Date <= '01/02/02' THEN Contract_Amount ELSE 0 END) as TotalBefore,
SUM(CASE WHEN Start_Date > '01/02/02' THEN Contract_Amount ELSE 0 END) as TotalAfter
FROM
Order_Detail
INNER JOIN
Base_Info
ON Order_Detail.Customer_Number = Base_Info.Customer_Number
GROUP BY Customer_Number, Base_Info

- Jeff

Edited by - jsmith8858 on 01/23/2003 10:32:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources