SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 sql?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

diarul
Starting Member

1 Posts

Posted - 07/15/2013 :  19:48:23  Show Profile  Reply with Quote
Can someone review this code below and let me know if there's a better way to write the same sql. The sql scans the same table twice...is there a way to avoid it. Using sql2008. Thank you.

The idea is to report ;

1. customers last order with the amount spent on the order

2. the total amount spent thus far by the customer




create table orders
(order_no varchar(3), customer_id varchar(3), product varchar(3), order_date datetime)
create table far
(order_no varchar(3), customer_id varchar(3), amount int)

insert into orders
values ('101',333,'ABC','2/1/2013'),
('102',333,'ABC','1/1/2013'),
('103',334,'ABC','5/1/2013'),
('104',333,'ABC','6/1/2013')
insert into far
values ('101',333,50),
('102',333,55),
('103',334,100),
('104',333,85)


WITH cte
AS (SELECT D.customer_id, T.order_no, T.amount, order_date,
Row_number () OVER ( partition BY T.customer_id ORDER BY T.order_no DESC) RN
FROM orders D, far T
WHERE product = 'ABC'
AND t.order_no = d.order_no),
total
AS (SELECT D.customer_id, Abs(Sum(T.amount)) total_amount
FROM orders D, far T
WHERE product = 'ABC'
AND t.order_no = d.order_no
GROUP BY d.customer_id)
SELECT a.customer_id,
order_no,
Abs(Sum(amount)) last_amount,
Max(order_date) last_date,
total_amount
FROM cte a,
total b
WHERE a.customer_id = b.customer_id
AND rn = 1
GROUP BY a.customer_id,
a.order_no,
total_amount

Edited by - diarul on 07/15/2013 20:50:09

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/15/2013 :  21:03:27  Show Profile  Reply with Quote


;WITH cte 
	AS (SELECT D.customer_id, T.order_no, T.amount, order_date, 
			Row_number () OVER ( partition BY T.customer_id ORDER BY T.order_no DESC) RN 
				FROM orders D, far T 
				WHERE product = 'ABC' 
				AND t.order_no = d.order_no), 
 total 
 AS (SELECT T.customer_id, Abs(Sum(T.amount)) total_amount 
				FROM  far T 
				GROUP BY T.customer_id) 
SELECT a.customer_id, order_no, Abs(amount) last_amount, 
		(order_date) last_date, total_amount 
	FROM cte a, total b
	WHERE a.customer_id = b.customer_id AND rn = 1 

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17611 Posts

Posted - 07/15/2013 :  23:49:39  Show Profile  Reply with Quote
; with cte as
(
	select	d.order_no, d.customer_id, t.amount, d.order_date, 
		rn = row_number() over (partition by d.customer_id order by d.order_date desc)
	from	orders d
		inner join far t	on	d.order_no	= t.order_no
)
select	customer_id, 
	order_no	= max(case when rn = 1 then order_no end),
	last_amount	= max(case when rn = 1 then amount end),
	last_date	= max(case when rn = 1 then order_date end),
	total_amount    = sum(amount)
from	cte
group by customer_id



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  02:09:39  Show Profile  Reply with Quote

SELECT customer_id AS CustomerID,
order_no AS LatestOrderNo,
order_date AS LatestOrderDate,
amount AS LatestOrderAmt,
TotalAmt
FROM
(
SELECT o.customer_id,o.order_no,o.order_date,f.amount
SUM(f.amount) OVER (PARTITION BY o.customer_id) AS TotalAmt,
MAX(o.order_date) OVER (PARTITION BY (o.customerid) AS LatestOrderDate
FROM orders o
INNER JOIN far f
ON f.order_no = c.order_no
AND f.customer_id = c.customer_id
)t
WHERE order_date = LatestOrderDate


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/16/2013 :  07:58:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WITH cteSource(Customer_ID, Order_No, Order_Date, Amount, rn, tot)
AS (
	SELECT		o.Customer_ID,
			f.Order_No,
			f.Order_Date,
			f.Amount,
			ROW_NUMBER() OVER (PARTITION BY o.Customer_ID ORDER BY f.Order_No DESC) AS rn,
			SUM(f.Amount) OVER (PARTITION BY o.Customer_ID) AS tot
	FROM		dbo.Far AS f
	INNER JOIN	dbo.Orders AS o ON o.Order_No = f.Order_No
)
SELECT	Customer_ID,
	Order_No,
	ABS(Amount) AS Last_Amount,
	Order_Date AS Last_Date,
	tot AS Total_Amount
FROM	cteSource
WHERE	rn = 1;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/17/2013 :  20:59:05  Show Profile  Reply with Quote
khtan's and SwePeso's are the best.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  05:54:20  Show Profile  Reply with Quote
quote:
Originally posted by sigmas

quote:
Originally posted by visakh16


SELECT customer_id AS CustomerID,
order_no AS LatestOrderNo,
order_date AS LatestOrderDate,
amount AS LatestOrderAmt,
TotalAmt
FROM
(
SELECT o.customer_id,o.order_no,o.order_date,f.amount,
SUM(f.amount) OVER (PARTITION BY o.customer_id) AS TotalAmt,
MAX(o.order_date) OVER (PARTITION BY (o.customerid o.customer_id) AS LatestOrderDate
FROM orders o
INNER JOIN far f
ON f.order_no = c.order_no o.order_no
AND f.customer_id =c.customer_id o.customer_id
)t
WHERE order_date = LatestOrderDate


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




You are really a MVP!


Yeah...wats the problem with it?
Do you think I'm having a sql compiler build into me?
I'm in between my day job and just tried to give suggestion based on logic that happened to me and I dont have a sql box nearby to test and see result for each of the questions.
The intention is to give OP idea on approach. Even if there are some typos which happen to escape my attention how difficult is it for someone to compile and find it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  06:58:18  Show Profile  Reply with Quote
quote:
Originally posted by sigmas

quote:
Originally posted by visakh16

quote:
Originally posted by sigmas

quote:
Originally posted by visakh16


SELECT customer_id AS CustomerID,
order_no AS LatestOrderNo,
order_date AS LatestOrderDate,
amount AS LatestOrderAmt,
TotalAmt
FROM
(
SELECT o.customer_id,o.order_no,o.order_date,f.amount,
SUM(f.amount) OVER (PARTITION BY o.customer_id) AS TotalAmt,
MAX(o.order_date) OVER (PARTITION BY (o.customerid o.customer_id) AS LatestOrderDate
FROM orders o
INNER JOIN far f
ON f.order_no = c.order_no o.order_no
AND f.customer_id =c.customer_id o.customer_id
)t
WHERE order_date = LatestOrderDate


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




You are really a MVP!


Yeah...wats the problem with it?
Do you think I'm having a sql compiler build into me?
I'm in between my day job and just tried to give suggestion based on logic that happened to me and I dont have a sql box nearby to test and see result for each of the questions.
The intention is to give OP idea on approach. Even if there are some typos which happen to escape my attention how difficult is it for someone to compile and find it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



I'm disagree with you, because it maybe be very difficult for OP to find and fix your typos by compiler.
for this reason it's better for you to post a query without syntax error.
no worry. following website help you to check your query syntax. no need any SQL Server software.
http://www.ubitsoft.com/products/t-sql-analyzer/

I have an advice for you: instead post a lot any way (a lot of "welcome"), post considered!


Thanks for the link.
If you can find out the typos, I'm sure OP will also be able to do it
And I dont understand what you meant by the last sentence, may be due to language barriers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 07/18/2013 :  08:30:37  Show Profile  Reply with Quote
You are new to this site. Coming in and telling someone that they are wrong and should not post things in certain ways is rude. This site is used by people for help, and those that give the help have other jobs. It is not always possible to give a PERFECT answer. Again, we try to HELP, not give a total answer to every problem.








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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000