selecting only data with the latest date 1

[toksoladuti]

I have the following query which brings back special prices set for customers, however there are sometimes multiple special prices on the same product for the same customer. What I'd like to do is change the query so that it only returns the row with the latest expiry date (spr.validto), but I haven't managed to get anywhere with this so I could do with some help please. Here is the current query:

select
c.name as [Customer],
c.customercode as [Customer Code],
p.productcode as [Product Code],
p.description as [Product],
(spr.price * (1-(spr.discount1/100)) * (1-(spr.discount2/100))) as [Special Price],
spr.price as [Calculation Price] ,
spr.discount1 as [Calculation Discount 1],
spr.discount2 as [Calculation Discount 2],
p.standardbuyprice as [Buy Price],
sr.name as [Sales Rep],
b.name as [Branch Name],
spr.validto as [Expiry Date],
spr.datetimelastused [Last Used Date],
spr.usagecount as [Times Used]
from sellpricerule spr
left outer join customer c
on spr.customerid=c.customerid
left outer join product p
on spr.productid=p.productid
left outer join branch b
on c.homebranchid=b.branchid
left outer join salesrep sr
on c.salesrepid=sr.salesrepid
where spr.customerid IS NOT NULL
order by spr.validto, b.name, c.name

Many thanks.

 

[kjv1611]

You just need to use the MAX function on your date field, and then GROUP BY clause for everything that's not a calculated field or that uses a function, such as MAX().

Hopefully I put the GROUP BY clause in the correct spot. swap it around with the ORDER BY clause if necessary. Debug, test, and let us know if it works.

select
c.name as [Customer], c.customercode as [Customer Code],
p.productcode as [Product Code], p.description as [Product],
(spr.price * (1-(spr.discount1/100)) * (1-(spr.discount2/100))) as [Special Price], spr.price as [Calculation Price], spr.discount1 as [Calculation Discount 1], spr.discount2 as [Calculation Discount 2], p.standardbuyprice as [Buy Price], sr.name as [Sales Rep],
b.name as [Branch Name], [HIGHLIGHT]MAX(spr.validto) as [Expiry Date][/HIGHLIGHT],
spr.datetimelastused [Last Used Date], spr.usagecount as [Times Used]

from sellpricerule spr
   left outer join
customer c
   on spr.customerid=c.customerid
   left outer join
product p
   on spr.productid=p.productid
   left outer join
branch b
   on c.homebranchid=b.branchid
   left outer join
salesrep sr
   on c.salesrepid=sr.salesrepid

where spr.customerid IS NOT NULL

[highlight]group by c.name as [Customer], c.customercode as [Customer Code],
p.productcode, p.description, spr.price, spr.discount1, spr.discount2, p.standardbuyprice, sr.name,
b.name, spr.datetimelastused, spr.usagecount[/highlight]

order by spr.validto, b.name, c.name

--

"If to err is human, then I must be some kind of human!" -Me

 

[markros]

select * from (select
c.name as [Customer],
c.customercode as [Customer Code],
p.productcode as [Product Code],
p.description as [Product],
(spr.price * (1-(spr.discount1/100)) * (1-(spr.discount2/100))) as [Special Price],
spr.price as [Calculation Price] ,
spr.discount1 as [Calculation Discount 1],
spr.discount2 as [Calculation Discount 2],
p.standardbuyprice as [Buy Price],
sr.name as [Sales Rep],
b.name as [Branch Name],
row_number() OVER (partition by c.CustomerCode, p.ProductCode ORDER BY spr.ValidTO DESC) as rn,
spr.validto as [Expiry Date],
spr.datetimelastused [Last Used Date],
spr.usagecount as [Times Used]
from sellpricerule spr
left outer join customer c
on spr.customerid=c.customerid
left outer join product p
on spr.productid=p.productid
left outer join branch b
on c.homebranchid=b.branchid
left outer join salesrep sr
on c.salesrepid=sr.salesrepid
where spr.customerid IS NOT NULL) In
order by [Expiry Date], Customer, Product where rn = 1

SQL Server 2005 and up solution

 

[toksoladuti]

Hi kjv1611,

I tried your query, but it still returning multiple product rows.

Thanks.

 

[toksoladuti]

markros,

Thanks for your query. I'm using SQL 2005 and have tried your query, but it's returning "Msg 156, Level 15, State 1, Line 26. Incorrect syntax near the keyword 'In'".

Many thanks.

 

[markros]

Oops again - move ORDER BY part after WHERE condition.

Also may be "In" is not a good alias, try something like OrderedCustomers.

 

[kjv1611]

Yes, since IN is an operator in SQL, probably not the best alias.

--

"If to err is human, then I must be some kind of human!" -Me

 

[markros]

Good point

 

[toksoladuti]

Many thanks for that markros, that did the trick.

 

[markros]

No problem