CODEWITHSUNDEEP
sql-server
user10214725
user10214725

Reputation: 11

How do I retrieve the max value date from a group on SQL

Can anyone suggest to me a SQL query, which I am fighting with?

I have two tables:

Table_1: pos_tran which has a schema like:

pos_tran
( 
entry_no CHAR 8 (NOT NULL PK),
barcode CHAR 8,
tran_amt NUMERIC 9,
tran_qty NUMERIC 9,
des CHAR 20
)

Table_2: pos_tran_hd and schema:

pos_tran_hd
(
entry_no CHAR 8 (NOT NULL PK), 
Location_id CHAR 3,
entry_date DATETIME8,
)

Now, I wanted to group the barcode after joining the two tables using entry_no and retrieve the max(last date) entry from the list as I am writing this, if someone emphasized me more

SELECT pthd.maxed, pt.barcode, tran_amt, tran_qty from pos_tran pt,
(
 SELECT entry_no, max(entry_date) maxed 
 FROM
 pos_tran_hd 
 where location_id=015 GROUP BY entry_no
)
pthd 
WHERE pthd.entry_no=pt.entry_no 
ORDER BY barcode,pthd.maxed

but this isn't working as I desired and giving me around 200,000 records without giving the max date, in this way:

2014-06-23 00:00:00.000 21155192 28000.000 7.00

2014-07-01 00:00:00.000 21155192 4000.000 1.00

2014-07-08 00:00:00.000 21155192 8000.000 2.00

2014-07-12 00:00:00.000 21155192 12000.000 3.00

2014-08-13 00:00:00.000 21155192 4000.000 1.00

2014-09-16 00:00:00.000 21155192 12000.000 3.00

2014-10-06 00:00:00.000 21155192 12000.000 3.00

2015-01-26 00:00:00.000 21155192 12000.000 3.00

2015-05-29 00:00:00.000 21155192 4000.000 1.00

2018-01-05 00:00:00.000 21155192 28000.000 4.00

but actually it should give me the last record which has max(entry-date) and the worst part of the database is that I am working on Ms-SQL 2000.

Please help me if anyone has a positive suggestion. Thank you.

Upvotes: 0

Views: 73

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

you need max() function to get maximum date from pos_tran_hd then join with it and get rest of the fields , later join it with pos_tran for getting corresponding max date result

pthd.maxed, pt.barcode, tran_amt, tran_qty

 select t.maxed,ps.barcode,ps.tran_amt,ps.tran_qty from 
 (SELECT pd.*
    from pos_tran_hd pd
     inner join
    (
     SELECT max(entry_date) maxed 
     FROM
     pos_tran_hd 
    )
    pthd 
     on pthd.maxed=pos_tran_hd.entry_date
    ) as t inner join pos_tran ps on t.entry_no =ps.entry_no

Upvotes: 0

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Your second query selects a max entry_date by entry_no while there is only one entry_date per entry_no. In your description, you were talking about "grouping by the barcode"... but when grouping by barcode (to show just one row per barcode), you can't display amount and quantity for each entry.

So, let's start with this one:

select pt.barcode, max(pthd.entry_date) maxed
from pos_tran pt
  inner join pos_tran_hd pthd on pt.entry_no = pthd.entry_no
group by pt.barcode
order by pt.barcode

Then, to display the complete row with the last entry_date per barcode and since you are still using SQL Server 2000, we need a subquery to calculate the maxed per barcode like I did before and compare these values to the ones we want to display:

SELECT pthd.entry_date maxed, pt.barcode, pt.tran_amt, pt.tran_qty 
FROM pos_tran pt
  INNER JOIN pos_tran_hd pthd ON pt.entry_no = pthd.entry_no
WHERE EXISTS (
    select pt2.barcode 
    from pos_tran pt2
      inner join pos_tran_hd pthd2 on pt2.entry_no = pthd2.entry_no
    group by pt2.barcode
    having pt2.barcode = pt.barcode AND max(pthd2.entry_date) = pthd.entry_date)
ORDER BY pt.barcode

Upvotes: 1

Related Questions