By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Selecting MAX date from a group of records

P: 2
Hi All,

New to the forum and to SQL.

I'm running a query to pull sales records for a specific customer that returns several dates (for each transaction). I only need the most recent date from the results however and am having difficulty figuring out how to get this.

Can anyone help?

Thanks

Current code is:
Expand|Select|Wrap|Line Numbers
  1. SELECT vw_Summary_sales_Cust_Region_Brick_All.ISBN13, Sum(vw_Summary_sales_Cust_Region_Brick_All.NET_U) AS 'Sum of NET_U', vw_Summary_sales_Cust_Region_Brick_All.DOC_DATE
  2. FROM Staging_vista.dbo.vw_Summary_sales_Cust_Region_Brick_All vw_Summary_sales_Cust_Region_Brick_All
  3. WHERE (vw_Summary_sales_Cust_Region_Brick_All.CUST='56879') AND (vw_Summary_sales_Cust_Region_Brick_All.PERIOD>='0502' And vw_Summary_sales_Cust_Region_Brick_All.PERIOD<='0911')
  4. GROUP BY vw_Summary_sales_Cust_Region_Brick_All.ISBN13, vw_Summary_sales_Cust_Region_Brick_All.DOC_DATE
  5.  
Resulting in:

Expand|Select|Wrap|Line Numbers
  1. 9781579905507    Altered Art    6,996     11/17/2006
  2. 9781579905507    Altered Art    1,804     8/24/2006
  3. 9781579905507    Altered Art    1,408     8/1/2006
  4. 9781579905507    Altered Art    638       9/28/2006
  5. 9781579905507    Altered Art    154       7/1/2008
  6. 9781579905507    Altered Art    110       7/25/2008
  7. 9781579905507    Altered Art    6         9/26/2006
  8. 9781579905507    Altered Art    (2)       7/25/2007
  9. 9781579905507    Altered Art    (5)       2/16/2007
  10. 9781579905507    Altered Art    (8)       5/13/2008
  11.  
These are individual transactions of purchases of one of our books. I need these individual transactions but in the end only need the most recent date (last time they purchased.)
Dec 8 '09 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Could you post some sample data and your table structure?

--- CK
Dec 8 '09 #2

P: 2
I'm pulling the data from a view.

I've attached a sample record screenshot taken in Studio. Below is the sample I provided in the original post...is that not what you're looking for?

Thanks so much.


9781579905507 Altered Art 6,996 11/17/2006
9781579905507 Altered Art 1,804 8/24/2006
9781579905507 Altered Art 1,408 8/1/2006
9781579905507 Altered Art 638 9/28/2006
9781579905507 Altered Art 154 7/1/2008
9781579905507 Altered Art 110 7/25/2008 *
9781579905507 Altered Art 6 9/26/2006
9781579905507 Altered Art (2) 7/25/2007
9781579905507 Altered Art (5) 2/16/2007
9781579905507 Altered Art (8) 5/13/2008


So again I'm able to pull (via MS Query) total units sold of a given book title to a given customer lifetime. However I need to add to this the date of the last sale of that title to the customer. When I add the "docdate" (which is the order date) to the query I of course then get every individual sale of that specific title to the customer and the date of the sale. Our sales group only needs the most recent date though.

* is the only date I need but I need the units sold in all of those transactions so that I can provide TOTAL units sold and the date of the last sale.
Attached Images
File Type: jpg sample.jpg (3.2 KB, 193 views)
Dec 8 '09 #3

nbiswas
100+
P: 149
Try this(a sample)

Expand|Select|Wrap|Line Numbers
  1. declare @t table(ISBN bigint,AlertNames varchar(50),FieldSums varchar(20),dt datetime)
  2. insert into @t
  3. select 9781579905507,'Altered Art','6,996','11/17/2006' union all
  4. select 9781579905507,'Altered Art','1,804','8/24/2006' union all
  5. select 9781579905507,'Altered Art', '1,408','8/1/2006'union all
  6. select 9781579905507,'Altered Art','638','9/28/2006'union all
  7. select 9781579905507,'Altered Art','154', '7/1/2008'union all
  8. select 9781579905507,'Altered Art' , '110', '7/25/2008'union all
  9. select 9781579905507,'Altered Art ','6','9/26/2006'union all
  10. select 9781579905507,'Altered Art','(2)','7/25/2007'union all
  11. select 9781579905507,'Altered Art','(5)' ,'2/16/2007'union all
  12. select 9781579905507,'Altered Art ','(8)' ,'5/13/2008'
Query:

Expand|Select|Wrap|Line Numbers
  1. select isbn,MAX(dt) latestdate from @t
  2. group by  isbn
Output:

isbn latestdate
9781579905507 2008-07-25 00:00:00.000

If you need some presentation, go ahead with union all like


select * from @t
union all
select null,'Latest Date : ',null,MAX(dt) from @t
Dec 9 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.