February 24, 2009 at 3:32 pm
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
01110Red Car with stripe 01/19/2009
01110Red Car with line 02/01/2009
01110 Red Car with laser 02/25/2009
Thanks,
Seajoker
February 24, 2009 at 3:56 pm
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
February 24, 2009 at 4:04 pm
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
February 24, 2009 at 4:23 pm
If I add Quantity to the equation, the data looks like this :
SKU# Description Date Quantity RN
01110Red Car with laser2009-02-25 00:00:00.00031
01110Red Car with line 2009-02-01 00:00:00.00032
01110Red Car with stripe2009-01-19 00:00:00.00033
01120Blue Car with laser2009-02-20 00:00:00.00031
01120Blue Car with line 2009-02-05 00:00:00.00022
01120Blue Car with stripe2009-01-19 00:00:00.00023
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
01110Red Car with laser2009-02-25 00:00:00.00031
01110Red Car with laser2009-02-01 00:00:00.00032
01110Red Car with laser2009-01-19 00:00:00.00033
01120Blue Car with laser2009-02-20 00:00:00.00031
01120Blue Car with laser 2009-02-05 00:00:00.00022
01120Blue Car with laser2009-01-19 00:00:00.00023
February 24, 2009 at 4:34 pm
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 #
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply