x

Generate last row in date by ID

Greetings,

I need to generate a report to extract only the last balance per product on a certain date. It turns out that you have several balances on the same date. It would be a stock report. I made the following query:

 select  
 idprd
 ,convert(varchar(10),DATAMOVIMENTO,103)
 ,SALDOFILIAL
 ,CUSTOMEDFILIAL
 ,totalfilial
 from TRELSLD AS A
 where IDPRD=83 

Return:

 ID               Date       Amount                             Average Cost                              Total
 
 83          21/02/2017 8487.0000                               0.1383                                  1173.9731
 83          21/02/2017 8486.0000                               0.1383                                  1173.8348
 83          21/02/2017 8485.0000                               0.1383                                  1173.6965
 83          21/02/2017 8484.0000                               0.1383                                  1173.5582

I need to return only the last line per product (ID):

 ID               Date       Amount                             Average Cost                              Total
 
 83          21/02/2017 8484.0000                               0.1383                                  1173.5582


Thanks if anyone can help.

more ▼

asked Mar 02 at 08:48 PM in Default

avatar image

Gelain
10 2

To know which is the last, we need to know what order they are in. Without an order they are just a set of 4 rows

Mar 02 at 09:12 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

why not use the row_number windows function, order it by date desc and grab row 1. Like this.

with a as (

select ROW_NUMBER() over(partition by a.idprd order by a.DATAMOVIMENTO desc) as rownumber, convert(varchar(10),a.DATAMOVIMENTO,103), a.SALDOFILIAL, a.CUSTOMEDFILIAL, a.totalfilial from TRELSLD A

) select * from a where rownumber = 1

more ▼

answered Mar 04 at 09:16 PM

avatar image

Thomas_Cruse
10 2

But if all the rows have the same date (including a timestamp to ms) which row do you think SQL will choose?

Mar 06 at 02:49 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Please check this code

SELECT TOP 1 IDPRD, CONVERT(varchar(10),DATAMOVIMENTO,103) ,SALDOFILIAL, CUSTOMEDFILIAL, totalfilial FROM TRELSLD AS A WHERE IDPRD=83 ORDER BY DATAMOVIMENTO DESC

more ▼

answered Mar 03 at 01:29 PM

avatar image

supriyan
1 1

(comments are locked)
10|1200 characters needed characters left

Thanks Kev and Supriyan for the help. Better detail my need:

 DATE       RECCREATEDON            IDPRD      AMOUNT             AVERAGE COST                    TOTAL
 ---------- ----------------------- ----------- ------------------ ------------------- ------------------------
 22/02/2017 2017-03-03 10:00:53.000 83          1511.0000           0.0657                           99.2710
 22/02/2017 2017-03-03 10:00:53.000 83          1510.0000           0.0657                           99.2053
 23/02/2017 2017-03-03 10:00:53.000 83          1511.0000           0.0657                           99.2710
 23/02/2017 2017-03-03 10:00:53.000 83          1510.0000           0.0657                           99.2053
 23/02/2017 2017-03-03 10:00:53.000 83          1508.0000           0.0657                           99.0739
 23/02/2017 2017-03-03 10:00:53.000 83          1499.0000           0.0657                           98.4826

I searched with the RECCREATEDON column, but at the same time it has several values. I consider that I need the final balance of the day for several products that are in the same table.

Greetings.

more ▼

answered Mar 03 at 08:28 PM

avatar image

Gelain
10 2

But still there is no way to know which of those rows is the last one for the day

Mar 04 at 11:40 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Thanks a lot for the help. There is an error and I could not resolve it.

image-1.jpg (236.3 kB)
image-2.jpg (186.5 kB)
more ▼

answered Mar 20 at 08:44 PM

avatar image

Gelain
10 2

the error is because you have not given a name/alias for column 2

Mar 21 at 08:18 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Thank you! It worked.

more ▼

answered Mar 22 at 07:45 PM

avatar image

Gelain
10 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x13
x10
x3

asked: Mar 02 at 08:48 PM

Seen: 93 times

Last Updated: Mar 22 at 07:45 PM

Copyright 2017 Redgate Software. Privacy Policy