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
 General SQL Server Forums
 New to SQL Server Programming
 Last transaction for customer + item

Author  Topic 

GuyZommer
Starting Member

2 Posts

Posted - 2013-01-14 : 01:11:15
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-14 : 02:52:57
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

52326 Posts

Posted - 2013-01-14 : 03:18:53
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/

Go to Top of Page

GuyZommer
Starting Member

2 Posts

Posted - 2013-01-14 : 03:53:12
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

52326 Posts

Posted - 2013-01-14 : 04:05:27
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
   

- Advertisement -