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
 General SQL Server Forums
 New to SQL Server Programming
 Last transaction for customer + item
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GuyZommer
Starting Member

2 Posts

Posted - 01/14/2013 :  01:11:15  Show Profile  Reply with Quote
Hello,

I have sales transaction table that have the follwing fields:
BDCUST (Cust number)
BDCAT (Item number)
BDQTY (Quantity)
BDVUC (Net price)
BDTOPN(Invoice date)

What I need is to present the last transaction for each customer + item
I tried the following but I get duplication

SELECT BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC, Max(BD.BDTOPN)
FROM GALF2.BD BD

WHERE (BD.BDTOPN Between 20120101 And 20130113) AND (BD.BDCAT>='2300000')
GROUP BY BD.BDCUST, BD.BDCAT, BD.BDQTY, BD.BDVUC
ORDER BY BD.BDCAT, Max(BD.BDTOPN) DESC

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 01/14/2013 :  02:52:57  Show Profile  Reply with Quote
Use the row_number function (assuming you are using SQL 2005 or later)
SELECT BD.BDCUST,
       BD.BDCAT,
       BD.BDQTY,
       BD.BDVUC,
       BD.BDTOPN
FROM
(      
	SELECT BD.BDCUST,
		   BD.BDCAT,
		   BD.BDQTY,
		   BD.BDVUC,
		   BD.BDTOPN,
		   ROW_NUMBER() OVER (PARTITION BY BDCUST,BDCAT ORDER BY BDTOPN DESC) AS NN
	FROM   GALF2.BD BD
	WHERE  (BD.BDTOPN BETWEEN 20120101 AND 20130113)
		   AND (BD.BDCAT >= '2300000')
) r
WHERE NN = 1		  
ORDER BY
       BDCAT,
       BD.BDTOPN DESC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/14/2013 :  03:18:53  Show Profile  Reply with Quote
by
last transaction for each customer + item

do you mean last transaction of customer and its associated item?
or last transaction of customer for an item?

if latter you can use given suggestion above

if former it should be


SELECT BD.BDCUST,
       BD.BDCAT,
       BD.BDQTY,
       BD.BDVUC,
       BD.BDTOPN
FROM
(      
	SELECT BD.BDCUST,
		   BD.BDCAT,
		   BD.BDQTY,
		   BD.BDVUC,
		   BD.BDTOPN,
		   ROW_NUMBER() OVER (PARTITION BY BDCUST,BDCAT ORDER BY BDTOPN DESC) AS NN
	FROM   GALF2.BD BD
	WHERE  (BD.BDTOPN BETWEEN 20120101 AND 20130113)
		   AND (BD.BDCAT >= '2300000')
) r
WHERE NN = 1		  
ORDER BY
       BDCAT,
       BD.BDTOPN DESC


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


Edited by - visakh16 on 01/14/2013 03:23:51
Go to Top of Page

GuyZommer
Starting Member

2 Posts

Posted - 01/14/2013 :  03:53:12  Show Profile  Reply with Quote
Hi,

Thanks for the prompt reply. I am using Microsoft Query.
I tried the code you suggest and I get the following error:
"Could not add the table '('.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/14/2013 :  04:05:27  Show Profile  Reply with Quote
quote:
Originally posted by GuyZommer

Hi,

Thanks for the prompt reply. I am using Microsoft Query.
I tried the code you suggest and I get the following error:
"Could not add the table '('.


You might be better off posting it in some Excel forums then. This is MS SQL Server forum and we deal with T-sql. The posted code works fine in t-sql.


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

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.08 seconds. Powered By: Snitz Forums 2000