Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select latest description from table Expand / Collapse
Author
Message
Posted Tuesday, February 24, 2009 3:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 16, 2010 9:17 AM
Points: 178, Visits: 146

I have a sales table that updated daily with new sales data. When pulling historical data for a particular SKU# and description, I get multiple lines for each description that the SKU# has been. I would like to be able to only pull the latest description from the table and use is for all sales for that SKU#

SKU# Description SalesDate
01110 Red Car with stripe 01/19/2009
01110 Red Car with line 02/01/2009
01110 Red Car with laser 02/25/2009

Thanks,
Seajoker
Post #663878
Posted Tuesday, February 24, 2009 3:56 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
if object_id('tempdb..#t1') is not null
drop table #t1

create table #t1 (SKU# char(5), [Description] varchar(200), SalesDate datetime)

Insert into #t1 ([SKU#], [Description], SalesDate) Values('01110', 'Red Car with stripe', '01/19/2009')
Insert into #t1 ([SKU#], [Description], SalesDate) Values('01110', 'Red Car with line', '02/01/2009')
Insert into #t1 ([SKU#], [Description], SalesDate) Values('01110', 'Red Car with laser', '02/25/2009')
Insert into #t1 ([SKU#], [Description], SalesDate) Values('01120', 'Blue Car with stripe', '01/19/2009')
Insert into #t1 ([SKU#], [Description], SalesDate) Values('01120', 'Blue Car with line', '02/05/2009')
Insert into #t1 ([SKU#], [Description], SalesDate) Values('01120', 'Blue Car with laser', '02/20/2009')

;with cte
AS
(SELECT *, row_number() over(partition by [sku#] order by SalesDate desc) rn
from #t1
)
select [SKU#], [Description], SalesDate
from cte
where rn =1




* Noel
Post #663891
Posted Tuesday, February 24, 2009 4:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:16 PM
Points: 298, Visits: 715
Hi,

you need to use the row_number function to identify which specific rows you want returned. Change the order by clause if you want the oldest record.


create table # (SKU# varchar(10), Description  varchar(128), SalesDate  datetime)

insert #
select '01110', 'Red Car with stripe', '01/19/2009'
union all select '01110', 'Red Car with line', '02/01/2009'
union all select '01110', 'Red Car with laser', '02/25/2009'
union all select '01120', 'Blue Car with stripe', '01/19/2009'
union all select '01120', 'Blue Car with line', '02/01/2009'
union all select '01120', 'Blue Car with laser', '02/25/2009'

select [sku#], [description], [SalesDate] from
(select *, row_number() over (partition by SKU# order by salesDate desc) as rownum from #) x
where rownum=1



Bevan
Post #663895
Posted Tuesday, February 24, 2009 4:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 16, 2010 9:17 AM
Points: 178, Visits: 146

If I add Quantity to the equation, the data looks like this :
SKU# Description Date Quantity RN
01110 Red Car with laser 2009-02-25 00:00:00.000 3 1
01110 Red Car with line 2009-02-01 00:00:00.000 3 2
01110 Red Car with stripe 2009-01-19 00:00:00.000 3 3
01120 Blue Car with laser 2009-02-20 00:00:00.000 3 1
01120 Blue Car with line 2009-02-05 00:00:00.000 2 2
01120 Blue Car with stripe 2009-01-19 00:00:00.000 2 3

How can I change all descriptions for a particular sku to the newest description, so the data looks like this: I need to keep the daily sales quantity.

SKU# Description Date Quantity RN
01110 Red Car with laser 2009-02-25 00:00:00.000 3 1
01110 Red Car with laser 2009-02-01 00:00:00.000 3 2
01110 Red Car with laser 2009-01-19 00:00:00.000 3 3
01120 Blue Car with laser 2009-02-20 00:00:00.000 3 1
01120 Blue Car with laser 2009-02-05 00:00:00.000 2 2
01120 Blue Car with laser 2009-01-19 00:00:00.000 2 3
Post #663905
Posted Tuesday, February 24, 2009 4:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:16 PM
Points: 298, Visits: 715
I see Noel and I posted almost identical answers on the first question!

Heres one way to update the values...

---  Have a look at new and old data side by side
select a.[sku#], a.[description], a.[SalesDate], x.[description] as new from
(select *, row_number() over (partition by SKU# order by salesDate desc) as rownum from #) x
inner join # a on a.sku# = x.sku#
where rownum=1


-- Run the update
UPDATE a set a.[description] = x.[description]
from
(select *, row_number() over (partition by SKU# order by salesDate desc) as rownum from #) x
inner join # a on a.sku# = x.sku#
where rownum=1


-- Have a look at new values
select * from #

Post #663908
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse