sql? - 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 2008 Forums

Transact-SQL (2008)

sql?

Author

Topic

diarul
Starting Member

1 Post

Posted - 2013-07-15 : 19:48:23

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-15 : 21:03:27

[CODE]

;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

[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-15 : 23:49:39

[code]; 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[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 02:09:39

[code]
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
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-16 : 07:58:24

[code]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;[/code]

N 56°04'39.26"
E 12°55'05.63"

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-17 : 20:59:05

khtan's and SwePeso's are the best.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 05:54:20

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 06:58:18

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-07-18 : 08:30:37

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources